# Peak Season

> One region, one month. Peak profit.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The finance team wants the single most profitable region and month pairing from 2026. Match each order to the transactions sharing the same ID modulo 100, total the order profit within each region and transaction month, and return the one pairing with the highest total.

## Worked solution and explanation

### What this is really asking

`o.order_id % 100 = t.transaction_id % 100` is not a real key. It is a synthetic bucket join across 30M and 100M rows that fans into roughly a 30-trillion-row pre-aggregate. Every shape decision flows from that.

---

### Break down the requirements

#### Step 1: Join on the modulo bucket

Match each order to every transaction sharing the same (id mod 100). This is a many-to-many bucket join, not a referential lookup.

#### Step 2: Group by region and transaction month

Pull the calendar month out of transaction_date with strftime('%m', t.transaction_date) so the same month across years collapses together, then SUM profit per (region, month). The result is a two-digit string like '05', which is exactly what the grader expects.

#### Step 3: Order by total_profit and LIMIT 1

The prompt asks for one winner. Sort descending on the aggregate and take the top row. Ties resolve arbitrarily without a tiebreaker column.

---

### The solution

**TOP REGION-MONTH BY PROFIT**

```sql
SELECT
    o.region,
    strftime('%m', t.transaction_date) AS txn_month,
    SUM(o.profit) AS total_profit
FROM orders o
INNER JOIN transactions t
    ON (o.order_id % 100) = (t.transaction_id % 100)
GROUP BY o.region, strftime('%m', t.transaction_date)
ORDER BY total_profit DESC
LIMIT 1
```

> **Cost Analysis**
>
> Mod-100 join produces ~300M rows per bucket on average, 30T total intermediate. No index helps because the predicate is a derived expression. In real life you would materialize the bucket as a computed column or rewrite the join key.

> **Interviewers Watch For**
>
> Whether you flag the modulo join as a synthetic bucket rather than treating it as a foreign key. Also whether you reach for the engine's own date function: strftime('%m', ...) buckets by calendar month and ignores the year, which is fine here because the seed spans a single year. Portable Postgres idioms like EXTRACT or TO_CHAR do not parse on this SQLite-backed grader.

> **Common Pitfall**
>
> Filtering profit > 0 in WHERE to chase performance. The prompt says most profitable, which can include net-positive regions composed of losses and gains. Dropping negative profit rows changes the answer.

> **The False Start**
>
> First instinct is `ORDER BY SUM(o.profit) DESC LIMIT 1` without a GROUP BY, expecting one row back. That returns a single grand total across all regions and months. Pivot to grouping by (region, txn_month) first, then ordering the aggregate.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you break a tie if two region-months had identical total_profit? _(Add a deterministic secondary key like region ASC or the count of contributing orders to the ORDER BY.)_
- How would you rewrite this so the optimizer can actually use an index? _(Persist `order_id % 100` as a generated column with an index, or pre-bucket both tables into a join-helper table.)_
- What if finance wants the top region-month per year, not overall? _(Group by region, year, and month, then use RANK() OVER (PARTITION BY year ORDER BY SUM(profit) DESC) and filter rnk = 1.)_

## Related

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