The Shape of the Year

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

L3

Asked in

Capital One

Problem

We're tracing a retail marketplace's revenue as it moves month by month across the calendar. For each month, report the total revenue, the number of transactions, and the average transaction value, earliest month first.

Revenue has a rhythm. Trace it, month by month.

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

What this really tests

This is a monthly rollup wearing a revenue-analysis costume. The only real skill: turn a raw transaction_date into a month bucket and pick a grain where each calendar month collapses to exactly one row. Everyone can type SUM and COUNT. The tell is whether your GROUP BY key is the exact same expression you SELECT as the bucket, so the two can never drift apart, and whether your average is revenue over transaction count rather than an average of some finer grain. Get the grain wrong and you either split one month across many rows or quietly average the wrong denominator.

Building it up

Step 1: Derive the month key

Derive the month key with strftime('%Y-%m', transaction_date). This yields a zero-padded string like 2026-04 that carries both year and month, and it is the shared bucket for every transaction in that month.

Step 2: Collapse each month

GROUP BY the month key so every transaction in the same year-month folds into a single row. Because the key includes the year, February 2025 and February 2026 stay separate.

Step 3: Compute the three metrics

Per month compute SUM(total_amount) as total revenue, COUNT(*) as the transaction count, and SUM(total_amount) / COUNT(*) as the average transaction value. Order by the month key so the timeline reads front to back.

The solution

SELECT strftime('%Y-%m', transaction_date) AS month,
       SUM(total_amount) AS total_revenue,
       COUNT(*) AS num_transactions,
       SUM(total_amount) / COUNT(*) AS avg_transaction_value
FROM transactions
GROUP BY month
ORDER BY month

Common follow-up questions

• A month with zero transactions vanishes from the result. How would you produce a row of zeros for every month in the range instead? (Tests awareness that GROUP BY silently omits empty buckets and how to backfill them.)
• How would you add a column showing each month's revenue change versus the prior month? (Tests understanding of month-over-month deltas built on top of the rollup.)
• With 80M rows partitioned by `transaction_date`, how would you make this cheaper if you only needed the most recent year, and what lets that filter prune partitions? (Tests partitioning and indexing intuition on a large date-partitioned table.)