# Before the Rush

> The holidays are coming. See where each category's money moves, month by month.

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

Domain: SQL · Difficulty: easy · Seniority: L5

## Problem

Ahead of the holiday planning cycle, the merchandising team needs to see how each product category's revenue shifts month to month. Show the total transaction amount for each category-month combination, ordered by category then month.

## Worked solution and explanation

### What this really is

Underneath the holiday-planning story, this is a fact-and-dimension reconciliation with a date bucket on top. The revenue lives in transactions; the category lives in products; you have to marry them and roll the result up to one number per category per calendar month. Anyone can write the SUM. Two quieter decisions separate the strong answers: what a 'month' actually means, and what happens to a transaction whose product you can't identify.

---

### Two ways to get plausible-but-wrong numbers

> **The month-of-year merge**
>
> Bucket on STRFTIME('%m', ...) alone and every April collapses into one row, no matter the year. The team is watching a trend across the two-year span, so April 2025 and April 2026 have to stay separate. Bucket on year plus month ('%Y-%m') so each calendar month is its own point on the line. Get this wrong and the numbers look reasonable while silently doubling up across years.

**INNER JOIN (correct here)**

A transaction whose product_id has no row in products has no known category, so it can't belong to any category bucket. Keeping only matched rows is the intended behavior.

**LEFT JOIN**

Keep the unmatched rows and they collect in a NULL-category bucket that isn't in the expected output, adding a phantom group the merchandising team never asked for.

### Building the query

#### Step 1: Join on product_id

Match each transaction to its product on product_id to pull the category across. An inner join keeps only transactions whose product exists, which is exactly what you want here.

#### Step 2: Bucket the date to year-month

STRFTIME('%Y-%m', transaction_date) turns a date into a '2026-04' label. This is the grain decision: year plus month, never month alone.

#### Step 3: Sum within each category-month

GROUP BY category and the same year-month expression, then SUM(total_amount). The GROUP BY key has to match the two output dimensions exactly, one row per category-month pair.

#### Step 4: Order for reading

ORDER BY category, month lays each category out as a top-to-bottom time series, which is how the merchandising team reads a trend.

---

### The solution

**Join with year-month bucket and two-level GROUP BY**

```sql
SELECT p.category,
    STRFTIME('%Y-%m', t.transaction_date) AS month,
    SUM(t.total_amount) AS total_revenue
FROM transactions t
JOIN products p ON t.product_id = p.product_id
GROUP BY p.category, STRFTIME('%Y-%m', t.transaction_date)
ORDER BY p.category, month
```

> **Cost analysis**
>
> transactions is 80M rows against a 25K-row products table, so the engine builds a hash on products and streams transactions through it. The GROUP BY collapses everything to a few hundred category-month rows before anything downstream sees it, which is where the real cost sits. transaction_date is the partition key, so a date-bounded version of this query prunes partitions instead of scanning all 80M rows.

> **Interviewers watch for**
>
> Say the output grain out loud before you write the GROUP BY: 'one row per category per calendar month.' Then name why the join is inner and what it discards. Candidates who state the grain and the join semantics up front read as senior; the ones who jump straight to SUM and backfill the grouping tend to ship the month-of-year bug.

## Common follow-up questions

- How would you add a month-over-month growth rate per category on top of these totals? _(Tests window functions layered over an aggregated series.)_
- If a product could change category over time, how would you attribute each transaction's revenue to the right category? _(Tests point-in-time / slowly-changing-dimension attribution.)_
- How would you restrict this to a single year without scanning all 80M rows? _(Tests partition pruning on transaction_date.)_

## Related

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