Consecutive groups in SQL using window functions

I have a database table with all of my Swarm checkins since 2011, created using my swarm-to-sqlite tool.

I wanted to run a query to show the date ranges I had spent in different countries, based on the country of the venue of each of those checkins.

I ended up learning how to perform operations against groups of rows identified by a consecutive sequence of values in a column, using window functions.

Here's a simplified version of the query I ended up with (the final query with joins is here):

with ordered as (
  select 
    created,
    country,
    lag(country) over (order by created desc)
      as previous_country
  from 
    raw
),
grouped as (
  select 
    country, 
    created, 
    count(*) filter (
      where previous_country is null
      or previous_country != country
    ) over (
      order by created desc
      rows between unbounded preceding
      and current row
    ) as grp
  from 
    ordered
)
select
  country,
  date(min(created)) as start,
  date(max(created)) as end,
  cast(
    julianday(date(max(created))) -
    julianday(date(min(created))) as integer
  ) as days
from 
  grouped
group by
  country, grp
order by
  start desc;

Here's the raw data I ran that against:

And the output of the query:

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:

with ordered as (
  select 
    created,
    country,
    lag(country) over (order by created desc)
      as previous_country
  from 
    raw
)
select * from ordered

Truncated output:

The magic here is this window function:

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:

with ordered as (
  select 
    created,
    country,
    lag(country) over (order by created desc)
      as previous_country
  from 
    raw
),
grouped as (
  select 
    country, 
    created, 
    count(*) filter (
      where previous_country is null
      or previous_country != country
    ) over (
      order by created desc
      rows between unbounded preceding
      and current row
    ) as grp
  from 
    ordered
)
select * from grouped

Truncated output:

This window function is more complicated:

count(*) filter (
  where previous_country is null
  or previous_country != country
) over (
  order by created desc
  rows between unbounded preceding
  and current row
) 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:

...
select
  country,
  date(min(created)) as start,
  date(max(created)) as end,
  cast(
    julianday(date(max(created))) -
    julianday(date(min(created))) as integer
  ) as days
from 
  grouped
group by
  country, grp
order by
  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.

Related

• sqlite Lag window function in SQLite - 2020-04-19
• sql Cumulative total over time in SQL - 2021-09-13
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• datasette Querying for GitHub issues open for less than 60 seconds - 2021-03-12
• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• sqlite Comparing database rows before and after with SQLite JSON functions - 2022-12-14
• sqlite The simplest recursive CTE - 2022-03-20
• sqlite Identifying column combination patterns in a SQLite table - 2021-01-12

Created 2023-06-08T19:59:22+01:00 · Edit