Transaction Timeline

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

L3

Problem

Our lifetime-value model is being fed a purchase snapshot with a row per transaction, so every buyer shows up a dozen times instead of once. Rebuild it from users and transactions so it holds only customers who have bought at least once, each with their username, first and latest purchase dates and total spend, as an alphabetical roster by username.

First purchase to last. The full spending arc.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

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 one-to-many collapse dressed up as a finance snapshot. Every buyer owns a dozen or so rows in transactions, the name lives in users, and the model wants exactly one row per buyer. Anyone can write MIN, MAX and SUM. What separates candidates is scoping: who belongs in the snapshot, and which key defines a customer. Reach for the reflexive LEFT JOIN and the 185 users who never bought anything show up as rows of NULLs. Group on username instead of user_id and two people who share a name get blended into one fake customer with merged dates and summed spend.

Walking to the query

Step 1: Let the join decide who is a buyer

The snapshot is about buyers, so keep only users with a matching transaction. An inner join of users to transactions on user_id does that by construction: a user with no rows on the right never appears, and you get the 15 buyers without writing a filter.

Step 2: Group on user_id, not username

The grain is one row per customer, and the customer is user_id, the key of users. username sits in the GROUP BY only so it can be selected; it is not the identity. The buyers' names happen to be unique on this data, which is exactly why the bug passes a sample check and shows up in production.

Step 3: Compute all three figures in one pass

MIN(t.transaction_date) and MAX(t.transaction_date) give the first and latest purchase, and SUM(t.total_amount) gives the spend, all from the same grouped rows. The dates are ISO strings such as '2026-02-02', so text order is date order and no conversion is needed.

Step 4: Sort the roster on the stored name

ORDER BY u.username sorts on a column the query returns, so the grader checks that your rows are sorted by it. Each buyer has a different name, so no tiebreaker is owed.

SELECT
    u.username,
    MIN(t.transaction_date) AS first_purchase,
    MAX(t.transaction_date) AS latest_purchase,
    ROUND(SUM(t.total_amount), 2) AS lifetime_spend
FROM users u
JOIN transactions t ON t.user_id = u.user_id
GROUP BY u.user_id, u.username
ORDER BY u.username;

Keep every user

users LEFT JOIN transactions returns all 200 users. The 185 who never bought produce rows with NULL dates and a NULL spend, so the model trains on customers with no purchase history.

Keep only buyers

users JOIN transactions returns only the 15 users with at least one row in transactions, which is exactly the population a lifetime-value model needs.

Common follow-up questions

• How would you add the number of days between each customer's first and latest purchase? (Date arithmetic on `MIN(t.transaction_date)` and `MAX(t.transaction_date)` inside the same grouped pass.)
• Finance now wants every user, with zero spend for those who never bought. What changes? (A `LEFT JOIN` plus `COALESCE(SUM(t.total_amount), 0)`, and a clear decision about what the date columns hold.)
• How would you add average spend per transaction? (`AVG(t.total_amount)` in the same pass, and whether refunds and voids should count in the denominator.)
• The roster should ignore stray spaces in names. How do you change it without merging customers? (Trim for display and sorting while still grouping on `user_id`.)