Lessons › SQL › Deep Dive: Advanced Window Functions

SQL lesson

PERCENT_RANK() and CUME_DIST()

PERCENT_RANK and CUME_DIST are window functions - meaning they calculate across a set of rows without collapsing them into a single result - that tell you where each row stands relative to the whole group. PERCENT_RANK says how far up the ranking a row has climbed (0 at the very bottom, 1 at the top), while CUME_DIST says what fraction of the group scores at or below that row.

Example

Segmenting Retail Products with PERCENT_RANK() and CUME_DIST()

SELECT product_id, category, units_sold, ROUND(PERCENT_RANK() OVER (ORDER BY units_sold)::numeric, 4) AS pct_rank, ROUND(CUME_DIST() OVER (ORDER BY units_sold)::numeric, 4) AS cum_dist FROM products ORDER BY units_sold

Result:

Step by step

1. Run the query without ROUND so you can see what the unfiltered values look like.

   SELECT product_id, category, units_sold, PERCENT_RANK, ROUND(CUME_DIST() OVER (ORDER BY units_sold)::numeric, 4) AS cum_dist FROM products ORDER BY units_sold;

2. ROUND rounds the value to the nearest integer (or to N decimal places with a second argument).

   SELECT product_id, category, units_sold, ROUND(PERCENT_RANK() OVER (ORDER BY units_sold)::numeric, 4), ROUND(CUME_DIST() OVER (ORDER BY units_sold)::numeric, 4) AS cum_dist FROM products ORDER BY units_sold;

3. AS gives the ROUND result a friendly column name so the output reads cleanly.

   SELECT product_id, category, units_sold, ROUND(PERCENT_RANK() OVER (ORDER BY units_sold)::numeric, 4) AS pct_rank, ROUND(CUME_DIST() OVER (ORDER BY units_sold)::numeric, 4) AS cum_dist FROM products ORDER BY units_sold;

Practice

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

SELECT *, PERCENT_RANK() OVER (ORDER BY revenue) FROM orders

SELECT *, CUME_DIST() OVER (ORDER BY revenue) FROM store_sales

SELECT *, CUME_DIST() OVER (ORDER BY price) FROM products

SELECT *, CUME_DIST() OVER (ORDER BY salary) FROM employees

SELECT *, CUME_DIST() OVER (ORDER BY rating) FROM reviews