Postgres SQL attribution over time range

I’m using Postgres 11 and trying to figure out if I can do this in a single query.

Suppose I have two tables:

Event1
------
id
uuid
timestamp

Event2
------
uuid
timestamp

When I join these tables by uuid, Event2 rows should be attributed to a particular Event1.id up to when a newer Event1.id is active.

For example, if I have these rows:

Event1:

id | uuid | timestamp
---------------------
10 | abc  | 2020-10-01 01:00:00
20 | abc  | 2020-10-01 02:11:00
30 | abc  | 2020-10-01 03:00:00

Event2:

uuid | timestamp
----------------
abc  | 2020-10-01 01:10:00
abc  | 2020-10-01 01:15:00
abc  | 2020-10-01 02:30:00
abc  | 2020-10-01 03:00:00
abc  | 2020-10-01 03:30:00

I would get:

id | uuid | timestamp
---------------------
10 | abc  | 2020-10-01 01:10:00
10 | abc  | 2020-10-01 01:15:00
20 | abc  | 2020-10-01 02:30:00
30 | abc  | 2020-10-01 03:00:00
30 | abc  | 2020-10-01 03:30:00

Event1.id == 10 would get attributed Event2 items from timestamp 01:00:00 until 02:10:59. Event1.id == 20 would get attributed Event2 items from timestamp 02:11:00 until 02:59:59. Event1.id == 20 would get attributed everything after 03:00:00.

Is this possible in SQL?

Answer

One approach is to use window functions to get the “next” event for each row in event1, and then it’s a simple join from there.

WITH time_window AS (
    SELECT id, uuid, timestamp as start, 
           lead(timestamp) OVER (PARTITION BY uuid ORDER BY timestamp) as end 
    FROM event1
)
SELECT time_window.id, time_window.uuid, event2.timestamp 
FROM time_window, event2 
WHERE time_window.uuid = event2.uuid 
AND event2.timestamp >= time_window.start 
AND (time_window.end IS NULL OR event2.timestamp < time_window.end);