# Total User Spend

> Each customer's total. Summarized.

Canonical URL: <https://datadriven.io/problems/total_user_spend>

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Etsy

## Problem

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

## Tables

### users

- user_id INTEGER
- username TEXT
- email TEXT
- signup_date TEXT
- account_status TEXT
- age_bucket TEXT

Sample rows (10 of 200):

| user_id | username | email | signup_date | account_status | age_bucket |
| --- | --- | --- | --- | --- | --- |
| 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 |
| 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 |
| 294 |  amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 |
| 391 | arjun  | arjun@example.com | 2025-05-05 | active | 55-64 |
| 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ |
| 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL |
| 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 |
| 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 |
| 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 |
| 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 |

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 15 rows:

| user_id | username | total_spend |
| --- | --- | --- |
| 294 |  amelia | 9003.12 |
| 197 | aaron42 | 8814.54 |
| 779 | aiden | 9946.02 |
| 100 | alice | 8605.98 |
| 585 | andrew_k | 9491.52 |

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a basic join with aggregation and ordering. Interviewers check that you can join two tables, aggregate correctly, and sort by a non-aggregated column.

---

### Break down the requirements

#### Step 1: Join users to transactions

`JOIN transactions ON users.user_id = transactions.user_id` matches each user with their purchases.

#### Step 2: Aggregate total per user

`GROUP BY users.user_id, username` with `SUM(total_amount)` computes each user's total spend.

#### Step 3: Order alphabetically

`ORDER BY username` sorts the output alphabetically by username as requested.

---

### The solution

**Join, aggregate, and sort alphabetically**

```sql
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
```

> **Cost Analysis**
>
> The join expands to 60M rows (matching transactions to users). The GROUP BY reduces to ~3M users with transactions. The alphabetical sort on 3M usernames is the secondary cost. An index on `transactions(user_id)` helps the join.

> **Interviewers Watch For**
>
> Whether you use INNER JOIN (only users with transactions) vs LEFT JOIN (all users including those with zero spend). The prompt says "total transaction amount," implying only users with transactions.

> **Common Pitfall**
>
> Sorting by `total_spend` instead of `username`. Read the prompt carefully: it asks for alphabetical order, not by spend.

---

## Common follow-up questions

- What if you needed to include users with zero transactions showing 0? _(Switch to LEFT JOIN and COALESCE(SUM(...), 0).)_
- How would you show only users with spend above a threshold? _(Tests HAVING vs WHERE for aggregate filters.)_
- What if usernames were not unique? _(Grouping by `user_id` alone ensures correctness; username is just for display.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/total_user_spend)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.