My question is very similar to this but not the same. The table is the same: rows with ID, volume, and price. In short, I need an SQL query that will give me the rows with the lowest prices whose sum of volumes is less or equal to 10% of total volume. I need the average price of those rows. In even shorter terms, average price for the 10% cheapest volume. For a better explanation, I've written an algorithm in pseudocode on how I would have done it in C++:
- Sort table by price (lowest first).
- Calculate total volume and store 10% of that value in a variable named 'threshold'.
- Declare a variable 'sum' with value 0 and start a loop that will iterate through the aforementioned sorted table.
- Check if 'sum' is less than 'threshold'. If true, add the row's volume value to 'sum', and add the examined row to a list. Increase iterator by 1.
- Repeat step 4 until condition is false. When false, it should break the loop and return the list.
- Make an arithmetic average of the returned row's prices.
I've written the entire thing with three explanations as to make sure people understand and hopefully provide their own insight on how to solve this in SQL. There might be a single function, or a very simple query that can solve this entire thing, so I gave a detailed context to the problem.