The following example uses nested RANK functions and QUALIFY clauses to report the top 100 items by profitability and the top items by revenue, matching those that appear in both lists using an OUTER JOIN:
SELECT * FROM (SELECT item, profit, RANK(profit) AS profit_rank FROM item, sales QUALIFY profit_rank <= 100 AS p) FULL OUTER JOIN (SELECT item, revenue, RANK(revenue) AS revenue_rank FROM item, sales QUALIFY revenue_rank <= 100 AS r) ON p.item = r.item;
The results of this query might look something like the following table:
item ---- |
profit ------ |
profitrank ---------- |
item ---- |
revenue ------- |
revenuerank ----------- |
---|---|---|---|---|---|
Dress slacks | 17804 | 74 | Dress slacks | 180211 | 56 |
Dress shirts | 16319 | 68 | ? | ? | ? |
Dresses | 55888 | 82 | Dresses | 652312 | 77 |
Blouses | 9849 | 48 | Blouses | 771849 | 92 |
? | ? | ? | Skirts | 817811 | 55 |