Example: Subqueries Using the RANK Functions and QUALIFY Clauses - Teradata VantageCloud Lake

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;

Results look similar to this: