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

SELECT
  department,
  emp_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,
    emp_name,
    salary,
    RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT department, emp_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 check whether you understand this distinction.

WITH customer_spend AS (
  SELECT
    user_id,
    product_id,
    SUM(total_amount) AS total_spend
  FROM transactions
  GROUP BY user_id, product_id
)
SELECT
  product_id,
  user_id,
  total_spend,
  RANK() OVER (
    PARTITION BY product_id
    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 2-step pattern (aggregate, then rank) is extremely common.

WITH latest AS (
  SELECT
    user_id,
    transaction_id,
    transaction_date,
    total_amount,
    RANK() OVER (
      PARTITION BY user_id
      ORDER BY transaction_date DESC
    ) AS rn
  FROM transactions
)
SELECT user_id, transaction_id, transaction_date, total_amount
FROM latest
WHERE rn = 1;

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