The following statement uses a nested ordered analytical function and a HAVING clause to find those items that appear in the top 1 percent of profitability in more than 20 stores:
SELECT item, COUNT(store) FROM (SELECT store,item,profit,QUANTILE(100,profit) AS percentile FROM (SELECT ds.store, it.item, SUM(sales)- COUNT(sales) * it.item_cost AS profit FROM daily_sales AS ds, items AS it WHERE ds.item = it.item GROUP BY ds.store, it.item, it.item_cost) AS item_profit GROUP BY store, item, profit QUALIFY percentile = 0) AS top_one_percent GROUP BY item HAVING COUNT(store) >= 20;
The results of this query might look like this:
Item Count(Store) ---------------- ------------ Chilean sea bass 99 Mackerel 149 Tako 121 Herring 120 Salmon 143