The Spending Rhythm

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

A monthly spending cadence report needs each user's transaction count broken out by calendar month so the finance team can spot irregular purchasing patterns. Lay it out as a per-user timeline, working through users from the lowest id upward and running each user's months from earliest to latest.

Every month tells a spending story, user by user.

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

This is a per-user, per-month frequency count wearing a finance-report costume. Anyone can reach for COUNT(*) and group by user_id. What actually separates candidates is recognizing that the second grouping key is a DERIVED value, the year-month pulled out of transaction_date, not the raw date itself. Group on the raw date and you get one row per day per user instead of one per month. The row counts collapse to mostly 1s, and the cadence report the finance team asked for dissolves into daily noise.

Break down the requirements

Step 1: Bucket by user and month

The output grain is one row per user per calendar month, so both user_id and the month string belong in the GROUP BY. Format the month as STRFTIME('%Y-%m', transaction_date) so it sorts chronologically and reads cleanly, then COUNT(*) the rows that fall into each bucket.

Step 2: Order the final output

The report is ordered by user, then month. Because the month is the zero-padded %Y-%m string, an ordinary ORDER BY user_id, month sorts it in true calendar order: for that format, lexicographic and chronological order coincide. Order on the raw date or an unpadded month and the sequence breaks.

The solution

SELECT user_id,
    STRFTIME('%Y-%m', transaction_date) AS month,
    COUNT(*) AS transaction_count
FROM transactions
GROUP BY user_id, STRFTIME('%Y-%m', transaction_date)
ORDER BY user_id, month

Common follow-up questions

• If some transactions had a `NULL` `transaction_date`, how would that change the result, and which rows would you want to count? (Tests whether the candidate knows `COUNT(*)` includes every row while `COUNT(col)` skips `NULL`s, and which one this report needs.)
• How would you verify the `transactions` table has no duplicate rows that would inflate each month's count? (Tests data quality awareness: duplicate transaction rows would inflate the per-month count.)
• Given tens of millions of rows, what index would you add so this per-user, per-month count stays fast as a scheduled report? (Tests indexing knowledge for a recurring grouped aggregation over a very large table.)