The following example reports the bottom percentile of items by profitability:
SELECT item, profit, QUANTILE(100, profit) AS percentile FROM (SELECT item, SUM(sales)-(COUNT(sales)*items.itemcost) AS profit FROM daily_sales, items WHERE daily_sales.item = items.item GROUP BY item) AS itemprofit QUALIFY percentile = 99;
The results of this query might look something like the following table.
item | profit | percentile |
---|---|---|
Carrot-flavored ice cream | 10.79 | 99 |
Low fat carrot-flavored ice cream | - 100.55 | 99 |
Low fat sugar-free carrot-flavored ice cream | - 1,110.67 | 99 |
Regular broccoli-flavored ice cream | - 2,913.88 | 99 |
Low fat broccoli-flavored ice cream | - 4,492.12 | 99 |