Rolling Revenue Average

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

L5

Asked in

Amazon

Problem

Give the finance team a 3-month rolling average of monthly revenue from the transactions table, oldest month first, where each month's figure averages its own revenue total with the two months before it. Refunds are logged there as negative amounts and are not revenue, and the first two months average over only the history they have.

Smooth out the revenue bumps. The trend matters more.

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 is

This is a two-level query dressed up as a one-line finance ask: first a revenue sum per calendar month, then a moving average over those sums. Everyone can type ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. What separates candidates is what the frame slides over. Put the window on raw transactions rows and '2 PRECEDING' means two transactions, not two months. Every rolling_avg then collapses to roughly one ticket size instead of a three-month revenue figure. The window's input has to be the monthly total, and that total only exists after the aggregation has run.

Build it in two levels

Step 1: Drop refunds before you sum

Refunds sit in the same table as negative total_amount rows, so WHERE total_amount >= 0 has to run before SUM sees them. You cannot fix this in the outer query: once a month is a single number, the refund is already baked into it. The voided sale at 0.00 can stay because it adds nothing to a sum.

Step 2: Collapse to one row per month in monthly_rev

strftime('%Y-%m', transaction_date) turns each date into its year-month bucket. Grouping on that same expression leaves the monthly_rev CTE with exactly one revenue per month. Naming the CTE lets the next level read it like a table at a different grain.

Step 3: Frame the window by rows over months

AVG(revenue) OVER (ORDER BY ym ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) averages each month with the two before it. Every row is now a month, so two rows back is two months back. For the first two months the frame simply shrinks: the oldest month averages one value and the second averages two, with no special case.

WITH monthly_rev AS (
    SELECT strftime('%Y-%m', transaction_date) AS ym, SUM(total_amount) AS revenue
    FROM transactions
    WHERE total_amount >= 0
    GROUP BY strftime('%Y-%m', transaction_date)
)
SELECT ym, AVG(revenue) OVER (ORDER BY ym ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM monthly_rev
ORDER BY ym

-- Wrong: the frame counts transactions, not months
SELECT strftime('%Y-%m', transaction_date) AS ym,
       AVG(total_amount) OVER (ORDER BY transaction_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM transactions
WHERE total_amount >= 0

Common follow-up questions

• How would you report months with no sales so the window still spans three calendar months? (Tests building a month spine with a `LEFT JOIN` and `COALESCE` to zero.)
• Finance now wants the same average per `product_id`. What changes? (Tests adding `PARTITION BY product_id` and grouping on both keys.)
• How would you keep this current daily without rescanning two years of `transactions`? (Tests incremental monthly rollups that recompute only the open month.)