Select nearest date from another table SQL

I have two tables in BigQuery:

table 1:

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

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)

Advertisement

Answer