sqlite: get the average of the top X% for every item

aggregate-functions, sql, sqlite

Solution

Getting the top n records within each group requires counting. Assuming that there are no duplicates, the following query returns the number of records for an item:

select t.*,
       (select count(*) from t t2 where t2.item_id = t.item_id
       ) as NumPrices
from t

This is called a correlated subquery. Now, let's extend the idea to include a rank and then calculate the average for the right group:

select item_id, avg(price)
from (select t.*,
             (select count(*) from t t2 where t2.item_id = t.item_id
             ) as NumPrices,
             (select count(*) from t t2 where t2.item_id = t.item_id and t2.price <= t.price
             ) as PriceRank
      from t
     ) t
where (100.0*PriceRank / NumPrices) <= X
group by item_id

To improve performance, you will want an index on `(item_id, price)`.

Problem

Is it possible to get the average of the top X% items in a group? For example: I have a table which has a item_id, timestamp and price column. The output should be grouped by item_id and timestamp and the 'price-column' should get averaged. For the averaging only the lowest X% prices within that group should be used. I've found similar questions (How to select top x records for every group) but this won't work with sqlite.

Original source