google data engineer sql question
Anonymous User
2280

join two tables based on others timestamp

Table A:

username | activity | timestamp
A activity1 2020-01-01 01:00:00
A activity2 2020-01-01 02:00:00
A activity3 2020-01-01 03:00:00
B activity1 2020-01-01 02:00:00
B activity2 2020-01-01 03:00:00
B activity3 2020-01-01 04:00:00
Table B:

username | clickId | timestamp
A click1 2020-01-01 00:00:00
A click2 2020-01-01 01:30:00
A click3 2020-01-01 01:45:00
B click1 2020-01-01 00:00:00
B click2 2020-01-01 00:30:00
B click3 2020-01-01 03:35:00
need to match all the activities in the activities table to the most recent clicks in the clicks table performed by each user

click matched to each activity must be the most recent click that occured prior to the activity timestamp

Output:

A activity1 click1 - since for activity1 click1 is the timestamp smaller than it
A activity2 click3
A activity3 click3
B activity1 click2
B activity2 click2
B activity3 click3
I tried multiple queries but didnt work

Comments (13)