mysql - How to join two tables by matching the date in one table with the date closest to and before in another table

Question

I am looking for a query to try and get the set of world rankings prior to each event. This is eventually to be used in a linear regression model to see the accuracy of predicting event results with world ranking. NB. there can be multiple tournaments on the same day.

question from:https://stackoverflow.com/questions/65847753/how-to-join-two-tables-by-matching-the-date-in-one-table-with-the-date-closest-t

Answer 1

You can use lag() to get a time period before each event. Then join:

select e.*, r.*
from rankings r left join
     (select e.*,
             lag(event_date) over (order by event_date) as prev_event_date
      from events e
     ) e
     on r.ranking_date < event_date and
        (r.ranking_date >= prev_event_date or prev_event_date is null)