Example: Ordered Analytical Functions Using QUALIFY with RANK - Teradata VantageCloud Lake

Adding a QUALIFY clause to a query eliminates rows from an unqualified table.

For example, if you wanted to see whether the high sales months were unusual, you can add a QUALIFY clause to the previous query.

   SELECT item, smonth, sales, 
   RANK() OVER (PARTITION BY item ORDER BY sales DESC), 
   AVG(sales) OVER (PARTITION BY item ORDER BY smonth ROWS 3 PRECEDING)
   FROM sales_tbl
   ORDER BY item, smonth
   QUALIFY RANK() OVER(PARTITION BY item ORDER BY sales DESC) <=5;

This additional qualifier produces a results table that may look like the following.

The result indicates that sales were likely low before the start of the current sales season.