SQL Recipe: Compare with Neighbors
Tags: sql, analytics
Published: 2023-06-05 20:11:58

Suppose we want to compare each data record with its neighbors based on some column value. For example:

-   Compare sales from one month to the previous month (month-over-month or MoM change) or to the same month a year ago (year-over-year or YoY change).
-   Compare financial results for a given period to the same period in the previous year (like-for-like or LFL analysis).
-   Observe the daily difference in stock prices to understand market trends.
-   Calculate the difference in traffic between days of the week to plan capacity changes.

The solution is to use the `lag()` function over an SQL window ordered by target columns.

## Example

Let's compare the company's `expenses` for each month to the previous month in absolute terms:

```sql
select
  year, month,
  expense,
  expense - lag(expense) over w as diff
from expenses
window w as (order by year, month)
order by year, month;
```

```
┌──────┬───────┬─────────┬──────┐
│ year │ month │ expense │ diff │
├──────┼───────┼─────────┼──────┤
│ 2020 │ 1     │  82     │      │
│ 2020 │ 2     │  75     │  -7  │
│ 2020 │ 3     │ 104     │  29  │
│ 2020 │ 4     │  94     │ -10  │
│ 2020 │ 5     │  99     │   5  │
│ 2020 │ 6     │ 105     │   6  │
│ 2020 │ 7     │  95     │ -10  │
│ 2020 │ 8     │ 110     │  15  │
│ 2020 │ 9     │ 104     │  -6  │
│ 2020 │ 10    │ 100     │  -4  │
│ 2020 │ 11    │  98     │  -2  │
│ 2020 │ 12    │ 106     │   8  │
└──────┴───────┴─────────┴──────┘
```

The `lag(value, offset)` function returns the `value` of the record that is `offset` rows behind the current one. By default, the offset is 1 and can be omitted.

Now let's calculate the relative change from month to month:

```sql
select
  year, month, expense,
  round(
    (expense - lag(expense) over w)*100.0 / lag(expense) over w
  ) as "diff %"
from expenses
window w as (order by year, month)
order by year, month;
```

```
┌──────┬───────┬─────────┬────────┐
│ year │ month │ expense │ diff % │
├──────┼───────┼─────────┼────────┤
│ 2020 │ 1     │  82     │        │
│ 2020 │ 2     │  75     │   -9   │
│ 2020 │ 3     │ 104     │   39   │
│ 2020 │ 4     │  94     │  -10   │
│ 2020 │ 5     │  99     │    5   │
│ 2020 │ 6     │ 105     │    6   │
│ 2020 │ 7     │  95     │  -10   │
│ 2020 │ 8     │ 110     │   16   │
│ 2020 │ 9     │ 104     │   -5   │
│ 2020 │ 10    │ 100     │   -4   │
│ 2020 │ 11    │  98     │   -2   │
│ 2020 │ 12    │ 106     │    8   │
└──────┴───────┴─────────┴────────┘
```

## Alternatives

Suppose we want to compare quarterly `sales` with the previous year. This is where the `offset` parameter comes in handy:

```sql
with data as (
  select
    year, quarter,
    lag(amount, 4) over w as prev,
    amount as current,
    round(amount*100.0 / lag(amount, 4) over w) as "increase %"
  from sales
  window w as (order by year, quarter)
)
select
  quarter,
  prev as y2019,
  current as y2020,
  "increase %"
from data
where year = 2020
order by quarter;
```

```
┌─────────┬────────┬────────┬────────────┐
│ quarter │ y2019  │ y2020  │ increase % │
├─────────┼────────┼────────┼────────────┤
│ 1       │ 155040 │ 242040 │ 156        │
│ 2       │ 162600 │ 338040 │ 208        │
│ 3       │ 204120 │ 287520 │ 141        │
│ 4       │ 200700 │ 377340 │ 188        │
└─────────┴────────┴────────┴────────────┘
```

Looking back 4 quarters with `lag(amount, 4)` gives us the same quarter but from the previous year.

There is also a `lead()` function. It works just like `lag()`, except that it looks forward instead of backward.

## Compatibility

All major vendors support the `lag()` and `lead()` window functions. Some of them, such as MS SQL and Oracle, do not support the `window` clause. In these cases, we can inline the window definition:

```sql
select
  year, month, expense,
  expense - lag(expense) over (
    order by year, month
  ) as diff
from expenses
order by year, month;
```

We can also rewrite the query without window functions:

```sql
select
  cur.year, cur.month, cur.expense,
  cur.expense - prev.expense as diff
from expenses cur
left join expenses prev on
  cur.year = prev.year and
  cur.month - 1 = prev.month
order by cur.year, cur.month;
```

Want to learn more about window functions? Read my book — [**SQL Window Functions Explained**](https://antonz.org/sql-window-functions-book/)

_Follow [**@ohmypy**](https://twitter.com/ohmypy) on Twitter to keep up with new posts_