16.20 - Example: Using QUALIFY With RANK - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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 could 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 might look like the following.

Item SMonth Sales Rank(Sales) Moving Avg(Sales)
A 1996-04 210 3 155
A 1996-05 270 1 195
A 1996-06 250 2 225
A 1996-07 190 4 230
A 1996-08 180 5 222
B 1996-02 30 1 30
... ... ... ... ...

The result indicates that sales had probably been fairly low prior to the start of the current sales season.