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

Useful sql. Is it possible for you to provide a sample dataset of the tables checkins and venues