How do you approach SQL: Day-over-Day Change (LAG)?

LAG(metric, 1) OVER (ORDER BY date) returns the previous row's metric.

What are common mistakes on SQL: Day-over-Day Change (LAG)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Day-over-Day Change (LAG)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. Given `DailyMetric(date, metric)` rows (one per date, sorted) for a single dashboard, return each row with `prev_metric` (the previous day's metric, or null for the first row) and `delta` (today minus yesterday, or null for the first). **For the auto-grader:** return `[{ date, metric, prev_metric, delta }]`.

Example 1

Input: rows = [{date:"2026-01-01",metric:100},{date:"2026-01-02",metric:120},{date:"2026-01-03",metric:90}]

Output: [{date:"2026-01-01",metric:100,prev_metric:null,delta:null},{date:"2026-01-02",metric:120,prev_metric:100,delta:20},{date:"2026-01-03",metric:90,prev_metric:120,delta:-30}]

Constraints

- 0 <= rows.length <= 10^4 - dates are unique and pre-sorted ascending.

SQL: Day-over-Day Change (LAG): approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

LAG(metric, 1) OVER (ORDER BY date) returns the previous row's metric.
metric - LAG(metric) gives the delta. NULL when there is no previous row.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT date,

metric,

LAG(metric) OVER (ORDER BY date) AS prev_metric,

metric - LAG(metric) OVER (ORDER BY date) AS delta

FROM DailyMetric

ORDER BY date;

```

`LAG(col, n, default)` returns the value n rows back; `LEAD` does the opposite. The window's `ORDER BY` defines what "previous" means.

Per-group day-over-day: add `PARTITION BY tenant_id` to reset the window per tenant. Without it, the lag bleeds across tenants and gives wrong answers - a classic interview gotcha.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.