# Ebb and Flow

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

Canonical URL: <https://datadriven.io/problems/ebb_and_flow>

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.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 57 rows:

| month | revenue | pct_change |
| --- | --- | --- |
| 2022-01 | 855.12 | NULL |
| 2022-02 | 1528.62 | 78.76 |
| 2022-03 | 696.96 | -54.41 |
| 2022-04 | 1259.22 | 80.67 |
| 2022-05 | 562.26 | -55.35 |

## 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.

> **Collapse to months before you look back**
>
> Reduce to one row per month first, so the window has a clean series to walk. Then reach the prior month's value with `LAG(revenue) OVER (ORDER BY month)`. Do the arithmetic in the outer query, where the undefined cases can be guarded in the same place.

---

### 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

**LAG for month-over-month percentage change**

```sql
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;
```

> **Aggregate first and the window is free**
>
> The query scans `transactions` once and aggregates it down to a few dozen monthly rows before anything else runs. `LAG` then walks that tiny grouped result, never the raw table, so the window and the final sort cost almost nothing; the whole expense is the single grouped scan. When callers add a date range, partitioning on `transaction_date` prunes the scan to the relevant partitions.

> **Name the grain and the null months out loud**
>
> Name the output grain ('one row per month') before writing the `GROUP BY`; it shows you think about data shape, not just syntax. Calling out the undefined months before being asked signals production awareness, and so does asking whether an empty month should break the chain or be skipped.

> **Divide as reals, and never by zero**
>
> In an engine that keeps integer operands integer, writing `(revenue - prev_revenue) / prev_revenue * 100` truncates the ratio to 0 before you multiply. Force real division with a `100.0` multiplier. The subtler trap is a prior month of exactly zero revenue: without the guard, that row errors in a strict engine or emits a meaningless percentage.

**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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/ebb_and_flow)
- [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.