17.05 - Example: Reporting the Bottom Percentile of Items Using QUANTILE in a QUALIFY Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)

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