Consecutive groups in SQL using window functions#

How that query works#

There are three steps to the query.

1. Extend the raw data with an extra previous_country column which shows the country for the previous row, using the lag() window function.
2. Group the data by country, using another window function to assign a group number to each row. That group number increments whenever the country changes.
3. Group the data by country and group number, and use min() and max() to find the start and end dates for each group and to calculate the number of days in each group.

The first two steps are implemented using CTEs. Let’s break those down:

1
with ordered as (
2
  select
3
    created,
4
    country,
5
    lag(country) over (order by created desc)
6
      as previous_country
7
  from
8
    raw
9
)
10
select * from ordered

Truncated output:

The magic here is this window function:

1
lag(country) over (order by created desc) as previous_country

That over (order by created desc) part means “consider all rows in this table, ordered by the created column in descending order”. lag(country) means “take the value of the country column from the previous row within that window”.

The next CTE adds a grp column which increments whenever the country changes:

1
with ordered as (
2
  select
3
    created,
4
    country,
5
    lag(country) over (order by created desc)
6
      as previous_country
7
  from
8
    raw
9
),
10
grouped as (
11
  select
12
    country,
13
    created,
14
    count(*) filter (
15
      where previous_country is null
16
      or previous_country != country
17
    ) over (
18
      order by created desc
19
      rows between unbounded preceding
20
      and current row
21
    ) as grp
22
  from
23
    ordered
24
)
25
select * from grouped

Truncated output:

This window function is more complicated:

1
count(*) filter (
2
  where previous_country is null
3
  or previous_country != country
4
) over (
5
  order by created desc
6
  rows between unbounded preceding
7
  and current row
8
) as grp

The over clause here defines a window that includes all of the rows up to and including the current one, ordered by created in descending order.

The count(*) filter (...) part counts the number of rows in that window where either previous_country is null or previous_country != country.

The final step is to group by country and group number, and to use min() and max() to find the start and end dates for each group and to calculate the number of days in each group:

1
...
2
select
3
  country,
4
  date(min(created)) as start,
5
  date(max(created)) as end,
6
  cast(
7
    julianday(date(max(created))) -
8
    julianday(date(min(created))) as integer
9
  ) as days
10
from
11
  grouped
12
group by
13
  country, grp
14
order by
15
  start desc;

Grouping by country, grp returns a single row per group. The min(created) and max(created) functions can then find the earliest and latest dates in each group.

I’m using date() to turn those created timestamps into YYYY-MM-DD dates.

Finally, I use julianday() to convert those dates into Julian day numbers, and then subtract the two numbers to get the number of days between them. The result is a floating point number, so I use cast(... as integer) to convert it to an integer.