BBampaa

Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

PERCENTILE_CONT() and PERCENTILE_DISC()

Percentile functions find the value at a specific ranked position in your data.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Finding Typical Retail Order Value with Percentiles

SELECT category, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_cont, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_disc FROM orders GROUP BY category ORDER BY category

Result:

categorymedian_revenue_contmedian_revenue_disc
Clothing61.2555.0
Electronics165.0120.0

Step by step

  1. AS gives a column a friendlier name in the result.
    SELECT category, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_cont, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_disc
  2. FROM tells SQL which table to pull the data from.
    SELECT category, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_cont, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_disc
FROM orders
  3. GROUP BY collapses rows that share the same value into one row per group.
    SELECT category, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_cont, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue_disc
FROM orders
GROUP BY category

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query to calculate the 50th percentile of revenue.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) FROM orders
2. Using store_sales, write a query to calculate the 50th percentile of revenue.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) FROM store_sales
3. Using products, write a query to calculate the 75th percentile of price.
SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) FROM products
4. Using employees, write a query to calculate the 75th percentile of salary.
SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) FROM employees
5. Using reviews, write a query to calculate the 50th percentile of rating.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY rating) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed