extract state changes from snapshot-based archive table
I came up with the following query:

    with city_before as (
        select person, city
        from (
            select 
                person, 
                timestamp, 
                first_value(timestamp) over (partition by person order by timestamp desc) as latest_timestamp, 
                first_value(city) over (partition by person order by timestamp desc) as city
            from snapshot
            where timestamp &lt; '2006-02-01'
        ) _
        where timestamp = latest_timestamp
    ),
    city_during as (
        select person, city
        from snapshot
        where timestamp between '2006-02-01' and '2006-02-15'
        group by person, city
    )
    select distinct person
    from (
        select s.person, b.city as city_before, s.city as city
        from city_during s
        left outer join city_before b on b.person = s.person
        where b.city &lt;&gt; s.city and s.city = 'NY'
    ) _;