ROW_NUMBER, RANK, and CTEs

~14 min · queries, advanced

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The three ranking functions

Three window functions for assigning numbers within partitions. They differ in how they handle ties:

ROW_NUMBER() — unique sequential number, no ties (1, 2, 3, 4).
RANK() — ties share the rank, then skips (1, 2, 2, 4).
DENSE_RANK() — ties share the rank, no skipping (1, 2, 2, 3).

Top-N per group

The classic application: "show me the 3 most recent orders per customer." Subquery with ROW_NUMBER, then filter WHERE rn <= 3. This single pattern shows up in nearly every dashboard you'll ever build.

CTEs for readable layered queries

A CTE (Common Table Expression, the WITH clause) is a named subquery defined at the top of your query. It turns deeply nested queries into named, readable steps. Multiple CTEs can chain. Recursive CTEs traverse trees.

Code

Top-N per group with ROW_NUMBER·sql

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY o.customer_id
           ORDER BY o.placed_at DESC
         ) AS rn
  FROM orders o
) ranked
WHERE rn <= 3;

Multi-CTE pipeline·sql

WITH active_users AS (
  SELECT DISTINCT user_id
  FROM   orders
  WHERE  placed_at >= now() - INTERVAL '90 days'
),
user_spend AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM   orders
  WHERE  user_id IN (SELECT user_id FROM active_users)
  GROUP  BY user_id
)
SELECT u.name, us.total_spent
FROM   user_spend us
JOIN   users u ON u.id = us.user_id
ORDER  BY us.total_spent DESC
LIMIT  10;

Recursive CTE — traverse a tree·sql

-- Org chart: every employee with their depth from the root
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM   employees e
  JOIN   org o ON e.manager_id = o.id
)
SELECT REPEAT('  ', depth-1) || name AS tree, depth
FROM   org
ORDER  BY depth, name;

External links

Exercise

Build a query that returns each customer's top 3 orders by total, using a CTE for the ranking and a final SELECT for presentation. Bonus: add a recursive CTE that walks a self-referential 'category parent' tree.