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 is to 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 months where a percentage is simply not defined: get those 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 instead of using 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 take SUM(total_amount) per month in the monthly CTE. This yields one revenue total per month that actually has transactions, which is 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). 'YYYY-MM' strings sort lexicographically in true chronological order, so 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 an engine that keeps integer operands integer cannot truncate the ratio, and the multiplication happens before the rounding.

Step 4: Guard the undefined cases

Emit null wherever a percentage is not defined. That covers the first month, where LAG returns null because there is no prior row, and any month whose prior month summed to 0. On this data only 2022-01 comes out null: the voided sale and the -45.00 refund are absorbed by their months' other sales. The zero guard is insurance a ledger with refunds needs, not a branch this data takes.

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. There is no join predicate to get wrong, and it runs over the tiny grouped result instead of 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.)
• With `transactions` partitioned by `transaction_date` at hundreds of millions of rows, 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 on a large table.)