16  Grouping consecutive rows

Note

Hey, do you think it would be hard to identify a “session” from all these user events we have?

A lot of data we work with can be ordered by time. Sometimes it’s user interactions in a website, sometimes it’s a history of contracts with clients. Everyone encounters such a table eventually. Sometimes we are interested in trying to group those rows together. It’s easier to understand this with an example. Let’s say we’re a company selling a streaming subscription service. Let’s say Alice and Bob are our users. Alice is very conscious of her services - after subscribing for a year, she paused the service and resumed after a month. Bob doesn’t care and auto-renews. If each row is a subscription, Alice has a “break” in her subscription history. Bob doesn’t. If we, say, defined loyal users as those without breaks in their subscription history, how could we identify Bob as loyal?

16.1 General theory of finding consecutive rows

Every way of finding consecutive rows is based on calculating the difference between two ranks in your table. The second rank will be partitioned by all the columns you are interested in so that you have a row number for each partition. Then the first rank will have one column fewer - essentially created a more general rank. The theory is that if both of these ranks are increasing at the same rate, they must be consecutive and belonging to the same group. Once the second rank reaches the end of the partition - and the second rank will always be shorted than the first one since we’re partitioning - the rank counter will reset but the first rank will continue counting up. Now the difference between the ranks is a larger number but if both ranks continue being consecutive, the difference will remain constant because they’ll be increasing by one over every row. The chapter below illustrates this principle in practice.

16.2 Grouping consecutive intervals

Now let’s answer the question on the second order - how could we group consecutive intervals? For example, if I had subscribed and then renewed my subscription, I have two rows in the database. How could we represent those two rows as a single row with their intervals combined?

What’s different from the previous scenario is that intervals do not have a single column to describe their consecutive-ness. For example, my first subscription might have started on January 1st, then my second one starts on March 1st. Previously, the request date by itself encoded the rank. Now we need to create the rank ourselves.

Most data we’re working with will contain an incremental id, a timestamp or a creation date. Sometimes, we are interested in grouping rows if the timestamp is consecutive between rows or if the rows have some desired distance between them. For example, we might want to create a session id if events are within 1 hour of each other. Or we might want to group rows if their dates are consecutive. In SQL-land, they’re called “islands”1. For me, it’s easier to visualise the concept using the interactive table above.

Gaps are the inverse problem - if there is a gap between two consecutive rows, we would like to infer new rows that show when/where the gap begins and when/where it ends.

There are actually a few use cases for using these methods, so we’ll go through all of them.

16.3 Grouping consecutive rows


with tmp as (
  select
    row_number() over (partition by user_id order by date_from) as user_rank,
    row_number() over (partition by user_id, plan_id order by date_from) as tier_rank,
    user_id,
    date_from,
    date_to,
    plan_id
  from subscriptions
)
select
  user_id,
  plan_id,
  min(date_from) as date_from,
  max(date_to) as date_to,
  count(*)
from tmp
group by user_id, plan_id, (user_rank-tier_rank)
Displaying records 1 - 10
user_id plan_id date_from date_to count_star()
40 1 2024-02-26 2024-03-27 1
7 4 2024-02-10 2024-03-11 1
7 2 2023-06-01 2023-07-31 1
10 1 2023-06-01 2024-11-22 4
15 4 2023-11-28 2023-12-28 1
14 3 2023-12-28 2024-02-26 1
11 4 2024-10-23 2025-04-21 1
54 3 2023-11-26 2023-12-26 1
47 1 2023-10-13 2024-01-25 2
1 2 2023-06-01 2023-09-29 1

However, some of the users had gaps in their subscription history. In other words, they sometimes paused their subscriptions, so there are intervals when they were not subscribed. If we wanted to group subscription intervals that were consecutive, what we need is a new column that indicates whether the new subscription started right after the previous one. In a sense, we are deriving a new column with which we will run the same islands recipe.


select 
    *,
    lag(date_to,1) over (partition by user_id order by date_from) as lag_date_to,
    date_from-1 as date_from_minus_1,
    coalesce(lag(date_to,1) over (partition by user_id order by date_from), date_from-1) = (date_from - 1) as is_equal
  from subscriptions
  order by user_id, date_from
Displaying records 1 - 10
subscription_id user_id plan_id date_from date_to lag_date_to date_from_minus_1 is_equal
1 1 2 2023-06-01 2023-09-29 NA 2023-05-31 TRUE
2 1 1 2023-12-28 2024-02-26 2023-09-29 2023-12-27 FALSE
3 1 1 2024-05-26 2024-09-23 2024-02-26 2024-05-25 FALSE
4 1 3 2024-12-22 2025-01-21 2024-09-23 2024-12-21 FALSE
5 2 1 2023-06-01 2023-07-01 NA 2023-05-31 TRUE
6 2 1 2023-07-01 2023-09-29 2023-07-01 2023-06-30 FALSE
7 2 2 2023-09-29 2023-10-29 2023-09-29 2023-09-28 FALSE
8 2 1 2023-11-28 2024-05-26 2023-10-29 2023-11-27 FALSE
9 3 1 2023-06-01 2023-07-01 NA 2023-05-31 TRUE
10 3 2 2023-07-01 2023-10-29 2023-07-01 2023-06-30 FALSE 

with lag_date_from as (
  select 
    *,
    case when coalesce(lag(date_to,1) over (partition by user_id order by date_from), date_from-1) = (date_from-1) then 1 else 0 end as is_sub_consecutive
  from subscriptions
),
tmp as (
  select
    row_number() over (partition by user_id order by date_from) as user_rank,
    row_number() over (partition by user_id, plan_id, coalesce(is_sub_consecutive,1) order by date_from) as consecutive_tier_rank,
    user_id,
    is_sub_consecutive,
    plan_id,
    date_from,
    date_to
  from lag_date_from
)
select 
  user_id,
  plan_id,
  min(date_from) as date_from,
  max(date_to) as date_to
from tmp
group by user_id, plan_id, (user_rank-consecutive_tier_rank)
order by user_id, date_from
Displaying records 1 - 10
user_id plan_id date_from date_to
1 2 2023-06-01 2023-09-29
1 1 2023-12-28 2024-09-23
1 3 2024-12-22 2025-01-21
2 1 2023-06-01 2023-07-01
2 1 2023-07-01 2023-09-29
2 2 2023-09-29 2023-10-29
2 1 2023-11-28 2024-05-26
3 1 2023-06-01 2023-07-01
3 2 2023-07-01 2023-10-29
3 3 2023-10-29 2024-02-26 
with aggregate as (
  select request_submitted_at, count(*) as request_count
  from requests
  group by request_submitted_at
),
tmp as (
  select
    *,
    case when request_count > 2 then 1 else 0 end as is_high_request_day
  from aggregate
),
windowed_tmp as (
  select 
  *,
  row_number() over (order by request_submitted_at) as request_rank,
  row_number() over (partition by is_high_request_day order by request_submitted_at) as high_request_day_rank
  from tmp
)
select 
  is_high_request_day,
  min(request_submitted_at) as request_start,
  max(request_submitted_at) as request_end
from windowed_tmp
group by is_high_request_day, request_rank-high_request_day_rank
order by request_start

/*select *
from windowed_tmp
order by request_submitted_at*/
with aggregate as (
  select request_submitted_at, count(*) as request_count
  from requests
  group by request_submitted_at
  having request_count > 2
)
select 
  *,
  request_submitted_at - interval (dense_rank() over (order by request_submitted_at)) day as date_group
from aggregate
with aggregate as (
  select request_submitted_at, count(*) as request_count
  from requests
  group by request_submitted_at
  having request_count > 2
),
aggregate_consecutive as (
select 
  *,
  case 
    when coalesce(lag(request_submitted_at) over (order by request_submitted_at) +1, request_submitted_at) = request_submitted_at 
    then 1
    else 0 
    end as is_consecutive
from aggregate
)
select *
from

https://dba.stackexchange.com/questions/193680/group-rows-by-uninterrupted-dates

16.4 Grouping overlapping rows

16.5 Grouping rows that have a minimal distance

https://stackoverflow.com/questions/53519702/group-rows-with-that-are-less-than-15-days-apart-and-assign-min-max-date

16.6 End of chapter exercises

  1. So the islands solution groups by rank but also groups by group type. Could we remove that column maybe? What happens when we do?
  2. What would the query look like if I wanted to create islands based on an additional column?

  1. As far as I can find the term “Islands and Gaps”, I think it was first coined in the SQL Server MVP Deep Dives book.↩︎