March Revenue by Customer

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

Problem

How much has each customer spent in March, counting every March in the transactions table rather than just this year's? Finance's seasonal review lists each user ID with that combined March total, biggest spender first.

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 really is

Strip off the finance costume and this is a one-month slice folded into a per-customer sum, with a twist in the slice: March means every March in transactions, 2022 through 2026, added together. Everyone writes the grouping. What separates candidates is reading the window right. Pin March to 2026 out of habit and the answer shrinks from ten customers to six; forget to sum and you hand back twenty purchase lines instead of ten totals.

The trap

The reflex is to read 'March revenue' as this March. On this table that is a different question: March rows exist in five years, and four customers (973, 1264, 391 and 682) only bought in older Marches. The preview gives it away if you look: 973 sits second at 1878.84, yet has no March purchase in 2026 at all. Reconcile the preview against the data before you commit to a window.

March pinned to one year

WHERE transaction_date >= '2026-03-01' AND transaction_date < '2026-04-01' returns six customers. 973, 1264, 391 and 682 drop out because their March purchases fell in earlier years.

Every March pooled

WHERE strftime('%m', transaction_date) = '03' keeps the twenty March rows from all five years, so all ten customers appear with their combined spend.

Step 1: Keep every March row

WHERE strftime('%m', transaction_date) = '03' keeps March rows from every year and drops the rest before any summing runs, so the total only ever sees March money. There is deliberately no year predicate.

Step 2: Fold purchases into one row per customer

GROUP BY user_id with SUM(total_amount) turns the twenty March purchases into ten totals. Nine of the ten customers bought more than once, so skipping this step is never a near miss: 294 alone has three March lines that add to 1929.24.

Step 3: Order the standings

ORDER BY march_total DESC puts the biggest spender on top. Sorting on the alias you return keeps the order you are graded on visible in the output itself.

SELECT user_id,
       SUM(total_amount) AS march_total
FROM transactions
WHERE strftime('%m', transaction_date) = '03'
GROUP BY user_id
ORDER BY march_total DESC

Common follow-up questions

• Finance now wants each March broken out by year rather than pooled. How does the query change? (Tests moving the year from the filter into the grouping key and the output.)
• How would you write the filter so an index on `transaction_date` can serve it? (Probes that a function on the column defeats the index, and that pooled months need a stored month key.)
• What should happen to a customer who spent nothing in March but transacted in other months? (Checks they know this query returns only customers with a March row, and how they would add zero-spend customers if asked.)