SQL(BigQuery) Join two tables with lag() function

Question

Considering I have a two tables.

First one:

user_id	name	timestamp1
1	purchase	12
1	purchase	14
2	purchase	22
2	purchase	14

Second one:

user_id	event_name	timestamp2
1	event1	10
1	event2	11
2	event12	20
2	event10	12

A want to add to the table one some fields(event_name, timestamp2) from the table two with the closest previous values by user_id, order by timestamp for every event from table one

Desired table should look like this

user_id	name	timestamp1	event_name	timestamp2
1	purchase	12	event2	11
1	purchase	14	event2	11
2	purchase	22	event12	20
2	purchase	14	event10	12

Help me please with sql query! Thanks.

Answer 1

You can use join on user_id then using row_number() ordered by the distance between timestamp1 and timestamp2 to get the closest row from table2:

SELECT  user_id, name, timestamp1, event_name, timestamp2
FROM (
  SELECT    t1.*, t2.event_name, t2.timestamp2,
            ROW_NUMBER() OVER(PARTITION BY t1.user_id, t1.timestamp1 ORDER BY ABS(t1.timestamp1 - t2.timestamp2)) AS rn
  FROM      table1 t1
  INNER JOIN table2 t2
  ON        t1.user_id = t2.user_id 
)
WHERE rn = 1

Output:

Answer 2

select any_value(t1).*, 
  array_agg(struct(event_name,timestamp2) order by timestamp2 desc limit 1)[offset(0)].*
from `project.dataset.table1` t1 
cross join `project.dataset.table2` t2
where t2.user_id = t1.user_id and timestamp2 < timestamp1
group by format('%t', t1)    

if to apply to sample data in your question - output is