title: BigQuery Join Using Most Recent Row
body: <p>I have seen variations of this question but have been searching StackOverflow for almost a week now trying various solutions and still struggling with this. Really appreciate you taking the time to consider my question.</p>
  <p>I am working on a research project in GCP using BigQuery. I have a table <code>result</code> of ~100 million rows of events where there is a <strong>session_id</strong> column that relates to the session that the event originated from. I would like to join this with another table <code>status</code> of about 40 million rows that has that same <strong>session_id</strong> and tracks the status of those sessions. Both tables have a time column. In the <code>result</code> table, this is the time of the event. In the <code>status</code> table this is the time of any status changes. I want to join the rows in the <code>result</code> table with the corresponding row in the <code>status</code> table for the most recent state of the session up to or before the time of the event using the session ID. The result would be that each row in the <code>result</code> table would have the corresponding information about the state of the session when the event occurred.</p>
  <p>How can I achieve this? Any way to do it that won't be really inefficient? Thank you so much for your help!</p>