Before the Rush

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

Domain

SQL

Difficulty

easy

Seniority

L5

Asked in

Visa

Problem

Ahead of holiday planning, a merchandising analyst is tracking how the revenue recorded in transactions moves from one calendar month to the next for each product category in products. Total the amounts per category and month, listed category by category with the months in time order.

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

Tables

transactions

Columns: transaction_id INTEGER, product_id INTEGER, total_amount REAL, transaction_date TEXT, user_id INTEGER, quantity INTEGER

products

Columns: product_id INTEGER, category TEXT, product_name TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

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

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

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

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`.)