Whale Watch

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

The transactions table holds one row per purchase, and total_amount is already that purchase's dollar value. For every user with more than a dozen transactions, report their total spending, how many transactions they made, and their average transaction size rounded to two decimals, biggest spender first.

The accounts driving the top line.

Tables

transactions

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

Expected output

Worked solution and explanation

What this is really asking

Strip away the dashboard language and this is a filter on something no single row knows. Each row in transactions is one purchase; none of them carries 'this user has bought thirteen times'. That fact only exists once the rows are rolled up per user_id. Anyone can write the three aggregates. What separates candidates is where the threshold lives: put it on the roll-up, keep it strictly above twelve, and the list is exactly the regulars. Get either wrong and users with twelve purchases slip in, or the query refuses to run.

Step 1: Roll each user up once

Group by user_id and compute SUM(total_amount), COUNT(*) and AVG(total_amount) in one pass. total_amount is already the dollar value of the purchase, so multiplying by quantity double counts.

Step 2: Filter on the per-user count

The threshold is a property of the group, so it belongs in HAVING COUNT(*) > 12. 'More than a dozen' is strict: a user with exactly twelve purchases is not on the list, and on this data some users sit right at twelve.

Step 3: Round late, sort by spend

ROUND(AVG(total_amount), 2) happens in the output, never before averaging. Then ORDER BY total_spend DESC puts the biggest spender at the top.

SELECT
    user_id,
    SUM(total_amount) AS total_spend,
    COUNT(*) AS txn_count,
    ROUND(AVG(total_amount), 2) AS avg_txn_size
FROM transactions
GROUP BY user_id
HAVING COUNT(*) > 12
ORDER BY total_spend DESC

Row filter

WHERE COUNT(*) > 12 is rejected outright: WHERE runs before any grouping, when each row is still a single purchase and there is nothing to count.

Group filter

HAVING COUNT(*) > 12 runs after the roll-up, when every user is one row holding their count. It is the only place the threshold can see what it is testing.

Common follow-up questions

• What if `total_amount` included refunds as negative values? (Tests whether you separate gross spend from net spend, and whether a refund should count as a transaction.)
• How would you require more than a dozen transactions within the last year only? (A `WHERE` on `transaction_date` before grouping, with the `HAVING` count unchanged: row filter and group filter working together.)
• How would you add each user's share of all spending on the list? (A total over the filtered result divided into each `total_spend`, computed after the `HAVING`.)