解答
227
SQL文(クエリ)
WITH master AS ( SELECT * , REGEXP_EXTRACT(page, r"[^\?]+") AS page_wo_parameter FROM sample.web_log ) SELECT * , round(bounce / session, 4) AS bounce_rate FROM ( SELECT landing_page , COUNT(DISTINCT session_id) AS session , SUM(bounce) AS bounce FROM ( SELECT CONCAT(cid, session_count) AS session_id , FIRST_VALUE(page_wo_parameter) OVER (PARTITION BY cid, session_count ORDER BY date_time) AS landing_page , COUNT(*) OVER (PARTITION BY cid, session_count) AS pv_per_session , IF(COUNT(*) OVER (PARTITION BY cid, session_count) = 1, 1, 0) AS bounce FROM master ) GROUP BY landing_page ) ORDER BY 4 DESC LIMIT 3