How to retrieve the first session after a purchase

To do this calculation, you need to retrieve the orders nth purchase and filter for the 1st order, this can be easily pulled out of the reports.order_primary which has this information in the parameters (name = ‘nth_purchase’, value = 1) and to then join it to all the reports.session_primary matching the same user persona id and having a session_start_ts bigger than the first order timestamp (order_tm).

The trick here is to use the ROW_NUMBER aggregation function ROW_NUMBER function (more examples here).

select
session_id
from (
  select

    s.session_id,
    ROW_NUMBER() OVER (
      PARTITION BY s.user_persona_id 
      ORDER BY s.session_start_ts ASC
  ) AS rank,

  from [ACCOUNT_NAME]_reports.order_primary as o
  join unnest(parameters) as parameter 
    on parameter.name = 'nth_order' and parameter.value = '1'
  join [ACCOUNT_NAME]_reports.session_primary as s 
    on s.user_persona_id = o.persona_id and s.session_start_ts > o.order_tm
)
where rank = 1

Replace [ACCOUNT_NAME] with your account name.