lesson 36 Window functions · advanced

RANK & DENSE_RANK

Rank rows and decide how ties should behave.

the idea

RANK gives ties the same rank, then leaves a gap. DENSE_RANK gives ties the same rank without gaps. ROW_NUMBER always assigns distinct numbers.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  customer_id,
  COUNT(*) AS order_count,
  DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
FROM orders
GROUP BY customer_id;

Watch for: Ties exist in this dataset, so RANK and DENSE_RANK produce different outputs.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Measure the value you will rank. Ranking needs a sort key. Count orders per customer first; DENSE_RANK will order those counts.

   step-1.sql Run

   sql›

   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   ORDER BY order_count DESC, customer_id;

2. 2

   Assign dense ranks to groups. DENSE_RANK gives the same rank to ties and does not skip the next rank. RANK would leave gaps after ties.

   step-2.sql Run

   sql›

   SELECT
     customer_id,
     COUNT(*) AS order_count,
     DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS rank
   FROM orders
   GROUP BY customer_id;