I have two tables in BigQuery:
table 1:
x
EmailName SentDateTime
Checkout 01/01/2020 12:48
Checkout 15/01/2020 06:21
Checkout 16/01/2020 09:23
table 2:
EmailName EventType EventDateTime
Checkout Open 02/01/2020 13:29
Checkout Open 11/01/2020 12:49
Checkout Open 03/01/2020 14:30
Checkout Click 11/01/2020 12:51
Checkout Open 16/01/2020 07:30
Checkout Open 16/01/2020 10:00
I would like to add the nearest SentDateTime where the EventDateTime is greater than than the SentDateTime. So outcome should look like this:
EmailName EventType EventDateTime SentDateTime
Checkout Open 02/01/2020 13:29 01/01/2020 12:48
Checkout Open 11/01/2020 12:49 01/01/2020 12:48
Checkout Open 03/01/2020 14:30 01/01/2020 12:48
Checkout Click 11/01/2020 12:51 01/01/2020 12:48
Checkout Open 16/01/2020 07:30 15/01/2020 06:21
Checkout Open 16/01/2020 10:00 16/01/2020 09:23
Advertisement
Answer
Below is for BigQuery Standard SQL
#standardSQL
SELECT ANY_VALUE(e).*, MAX(SentDateTime) SentDateTime
FROM `project.dataset.table2` e
JOIN `project.dataset.table1` s
ON e.EmailName = s.EmailName
AND EventDateTime > SentDateTime
GROUP BY FORMAT('%t', e)