Big Query: Join single latest row from second table

user_845294 0
Viewed 1166

I have two tables. One is a list of Orders, and one is a list of Events.

For each Order, I want to join the single last Event that happened (using clicked_at) before the created_at of the Order.

I have tried numerous ways to get this to work and tried several other answers on Stack Overflow but I am struggling to return the correct data.

The sudo logic for the subquery in my mind is something like:

SELECT campaign, user_id, created_at 
FROM `Events`
WHERE order.user_id = user_id AND clicked_at < order.created_at
ORDER created_at DESC
LIMIT 1

Please see the example data below:

# Orders

| order_id | user_id | created_at |
-----------------------------------
| 123      | abc     | 2020-07-04 |
| 456      | abc     | 2020-05-01 |


# Events

| campaign | keyword  | user_id | clicked_at |
----------------------------------------------
| facebook | shoes    | abc     | 2020-07-03 |
| google   | hair     | abc     | 2020-07-01 |

My desired result

# Orders with campaign attribution

| order_id | user_id | created_at | campaign | keyword  |
---------------------------------------------------------
| 123      | abc     | 2020-07-04 | facebook | shoes    |
| 456      | abc     | 2020-06-04 | null     | null     |

Thanks! Alex

google-bigquery mysql
2 Answers

Below is for BigQuery Standard SQL

#standardSQL
SELECT a.*, campaign, keyword
FROM  `project.dataset.orders` a
LEFT JOIN (
  SELECT  
    ANY_VALUE(o).*, 
    ARRAY_AGG(STRUCT(campaign, keyword) ORDER BY clicked_at DESC LIMIT 1)[OFFSET(0)].*
  FROM `project.dataset.orders` o
  JOIN `project.dataset.events` e
  ON o.user_id = e.user_id
  AND clicked_at < created_at
  GROUP BY FORMAT('%t', o)
)
USING(order_id)

if applied to sample data from our question - result is

Row order_id    user_id created_at  campaign    keyword  
1   123         abc     2020-07-04  facebook    shoes    
2   456         abc     2020-05-01  null        null
user_52219440 
with orders as (
  select 123 as order_id, 'abc' as user_id, cast('2020-07-04' as date) as created_at union all
  select 456, 'abc', '2020-05-01'
),
events as (
  select 'facebook' as campaign, 'shoes' as keyword, 'abc' as user_id, cast('2020-07-03' as date) as clicked_at union all
  select 'google', 'hair', 'abc', '2020-07-01'
),
logic as (
  select
    orders.order_id, 
    orders.user_id, 
    orders.created_at, 
    events.clicked_at,
    events.campaign, 
    events.keyword, 
    row_number() over (partition by orders.order_id order by events.clicked_at desc) as rn
  from orders
  left join events 
  on orders.user_id = events.user_id and events.clicked_at < orders.created_at
)
select * except(rn)
from logic 
where rn = 1
user_116438580
Related