couty_visits.sql

with county_visits as (
  select
    timestamp,
    county_name,
    row_number() over (order by timestamp) as row_number
  from joined
),
county_visits_with_next as (
  select
    county_visits.*,
    lead(county_name) over (order by timestamp) as next_county_name,
    lead(row_number) over (order by timestamp) as next_row_number
  from county_visits
),
county_visits_with_next_and_end_time as (
  select
    county_visits_with_next.*,
    lead(timestamp) over (order by timestamp) as end_time
  from county_visits_with_next
),
county_visits_with_end_time as (
  select
    county_visits_with_next_and_end_time.*,
    end_time - timestamp as duration
  from county_visits_with_next_and_end_time
),
select * from county_visits_with_end_time where county_name != next_county_name;