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