Deep Pockets

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

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.

One month, every customer, every dollar accounted for.

Tables

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

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

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

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.)