SQL RANK() OVER (PARTITION BY) Explained with Examples

RANK() OVER (
  PARTITION BY department
  ORDER BY salary DESC
) AS salary_rank

SELECT
  department,
  name,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_rank
FROM employees;

-- department  | name    | salary  | salary_rank
-- ------------|---------|---------|------------
-- Engineering | Alice   | 180000  | 1
-- Engineering | Bob     | 165000  | 2
-- Engineering | Carol   | 165000  | 2
-- Engineering | Dave    | 150000  | 4
-- Marketing   | Eve     | 140000  | 1
-- Marketing   | Frank   | 130000  | 2

Bob and Carol both earn $165K, so they both get rank 2. Dave gets rank 4, not rank 3. The gap in ranks tells you that ties exist above.

WITH ranked AS (
  SELECT
    department,
    name,
    salary,
    RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3;

If there are ties at rank 3, RANK() returns all tied rows. That means you might get more than 3 rows per department. If you want exactly 3, use ROW_NUMBER() instead. Interviewers specifically test whether you understand this distinction.

WITH customer_spend AS (
  SELECT
    customer_id,
    region,
    SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id, region
)
SELECT
  region,
  customer_id,
  total_spend,
  RANK() OVER (
    PARTITION BY region
    ORDER BY total_spend DESC
  ) AS spend_rank
FROM customer_spend;

The CTE computes total_spend with GROUP BY. The outer query applies RANK() to the aggregated result. This two-step pattern (aggregate, then rank) is extremely common.

WITH latest AS (
  SELECT
    customer_id,
    order_id,
    order_date,
    amount,
    RANK() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM latest
WHERE rn = 1;

If a customer has two orders on the same date, RANK() returns both. Use ROW_NUMBER() if you want exactly one row per customer regardless of ties.