Example: Reporting the Bottom Percentile of Items Using QUANTILE in a QUALIFY Clause - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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