Third Highest Spender

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Amazon

Problem

Find the user with the third highest total spend across all transactions. If there is a tie, include all users sharing that rank. Show the user ID and total spend.

Bronze medal in spending.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

customers

Columns: customer_id INTEGER, first_name TEXT, last_name TEXT, country TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is the classic Nth-highest value problem. Interviewers use it to test whether you know how to rank aggregated values with a window function and extract a specific position.

Break down the requirements

Step 1: Aggregate spend per entity

SUM(total_amount) grouped by the spender produces per-entity totals.

Step 2: Rank by total spend

DENSE_RANK() OVER (ORDER BY total_spend DESC) assigns positions. DENSE_RANK ensures tied values share the same rank.

Step 3: Filter to rank 3

Wrap in a subquery and filter WHERE rnk = 3 to extract the third-highest spender.

The solution

SELECT user_id, total_spend
FROM (
    SELECT
        user_id,
        SUM(total_amount) AS total_spend,
        DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rnk
    FROM transactions
    GROUP BY user_id
) ranked
WHERE rnk = 3

Common follow-up questions

• What if multiple users tie for 3rd place? (Tests understanding that `DENSE_RANK` returns all tied rows at position 3.)
• How would you find the Nth highest for each category? (Tests adding PARTITION BY category to the window function.)
• Could you solve this without a window function? (Tests correlated subquery: COUNT(DISTINCT higher values) = 2.)
• What if there are fewer than 3 distinct spend values? (Tests edge case awareness: the query returns no rows.)