swarm-grouped-countries.sql

with raw as (select venues.country, checkins.created from checkins join venues on checkins.venue = venues.id order by checkins.created desc ), ordered as ( select country, created, lag(country) over (order by created) 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 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 created desc