Regional Sales Growth QoQ

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Shopify

Problem

A region only gets a growth figure if it sold in both Q3 and Q4; one with sales in just one of those quarters is left out. We key regions by user_id in transactions and pool every year's July to September sales as Q3 and October to December sales as Q4. Report each region's Q4 total as a percentage change over its Q3 total.

Quarter-over-quarter growth. Region by region.

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

This is a set intersection dressed up as a growth report. Anyone can sum total_amount for July to September and for October to December. What separates candidates is seeing that 'only regions with sales in both quarters' is not a filter you bolt on at the end: it is the join. Total each quarter on its own and join the two totals on region, and a region missing from either side never appears. Compute both quarters in one pass over transactions instead, and the three regions with Q3 sales but no Q4 sales survive with a NULL growth: 15 rows where 12 are graded.

Building it

Step 1: Read the quarter off the month alone

transaction_date is text, so pull the month with strftime('%m', transaction_date) and CAST it to an integer, so the comparison is numeric rather than against the text '07'. Do not add a year filter. The quarters pool every year in the table, and the current year's Q4 has not happened yet, so restricting to 2026 empties the Q4 side completely.

Step 2: Total each quarter in its own CTE

q3 and q4 each keep their three months, alias user_id as region and sum total_amount. Keeping them separate means each CTE holds only the regions that actually sold in that quarter, which is exactly the property the next step leans on.

Step 3: Let the join enforce 'both quarters'

A plain JOIN is an inner join, so q4 JOIN q3 ON q4.region = q3.region keeps a region only when it has a row on both sides. On the seed, three of the fifteen buyers sold in Q3 and never in Q4, and the join removes them without a single extra predicate.

Step 4: Divide by the Q3 base

Growth is (q4.total - q3.total) * 100.0 / q3.total. The 100.0 keeps the arithmetic in floating point on engines where both totals could be integers. Here total_amount is already REAL, so it is a habit worth keeping rather than a fix.

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

One pass, conditional sums

SUM(CASE WHEN ... BETWEEN 10 AND 12 THEN total_amount END) per user_id emits every buyer. A buyer with no Q4 rows gets a NULL Q4 sum, so their growth is NULL and the result carries 15 rows. Making it right needs a HAVING that checks both sums, a rule you have to remember to add.

Two totals, joined

Each CTE only contains regions that sold in its quarter, so the join itself is the rule. Twelve rows, no NULL growth, nothing extra to remember.

SELECT user_id AS region,
       (SUM(CASE WHEN CAST(strftime('%m', transaction_date) AS INTEGER) BETWEEN 10 AND 12 THEN total_amount END)
        - SUM(CASE WHEN CAST(strftime('%m', transaction_date) AS INTEGER) BETWEEN 7 AND 9 THEN total_amount END))
       * 100.0
       / SUM(CASE WHEN CAST(strftime('%m', transaction_date) AS INTEGER) BETWEEN 7 AND 9 THEN total_amount END) AS growth_pct
FROM transactions
GROUP BY user_id

Common follow-up questions

• How would you compare Q4 against Q3 of the same year only? (Adds the year to the grouping key and the join, and checks whether the candidate notices 2026 has no Q4 yet.)
• A region's Q3 total is zero or negative after refunds. What should growth be? (A zero base divides by zero, and a negative base flips the sign's meaning; tests `NULLIF` and judgment.)
• Can you write this in one pass over `transactions`? (Conditional sums with a `HAVING` that requires both quarters; keeps the intersection rule without the join.)
• Real regions arrive as a dimension table keyed by `user_id`. What changes? (Adds a join before the totals, so several buyers per region must sum together; tests grain.)