The following statement specifies a GROUP BY clause with an ordered analytical function to generate report breaks where the function resets and computes a new value for the next grouping.
The example groups all items into percentile by profitability for each store and then returns only the items of interest, which, in this case, are the lowest percentile for each store.
SELECT store, item, profit, QUANTILE(100, profit) AS percentile FROM (SELECT items.item, SUM(sales) - (COUNT(sales)*items.item_cost) AS profit FROM daily_sales, items WHERE daily_sales.item = items.item GROUP BY items.item,items.itemcost) AS item_profit GROUP BY store, item, profit, percentile QUALIFY percentile = 99;
The result of this query looks like the following table:
store ----- |
item ---- |
profit ------ |
percentile ---------- |
Eastside |
Golf balls |
100.19 |
99 |
Westside |
Tennis balls |
-110.00 |
99 |
Central |
Bowling balls |
-986.81 |
99 |
South |
Codfish balls |
- 1,891.89 |
99 |
North |
Bocce balls |
1,180.88 |
99 |