Before They Walk

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
mid
Asked in
Allstate

Problem

Most of the revenue in transactions seems to come from a tiny sliver of customers, and the retention team wants a white-glove loyalty program for exactly those people before a competitor poaches them. Find the ten customers who have spent the most across every purchase they have ever made, showing each one's user ID, username from users, and total spend, biggest spenders first.

A few names carry the whole ledger. Reach them first, before someone else does.

Tables

users

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

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

transactions

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

transactions: 10 of 200 rows
transaction_iduser_idproduct_idquantitytotal_amounttransaction_date
10671971001223.462026-02-02
11342941050336.932026-03-03
12013911099450.42026-04-04
12684881148563.872026-05-05
13355851197002026-06-06
14026821246290.812026-07-07
146977912953104.282026-08-08
153687613444117.752026-09-09
160397313935-452026-01-10
1670107014421144.692026-02-11

Expected output

First 5 of 10 rows
user_idusernamelifetime_spend
1070bjorn9910511.76
973brian10146.96
876aria_b10134.6
779aiden9946.02
682anika9757.44

Worked solution and explanation

Why this problem exists in real interviews

This is the canonical revenue-concentration question, and it shows up on phone screens because it quietly checks four things at once: can you connect a fact table to a dimension table, can you collapse many rows per entity into one number, can you rank that number, and can you cut the list off at a useful size. The purchase facts live in transactions (one row per purchase, with total_amount and user_id), but the human identity lives in users (username keyed by user_id). A candidate has to recognize that total_amount must be summed per customer, then attach the name, then surface only the heaviest contributors. The decision that separates a clean answer from a messy one is grouping on user_id (the stable key) while still carrying username through for readability.

Break down the requirements

Step 1: Connect purchases to people

Each transaction only carries a user_id, but the team needs a name. Match every transaction row to its owner in users on user_id so the username travels alongside the dollars.

Step 2: Collapse to one total per customer

A customer has many purchases, so add up total_amount per user_id. Group on the user_id (and carry username along) and SUM the amounts into a single lifetime_spend figure per person.

Step 3: Rank and trim

Sort the per-customer totals from largest to smallest and keep only the first ten rows, since the loyalty program targets just the heaviest spenders.

The solution

Top ten by lifetime spend
SELECT u.user_id AS user_id, u.username AS username, SUM(t.total_amount) AS lifetime_spend FROM users u JOIN transactions t ON u.user_id = t.user_id GROUP BY u.user_id, u.username ORDER BY lifetime_spend DESC LIMIT 10;

Common follow-up questions