How to retrieve the exit page of a session

To do this calculation, you need to retrieve all the pageViews of a session and recuperate only the last one (the one with the biggest event_tm timestamp).

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

select
  session_id,
  parameter.value as exitPage
from (
  select
    s.session_id,
    pv.parameters,
    ROW_NUMBER() OVER (
      PARTITION BY s.session_id 
      ORDER BY pv.event_tm DESC
    ) AS rank,
  from [ACCOUNT_NAME]_reports.session_primary as s
  join [ACCOUNT_NAME]_views.tracking as pv 
    on pv.event_type = 'pageView' 
      and pv.session_id = s.session_id 
      and date(pv.event_tm) >= [YYYY-MM-DD]
  where date(s.session_start_ts) >= [YYYY-MM-DD]
)
join unnest(parameters) as parameter on parameter.key = 'id'
where rank = 1

Replace [ACCOUNT_NAME] with your account name.

Replace [YYYY-MM-DD] with a date to limit the scope and costs of your query.