SQL RANK vs DENSE_RANK vs ROW_NUMBER for Data Engineers

SELECT
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM exam_results;

Key insight: ROW_NUMBER always increments. RANK skips from 2 to 4 because two rows consumed positions 2 and 3. DENSE_RANK goes from 2 to 3 because it only tracks distinct positions, not consumed slots.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY revenue DESC
    ) AS rn
  FROM customers
)
SELECT * FROM ranked WHERE rn <= 5;

The most frequently tested ranking pattern. Use ROW_NUMBER inside a CTE, then filter WHERE rn <= N. PARTITION BY defines the group; ORDER BY defines the ranking criteria.

WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY ingested_at DESC
    ) AS rn
  FROM raw_orders
)
SELECT * FROM deduped WHERE rn = 1;

Source tables often contain duplicate rows from retries, late-arriving data, or CDC replication. ROW_NUMBER partitioned by the natural key and ordered by a freshness column assigns 1 to the most recent version. This is the standard ELT dedup pattern.

SELECT
  customer_id,
  lifetime_value,
  NTILE(4) OVER (ORDER BY lifetime_value DESC) AS quartile
FROM customers;
-- quartile 1 = top 25% spenders

NTILE(N) splits the result set into N roughly equal groups. NTILE(4) creates quartiles, NTILE(100) creates percentiles. If the row count is not evenly divisible, earlier buckets get one extra row.

WITH numbered AS (
  SELECT
    event_date,
    ROW_NUMBER() OVER (ORDER BY event_date) AS rn
  FROM daily_logins
  WHERE user_id = 42
)
SELECT
  MIN(event_date) AS streak_start,
  MAX(event_date) AS streak_end,
  COUNT(*) AS streak_length
FROM numbered
GROUP BY event_date - INTERVAL '1 day' * rn
ORDER BY streak_start;

Assign ROW_NUMBER ordered by date, then subtract it from the actual date. Consecutive rows produce the same difference; gaps produce a different one. GROUP BY that difference to identify islands of consecutive data.

-- Without PARTITION BY: global ranking
SELECT
  department, employee_name, salary,
  RANK() OVER (ORDER BY salary DESC) AS global_rank
FROM employees;
-- rank runs 1 through N across all rows

-- With PARTITION BY department: per-department ranking
SELECT
  department, employee_name, salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;
-- Engineering has ranks 1, 2, 3...
-- Marketing has its own 1, 2, 3...

-- Multiple PARTITION BY columns
SELECT
  department, fiscal_year, employee_name, sales_total,
  ROW_NUMBER() OVER (
    PARTITION BY department, fiscal_year
    ORDER BY sales_total DESC
  ) AS yearly_rank
FROM annual_sales;