# The Spending Rhythm

> Every month tells a spending story, user by user.

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

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. Order results by user then month.

## 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: lexicographic and chronological coincide for that format. Order on the raw date or an unpadded month and the sequence breaks.

---

### The solution

**User-month grain for spending cadence**

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

> **Cost Analysis**
>
> At 60M rows the whole cost is one pass plus the grouped aggregation; there is no join and no subquery to blow up. The aggregation collapses the row count early, so everything downstream sees the small grouped result, not the full table. If this report runs daily, a covering index on (user_id, transaction_date) lets the engine stream rows already clustered by the group keys.

> **Interviewers Watch For**
>
> Naming the output grain ('one row per user per month') out loud before writing the GROUP BY signals you think in terms of data shape, not syntax. The tell of seniority here is mentioning that you derive the month into a stable, sortable format rather than grouping on the raw timestamp.

> **Common Pitfall**
>
> transaction_date is stored as TEXT. Grouping or ordering on the raw column works only because the ISO 'YYYY-MM-DD' layout happens to sort correctly as a string; a different date format (say 'MM/DD/YYYY') would order lexicographically and scramble the months. Always confirm the stored format before you lean on string ordering.

---

## 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 NULLs, 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.)_

## Related

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