# The Shape of the Year > Revenue has a rhythm. Trace it, month by month. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## 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. ## 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. > **Trick to solving** > > Name the output grain before you write a single aggregate: one row per calendar month. Once the grain is fixed, every column is forced. The bucket expression you choose is both the SELECT key and the GROUP BY key, so they stay locked together. --- ### 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 **Monthly revenue rollup** ```sql 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 pitfall** > > Because the YYYY-MM bucket is zero-padded and leads with the year, sorting it as text already lands in chronological order. If you had built the key as a bare month number, or dropped the year, the ordering would jumble across year boundaries and sort 10 before 2. Keep the full YYYY-MM string and ORDER BY stays honest. > **Interviewers watch for** > > Stating the grain out loud (one row per month) before writing GROUP BY signals you reason about data shape, not just syntax. Deriving the average as SUM over COUNT, rather than reaching for a canned AVG on the wrong grain, shows you know exactly what the denominator is. > **Cost analysis** > > The scan touches all 80M rows of `transactions`, and the aggregation reduces them to one row per year-month before anything downstream runs, so the grouped output is tiny and cost is dominated by the sequential read. transaction_date is the partition key, so the moment you add a date range the engine can prune to just those partitions instead of scanning the whole table. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_shape_of_the_year) - [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.