The Slow Build

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

Audible

Problem

Each row of transactions is one sale with its total_amount and transaction_date, and a month's revenue is the sum of its sales, refunds included. List every month of 2026 from the first onward with its revenue next to the average monthly revenue of all months so far, that month included, as a whole-dollar integer with the cents dropped.

Month over month, the number grows. Track how the average moves with it.

Tables

transactions

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

products

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

Expected output

Worked solution and explanation

What this problem really is

Under the revenue-tracker costume, this is a two-level aggregation: an average of monthly totals, scoped to every month so far, then cut to whole dollars. Anyone can sum a month. What separates candidates is putting the cumulative average on top of the grouped totals instead of the raw total_amount rows, and noticing that 'cents dropped' is not 'rounded'. Average the raw rows and every figure collapses to a per-sale number in the tens of dollars. Round instead of truncating and February reads 9818 where the books say 9817.

Build it in two levels

Step 1: Total each month of the year

Keep only 2026 rows with strftime('%Y', transaction_date) = '2026', bucket by strftime('%Y-%m', transaction_date), and take SUM(total_amount). The refund and the voided zero sale stay in: revenue is net. No products join is needed, because revenue lives entirely on transactions.

Step 2: Make the monthly totals the rows

Wrap that aggregate in a subquery so each month is one row with one monthly_revenue. This is the crux: the average must read nine monthly figures, not two hundred sales.

Step 3: Scope the average to the months so far

AVG(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) averages January through the current month. Without the ordered frame, every row would show the same full-year figure.

Step 4: Drop the cents with an integer cast

CAST(... AS INTEGER) truncates toward zero, which is what dropping the cents means. ROUND would push 9817.5 up to 9818, so it is the wrong tool here.

The solution

SELECT month, monthly_revenue,
  CAST(AVG(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS INTEGER) AS cumulative_avg
FROM (
  SELECT strftime('%Y-%m', transaction_date) AS month,
         SUM(total_amount) AS monthly_revenue
  FROM transactions
  WHERE strftime('%Y', transaction_date) = '2026'
  GROUP BY strftime('%Y-%m', transaction_date)
)
ORDER BY month

Rounded average

ROUND(AVG(...)) gives February 9818 and May 9298. Both are a dollar more than the year has actually averaged.

Truncated average

CAST(AVG(...) AS INTEGER) gives February 9817 and May 9297: the cents are dropped, never carried up.

Common follow-up questions

• How would this change for a 3-month rolling average instead of a cumulative one? (The frame becomes `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`, and the first two months average fewer values.)
• What if the average should span every year but reset each January? (Drop the year filter and add `PARTITION BY substr(month, 1, 4)` to the window.)
• What if the cents must be kept to two places instead? (The integer cast goes away and `ROUND(..., 2)` takes its place, which changes the type the output carries.)