BigQuery - How to do a Left Join by the closest date?

Question

Using BigQuery, How to do a Left Join to Customer Book Table by the closest date on the Customer Score Table? I want to the choose the closest Open_Date_Score that is prior to the Open_Date_Book?

enter image description here

Wanted Results

enter image description here

Answer 1

Basically, partition your data on ID and rank the date for closeness and then filter the date to show only the closest date. This code should point you in the right direction Customer Book Table=cbt Customer Score Table=cst

SELECT
cbt.ID,
cbt.Open_Date_Book,
cst.Score,
cst.Open_Date_Score
FROM
CustomerBookTable cbt
LEFT JOIN (
SELECT
ID,
Open_Date_Score,
Score,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 
ABS(TIMESTAMP_DIFF(Open_Date_Score, 
Open_Date_Book, SECOND))) AS rn
FROM
CustomerScoreTable
JOIN
CustomerBookTable
ON
CustomerScoreTable.ID = CustomerBookTable.ID
WHERE
Open_Date_Score <= Open_Date_Book
) cst ON cbt.ID = cst.ID
WHERE
cst.rn = 1