Compare Similar DataSet in BigQuery

Here provide simple SQL Query to compare between page_view, session_start and scroll event. Also, compare the page_view with other 2 to see how much difference between each days.

with page_view as (
  SELECT event_date, count(1) as page_view_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'page_view'
  group by 1
),
session_start as (
  SELECT event_date, count(1) as session_start_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'session_start'
  group by 1
),
scroll as (
  SELECT event_date, count(1) as scroll_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'scroll'
  group by 1
)
select
page_view.event_date
, page_view.page_view_count
, session_start.session_start_count
, scroll.scroll_count
, safe_subtract(page_view.page_view_count, session_start.session_start_count) page_minus_session
, safe_subtract(page_view.page_view_count, scroll.scroll_count) page_minus_scroll
, round(safe_divide(page_view.page_view_count - session_start.session_start_count, session_start.session_start_count) * 100, 2) page_vs_session_percent
, round(safe_divide(page_view.page_view_count - scroll.scroll_count, scroll.scroll_count) * 100, 2) page_vs_scroll_percent
from page_view
inner join session_start on page_view.event_date = session_start.event_date
inner join scroll on scroll.event_date = page_view.event_date
order by event_date