Day-over-Day Change with LAG and LEAD — SQL

Solving tips

LAG pulls the previous row's value into the current row and LEAD pulls the next row's — both need an ORDER BY to define previous and next.
The first row has no previous (LAG is NULL) and the last has no next (LEAD is NULL); any arithmetic on them yields NULL unless you supply a default.
Multiply by 100.0 (not 100) before dividing so the percentage keeps its decimals instead of doing integer division.

You track daily active users (DAU). The interviewer wants the previous day’s value, the next day’s value, the day-over-day change, and the percent change, all on one row.

Schema

CREATE TABLE daily_active_users (
    day DATE,
    dau INT
);

day	dau
2024-03-01	1000
2024-03-02	1100
2024-03-03	1050
2024-03-04	1300
2024-03-05	1290

Task

For each day return dau, prev_dau (previous day’s DAU via LAG), next_dau (next day’s DAU via LEAD), delta (dau - prev_dau), and pct_change (delta / prev_dau as a percentage, rounded to 1 decimal). Leave prev_dau, delta, and pct_change NULL on the first day and next_dau NULL on the last day. Order by day ascending.

Expected output

day	dau	prev_dau	next_dau	delta	pct_change
2024-03-01	1000		1100
2024-03-02	1100	1000	1050	100	10.0
2024-03-03	1050	1100	1300	-50	-4.5
2024-03-04	1300	1050	1290	250	23.8
2024-03-05	1290	1300		-10	-0.8

Approach

LAG(dau) and LEAD(dau) over ORDER BY day give each row direct access to the neighboring days without a self-join. Compute those first, then derive delta and pct_change from dau and the lagged value. Both derived columns fall out as NULL on the first row automatically, since prev_dau is NULL there.

Query

SELECT
    day,
    dau,
    LAG(dau)  OVER (ORDER BY day) AS prev_dau,
    LEAD(dau) OVER (ORDER BY day) AS next_dau,
    dau - LAG(dau) OVER (ORDER BY day) AS delta,
    ROUND(
        100.0 * (dau - LAG(dau) OVER (ORDER BY day))
        / LAG(dau) OVER (ORDER BY day),
        1
    ) AS pct_change
FROM daily_active_users
ORDER BY day;

Walkthrough

2024-03-01: no earlier row, so LAG is NULL; delta and pct_change are NULL too. LEAD sees 2024-03-02, so next_dau = 1100.
2024-03-02: prev_dau = 1000, delta = 1100 - 1000 = 100, pct = 100 * 100 / 1000 = 10.0.
2024-03-03: delta = 1050 - 1100 = -50, pct = 100 * -50 / 1100 = -4.545... → -4.5.
2024-03-04: delta = 1300 - 1050 = 250, pct = 100 * 250 / 1050 = 23.809... → 23.8.
2024-03-05: delta = 1290 - 1300 = -10, pct = 100 * -10 / 1300 = -0.769... → -0.8; LEAD is NULL (last row).

Complexity & notes

Repeating LAG(dau) OVER (ORDER BY day) three times is fine — identical window expressions are computed once by the planner. For readability you can instead wrap the LAG/LEAD columns in a CTE and do the arithmetic in the outer query.
LAG/LEAD accept a second arg for the offset and a third for a default, e.g. LAG(dau, 1, 0) would return 0 instead of NULL for the first row — but that would make delta/pct_change non-NULL, which the task does not want here.
100.0 * forces numeric arithmetic; writing 100 * with integer columns risks integer division truncating the fraction in some engines.
If prev_dau could be 0, the division would raise a divide-by-zero — guard with NULLIF(LAG(dau) OVER (ORDER BY day), 0).
Add PARTITION BY (e.g. per country) if the series should reset per group rather than run across the whole table.