Interview › Second highest

SQL interview: second-highest salary / order

Second highest is a ranking problem. Nested MAX works until there is a tie and someone asks for the row.

Prompt. Return the customer with the second-highest lifetime spend, and the spend.

Hint: ROW_NUMBER on the aggregated spend

Your T-SQL

WITH spend AS (
  SELECT customer_id, SUM(total_amount) AS sales
  FROM Orders
  GROUP BY customer_id
)
SELECT customer_id, sales
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY sales DESC, customer_id) AS rn
  FROM spend
) x
WHERE rn = 2;

DENSE_RANK = 2 returns every customer tied for second. Say so in the interview.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free