# Deep Pockets

> One month, every customer, every dollar accounted for.

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

Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Amazon

## Problem

We're closing the books for March 2026 and need a spend breakdown by customer. Total each customer's transactions for the month, biggest spenders first.

## Tables

### 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 6 rows:

| user_id | total_revenue |
| --- | --- |
| 294 | 1929.24 |
| 876 | 1649.85 |
| 100 | 1434.33 |
| 1458 | 1407.3899999999999 |
| 585 | 1164.9299999999998 |

## Worked solution and explanation

### What this problem is really testing

This is a plain per-customer sum wearing a finance costume. The whole game is the date boundary. Anyone can write GROUP BY `user_id` with SUM(`total_amount`); what separates people is expressing 'March' as a half-open range instead of reaching for BETWEEN or LIKE, and NOT bolting on a redundant filter for 'users with at least one transaction' when the grouping already guarantees that. Botch the boundary and you either swallow April 1 or drop the last day of March, and finance reconciles against a total that is quietly wrong.

---

### Break down the requirements

#### Step 1: Filter to March of the target year

Use `WHERE transaction_date >= '2026-03-01' AND transaction_date < '2026-04-01'`. The half-open range captures every March date, including the 31st, and cleanly excludes April even if a time component sneaks in later.

#### Step 2: Aggregate per customer

`GROUP BY user_id` with `SUM(total_amount)` gives each customer's March spend. Because you are grouping the transactions themselves, only customers who actually transacted in March appear. The 'at least one transaction' requirement is satisfied for free, so an extra HAVING or existence check is wasted work.

#### Step 3: Sort highest to lowest

`ORDER BY total_revenue DESC` puts the biggest spenders at the top, which is what finance reads first.

---

### The solution

**Date-range filter with per-customer aggregation**

```sql
SELECT user_id, SUM(total_amount) AS total_revenue
FROM transactions
WHERE transaction_date >= '2026-03-01'
  AND transaction_date < '2026-04-01'
GROUP BY user_id
ORDER BY total_revenue DESC
```

> **Cost Analysis**
>
> The date filter narrows 100M rows to roughly 8.2M (one of about twelve months). A composite index on `(transaction_date, user_id, total_amount)` turns this into a covering range scan: the engine walks only the March slice and aggregates straight from the index without touching the heap.

> **Interviewers Watch For**
>
> The half-open interval (`>=` and `<`). Candidates who write `BETWEEN '2026-03-01' AND '2026-03-31'` silently drop any March 31 row that carries a time component, and the mistake is invisible on small sample data.

> **Common Pitfall**
>
> Reaching for `LIKE '2026-03%'`. It happens to work on TEXT dates, but it defeats an index range scan and breaks the moment the stored format changes. It also tempts people into a needless second filter for 'active' customers that the GROUP BY already handles.

---

## Common follow-up questions

- How would you modify this to show revenue by month for the entire year? _(Tests adding date truncation to the GROUP BY for a time-series pivot.)_
- What if you needed to include customers with zero March transactions? _(Requires a LEFT JOIN from a customers table so zero-spend customers survive.)_
- What if `transaction_date` included timestamps with time components? _(Confirms the half-open interval still behaves correctly once time components exist.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/deep_pockets)
- [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.