# Regional Sales Growth QoQ

> Quarter-over-quarter growth. Region by region.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

Compare total transaction amounts in Q4 vs Q3 to compute quarter-over-quarter revenue growth by region. Growth is ((Q4 total minus Q3 total) / Q3 total) * 100. Only include regions with sales in both quarters. Return the region and growth percentage.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests whether a candidate can demonstrate structuring multi-step logic with CTEs, working with date arithmetic and formatting, and writing subqueries that integrate with the outer query. This is an advanced probe that separates senior candidates who can decompose complex logic from those who cannot.

> **Trick to Solving**
>
> Complex queries with multiple transformation stages are best solved by writing one CTE per stage.
> 
> 1. Identify the intermediate results needed
> 2. Write each as a named CTE
> 3. Chain them: each CTE reads from the previous one
> 4. The final SELECT assembles the output

---

### Break down the requirements

#### Step 1: Structure the logic with 2 CTEs

Break the problem into named stages: `q3`, `q4`. Each CTE isolates one transformation, making the query readable and debuggable.

#### Step 2: Join `transactions` to `q4`

The join connects the two tables on their shared key. This brings the columns needed for filtering and aggregation into a single row set.

#### Step 3: Apply the range filter

The WHERE clause restricts rows to the target range. Applying this filter early reduces the volume flowing into downstream operations.

---

### The solution

**CTE with grouped aggregation**

```sql
WITH q3 AS (
    SELECT user_id AS region, SUM(total_amount) AS total
    FROM transactions
    WHERE CAST(strftime('%m', transaction_date) AS INTEGER) BETWEEN 7 AND 9
    GROUP BY user_id
)
, q4 AS (
    SELECT user_id AS region, SUM(total_amount) AS total
    FROM transactions
    WHERE CAST(strftime('%m', transaction_date) AS INTEGER) BETWEEN 10 AND 12
    GROUP BY user_id
)
SELECT q4.region, (q4.total - q3.total) * 100.0 / q3.total AS growth_pct
FROM q4
JOIN q3 ON q4.region = q3.region
```

> **Cost Analysis**
>
> With ~200M rows, the GROUP BY reduces the working set before any downstream operations; the join cost depends on the smaller table's cardinality; CTEs materialize intermediate results, which can be beneficial or costly depending on the engine. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether you decompose the problem into named, testable stages rather than nesting everything; how you handle date arithmetic and whether you account for edge cases like month boundaries; whether you use a subquery or self-join, and can explain the tradeoffs.

> **Common Pitfall**
>
> Integer division truncates the result silently. Cast at least one operand to DOUBLE before dividing to get a decimal result.

---

## Common follow-up questions

- How would you handle late-arriving data that changes historical results? _(Tests understanding of backfill strategies, SCD patterns, and immutable event logs.)_
- What if the dataset was partitioned across 50 shards? _(Tests distributed query planning: partition pruning, shuffle costs, and data locality.)_
- Would a temp table outperform the CTE approach at scale? _(Tests knowledge of CTE materialization behavior: some engines re-execute CTEs, others materialize.)_
- How would you add unit tests for this query? _(Tests dbt-style testing: schema tests, row count assertions, referential integrity checks.)_
- What monitoring would you put around this in production? _(Tests operational maturity: freshness checks, anomaly detection on output metrics, alerting.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/regional_sales_growth_qoq)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.