Total User Spend

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
Etsy

Problem

Show each user's ID, username, and total transaction amount, alphabetically by username.

Each customer's total. Summarized.

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 15 rows
user_idusernametotal_spend
294 amelia9003.12
197aaron428814.54
779aiden9946.02
100alice8605.98
585andrew_k9491.52

Worked solution and explanation

What this really is

This is a one-to-many collapse dressed up as a customer roster. users holds the name and transactions holds the money, and every buyer has a dozen or more purchases, so the join hands you one row per purchase, not one per person. Anyone can write the join. What separates candidates is remembering that the join multiplies rows and that SUM has to fold them back to one row per user_id. Skip that and the username repeats down the page with a single purchase amount beside it: it looks like a report and is wrong on every line.

Build it

Step 1: Join users to transactions on user_id

Neither table answers the question alone: transactions has total_amount but no name, and users has username but no money. Join them on user_id. An inner join is the right call, because someone with no purchases has no spend to report, and the expected output has 15 rows, exactly the buyers.

Step 2: Fold each user's purchases into one total

Group by u.user_id and u.username and take SUM(t.total_amount). user_id is the real key; username rides along because the select needs it for display. The sum keeps the voided sale at 0.0 and the refund at -45.0, which is what spend net of refunds means.

Step 3: Sort by name, not by money

ORDER BY u.username ascending. Sorting on a non-aggregated column is legal here because username is part of the grouping.

The solution

Join, aggregate, and sort alphabetically
SELECT u.user_id, u.username, SUM(t.total_amount) AS total_spend
FROM users u
JOIN transactions t ON u.user_id = t.user_id
GROUP BY u.user_id, u.username
ORDER BY u.username

Common follow-up questions