Ebb and Flow

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Amazon

Problem

A finance team tracks revenue momentum by watching how each month stacks up against the one before it. Walking forward from the earliest month, total each month's transaction revenue and show how it moved, in percent, from the immediately preceding month.

The rise and fall of revenue, one month against the last.

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 period-over-period comparison wearing a revenue-tracking costume. The real skill: line each month up against the one immediately before it and express the delta as a percentage, without losing the first month to a null prior or dividing by a prior that netted to zero. Anyone can sum revenue by month. What separates candidates is the two months where a percentage simply is not defined: get either wrong and you either crash on a divide-by-zero or print a number that means nothing. Note what the solution does NOT do: it groups by the calendar month, not by any transaction attribute, and the comparison walks the ordered months, not a self-join.

Walking through it

Step 1: Roll transactions up to monthly revenue

Bucket every transaction into its calendar month with STRFTIME('%Y-%m', transaction_date) and SUM(total_amount) per month in the monthly CTE. This yields one revenue total per month that actually has transactions, which is exactly the row set the prose's 'each month' implies. A month with no transactions never appears here, which is a design fork worth naming out loud.

Step 2: Reach back to the prior month with LAG

In the sequenced CTE, attach each month's prior-month revenue with LAG(revenue) OVER (ORDER BY month). Because 'YYYY-MM' strings sort lexicographically in true chronological order, ordering the window by that string makes 'the prior month' resolve to the immediately preceding present month, and the result is deterministic.

Step 3: Turn the gap into a percentage

Compute the change as (revenue - prev_revenue) * 100.0 / prev_revenue and ROUND it to two decimals. The 100.0 forces REAL division so the ratio is not truncated to an integer, and the multiplication happens before the rounding.

Step 4: Guard the two undefined cases

Emit null for both cases where a percentage is not well defined: (a) the FIRST month, where LAG returns null because there is no prior row; and (b) any month whose prior present month summed to 0 revenue, where dividing by prev_revenue is undefined. total_amount is REAL and refunds or credits can net a month to zero in this data, so a naive LAG-and-divide would either divide by zero or fabricate a misleading value.

The solution

WITH monthly AS (
    SELECT STRFTIME('%Y-%m', transaction_date) AS month,
           SUM(total_amount) AS revenue
    FROM transactions
    GROUP BY STRFTIME('%Y-%m', transaction_date)
),
sequenced AS (
    SELECT month,
           revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly
)
SELECT month,
       ROUND(revenue, 2) AS revenue,
       CASE
           WHEN prev_revenue IS NULL THEN NULL
           WHEN prev_revenue = 0    THEN NULL
           ELSE ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 2)
       END AS pct_change
FROM sequenced
ORDER BY month;

Self-join on month minus one

Joining each month to 'the previous month' by arithmetic on the month key is brittle: it silently drops the comparison whenever a calendar month has no transactions, and the join predicate gets ugly across year boundaries.

LAG over ordered months

LAG over the chronologically ordered present months always pairs a month with the one immediately before it in the data, with no join predicate to get wrong, and it runs over the tiny grouped result rather than the raw table.

Common follow-up questions

• If a month has zero transactions and never appears in the grouped result, what does your percentage change compare against, and how would you force a dense calendar spine? (Tests whether the candidate understands that the grouped result omits empty months, so LAG compares against the previous PRESENT month, not the previous calendar month.)
• How would you extend this to show each month against its trailing three-month average instead of just the prior month? (Tests generalizing the window from a single prior row to a moving frame.)
• At 250M rows partitioned by `transaction_date`, what keeps this query cheap, and where in the plan does the window function actually execute? (Tests understanding of where the window runs and how the aggregation controls cost at 250M rows.)