I have a table snapshot, storing snapshots (of the new state) every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to a specific value in a given time frame.

For example, assume the following layout:

    CREATE TABLE snapshot
        "timestamp" timestamp with time zone NOT NULL,
        person text NOT NULL,
        haircolor text NOT NULL,
        city text NOT NULL,
        CONSTRAINT snapshot_pkey PRIMARY KEY (person, "timestamp")

Here, I want to find all persons that moved (from any other city) to NY between 2006-02-01 and 2006-02-14. I want all of them, even the ones that moved in on 2006-02-03 and moved out on 2006-02-05 again. (And of course I do not want to list people who only changed their haircolor in the given time frame).

What would an efficient query look like?


I came up with the following query:

    with city_before as (
        select person, city
        from (
                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 < '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 <> s.city and s.city = 'NY'
    ) _;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.