Узнаем популярные страницы сайта

WITH pages AS (
--- Достаем количество просмотренных страниц их название, количество сессий пользователя, количество новых пользователей, вовлеченность пользователя и время
SELECT 
    user_pseudo_id,event_name,
    MAX(CASE WHEN key = "page_title" THEN value.string_value ELSE NULL END) AS page,
    MAX(CASE WHEN event_name = 'page_view' and key = 'page_title' THEN value.string_value ELSE NULL END) AS page_title,
    MAX (CASE WHEN params.key = "ga_session_id" THEN params.value.int_value ELSE 0 END) AS sessionId,
    CASE WHEN event_name = "first_visit" then 1 else 0 END AS new_users,
    MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged')) as session_engaged,
    MAX(CASE WHEN key = "engagement_time_msec" then value.int_value else 0 END) AS engagement_time_msec
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST (event_params) AS params
WHERE _table_suffix BETWEEN '20210101' AND '20210131'
GROUP BY 
user_pseudo_id,
event_name),
--- Считаем количество страниц, переводим время в секунды
page_top AS (
SELECT
  user_pseudo_id, 
  event_date, 
  event_timestamp, 
  event_name, 
  MAX(CASE WHEN event_name = 'page_view' AND params.key = "page_title" THEN params.value.string_value END) AS page_count,
  MAX(CASE WHEN params.key = "page_title" THEN params.value.string_value ELSE NULL END) AS page,
  MAX(CASE WHEN params.key = "engagement_time_msec" THEN params.value.int_value/1000 ELSE 0 END) AS engagement_time
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, unnest(event_params) as params
 
WHERE _table_suffix BETWEEN '20210101' AND '20210131'
GROUP BY user_pseudo_id, event_date, event_timestamp, event_name
),
--- Считаем среднее время на странице, количество просмотров, кол-во уникальных польз.,кол-во просмотров страниц на польз.
page_top_summary AS (
SELECT 
  page, 
  ROUND (SAFE_DIVIDE(SUM(engagement_time),COUNT(DISTINCT user_pseudo_id)),2) AS avg_engagement_time,
  COUNT (page_count) AS views,
  COUNT (DISTINCT user_pseudo_id) AS users,
  ROUND(COUNT (page_count)/COUNT (DISTINCT user_pseudo_id),2) AS views_per_user
FROM 
  page_top
GROUP BY 
  page)
--- Результирующих запрос
SELECT 
    sub.page,
    views,
    users,
    new_user,
    views_per_user,
    avg_engagement_time
FROM (
SELECT 
    page,
    SUM (new_users) as new_user,
FROM 
    pages
WHERE page IS NOT NULL
GROUP BY 
    page) sub
--- Объединяем две временные таблицы в единую на основе страниц
LEFT JOIN  page_top_summary
ON  page_top_summary.page = sub.page
ORDER BY 
    users  DESC