Transaction Overview

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

Asked in

Meta

Problem

The finance team needs two numbers for the board slide: how many users have made at least one purchase, and the average transaction amount across all transactions.

The executive snapshot. Users, products, revenue.

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

This is a grain question dressed up as a board-slide request. The two numbers come from the same table but at different grains: purchasers are counted per person, while the average is taken per transaction row. Anyone gets AVG(total_amount) right. The trap is the first number. Every row in transactions carries a user_id, so COUNT(user_id) looks like it counts users, but it counts purchase lines. On this data that gives 200 instead of 15. On the production table each buyer fans out to about twenty transactions, so the slide would claim twenty times the real customer base while the average beside it looks perfectly sane.

Read the grain of each number

Step 1: Count people, not purchase lines

user_id repeats on every transaction a person makes: each of the 15 buyers here sits on 12 to 14 rows. COUNT(DISTINCT user_id) collapses that fan-out, so a buyer with fourteen purchases counts once. A user who never bought has no row in transactions at all, so 'at least one purchase' needs no extra filter: appearing in the table is the condition.

Step 2: Average every transaction row

The second number is per transaction, so AVG(total_amount) runs over all 200 rows as recorded, including the -45.0 refund and the 0.0 voided sale. Averaging per-user subtotals first would weigh a one-time buyer the same as a heavy one, which answers a different question.

Step 3: Return both in one pass

Neither number is broken down by anything, so both aggregates sit in one SELECT with no GROUP BY. The result is one scan, one row, and two columns named for the slide.

SELECT
    COUNT(DISTINCT user_id) AS unique_purchasers,
    AVG(total_amount) AS avg_transaction_amount
FROM transactions

Counts purchase lines

COUNT(user_id) or COUNT(*) returns 200 here, one per transaction. Nothing errors, and the correct average next to it makes the row look trustworthy.

Counts buyers

COUNT(DISTINCT user_id) returns 15: each buyer once, no matter how many times they bought.

Common follow-up questions

• What if you needed the median instead of the average? (Tests knowledge of `PERCENTILE_CONT` or a manual approach, since a median is not a streaming aggregate.)
• How would you add the total transaction count alongside these metrics? (Add `COUNT(*)` to the same `SELECT`; it sits at the row grain, like the average.)
• What if some `total_amount` values were NULL? (`AVG` skips NULL amounts while `COUNT(DISTINCT user_id)` still counts that buyer, so the two numbers would describe slightly different populations.)