解答
CHAPTER 9
031
SQL文（クエリ）
WITH master AS (
SELECT user_pseudo_id, event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
IF(event_name = "purchase", 1, 0) AS conversion
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201101`
), master2 AS (
SELECT user_pseudo_id,
IF(event_name = "scroll" AND page_title = "New | Google Merchandise Store", TRUE, FALSE) AS key_page_scroll,
conversion
FROM master
), master3 AS (
SELECT user_pseudo_id, MAX(key_page_scroll) AS key_page_scrolls, MAX(conversion) AS conversions
FROM master2
GROUP BY user_pseudo_id
)
SELECT key_page_scrolls, COUNT(DISTINCT user_pseudo_id) AS users, SUM(conversions) AS cv_users,
ROUND(SUM(conversions) / COUNT(DISTINCT user_pseudo_id), 3) AS user_cvr
FROM master3
GROUP BY key_page_scrolls
ORDER BY 1
結果テーブル
解説
できるだけデータの加工状況がわかりやすいよう、WITH句を3つ利用しました。
最初のWITH句であるmasterでは、集計に必要となる情報をすべて取得しています。2つ目ののWITH句であるmaster2は、特定ページをスクロールした場合に「TRUE」、そうでない場合に「FALSE」のフラグを立てています。3つ目のWITH句であるmaster3では［user_pseudo_id］で、スクロールしたユーザかどうかを表す［key_page_scrolls］、コンバージョンしたユーザーであるかどうかを示す［conversions］を取得しています。
取得にあたっては、両者ともMAX関数を利用しています。したがって、特定ページを一度でもスクロールしたユーザー（user_pseudo_id）には「TRUE」が、コンバージョンしたユーザーには何度コンバージョンした場合でも「1」がフラグとして立っています。
WITH句を数珠つなぎで3つも利用したので、メインのSQL文はかなりシンプルになっています。［key_page_scrolls］でグループ化し、ユーザー数を固有の［user_pseudo_id］の種類数でコンバージョンしたユーザーを［convsersions］の合計で集計しています。
032
SQL文（クエリ）
WITH master AS (
SELECT user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
IF(event_name = "purchase", 1, 0) AS conversion
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201101`
WHERE device.category = "mobile"
),master2 AS (
SELECT CONCAT(user_pseudo_id, ga_session_id) AS unique_session_id
, MAX(ga_session_number) AS ga_session_number
, MAX(conversion) AS conversion
FROM master
group by user_pseudo_id, ga_session_id
)
SELECT ga_session_number, COUNT(DISTINCT unique_session_id) AS sessions,
SUM(conversion) AS conversions,
ROUND(SUM(conversion) / COUNT(DISTINCT unique_session_id), 5) AS cvr
FROM master2
GROUP BY ga_session_number
HAVING ga_session_number <= 5
ORDER BY 1
結果テーブル
解説
数珠つなぎのWITH句を利用しています。最初の仮想テーブル［master］では、メインのSQL文で集計を必要とするフィールドを単純に取得しています。その際、［device.category］を"mobile"に絞り込んでいます。
2番目の仮想テーブルmaster2では［user_pseuso_id］と［ga_session_id］を文字連結した値を［unique_session_id］として定義し、グループ化したうえで、［ga_session_number］および、コンバージョンしたかどうかのフラグである［conversion］をどちらもMAX関数で集計し、取得しています。
メインのSQL文は仮想テーブルmaster2を対象に［ga_session_number］でグループ化しています。集計は、固有の［unique_session_id］の個数をセッションとして、［conversion］の合計をコンバージョン数としてを取得しています。
033
SQL文（クエリ）
WITH april AS (
SELECT query
FROM jissen.searchdata_site_impression
WHERE query IS NOT NULL AND data_date BETWEEN "2024-04-01" AND "2024-04-30"
GROUP BY query
ORDER BY SUM(clicks) DESC
LIMIT 10
), may AS (
SELECT query
FROM jissen.searchdata_site_impression
WHERE query IS NOT NULL AND data_date BETWEEN "2024-05-01" AND "2024-05-31"
GROUP BY query
ORDER BY SUM(clicks) DESC
LIMIT 10
)
SELECT query, SUM(impressions) AS sum_imp,
SUM(clicks) AS sum_clk,
ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS ctr,
ROUND(SUM(sum_top_position) / SUM(impressions) + 1, 1) AS avg_pos
FROM jissen.searchdata_site_impression
WHERE data_date BETWEEN "2024-05-01" AND "2024-05-31"
GROUP BY query
HAVING query IN (SELECT query FROM april EXCEPT DISTINCT SELECT query FROM may)
ORDER BY sum_clk DESC
結果テーブル
解説
WITH句を2つ作成しています。1つは「april」と名前をつけた4月のクリック数トップ10のクエリ（query）の仮想テーブル、もう1つは「may」と名前をつけた、5月のクリック数トップ10のクエリ（query）の仮想テーブルです。
本体のSQL文ではクエリ（query）でグループ化したうえで、5月についての表示回数合計、クリック数合計、クリック率、平均掲載順位を集計しています。肝心の「4月にはトップ10に入っていたが、5月には入らなくなってしまった」の条件はHAVING句で［query］が、aprilからmayを差し引く集合演算をして残った［query］に一致するという条件を適用しています。
034
SQL文（クエリ）
WITH master AS (
SELECT DATE_TRUNC(data_date, MONTH) AS year_month, url,
SUM(impressions) AS sum_imp
FROM jissen.searchdata_url_impression
WHERE url NOT LIKE ("%sql%") AND query LIKE ("%sql%")
GROUP BY year_month, url
)
SELECT * FROM master
QUALIFY RANK() OVER(PARTITION BY year_month ORDER BY sum_imp DESC) <= 3
ORDER BY year_month, sum_imp DESC
結果テーブル
解説
WITH句で、問題文にある「表示回数合計3つまで」という条件以外を満たす仮想テーブルを作成しておき、本体のクエリでQUALIFY句を利用して、RANK関数が出力したランキングを表す整数を、3以下だけに絞り込んでいます。
別解は以下です。WITH句を必ずしも使わなくとも同じ結果を得ることができます。
SELECT DATE_TRUNC(data_date, MONTH) AS year_month, url,
SUM(impressions) AS sum_imp
FROM jissen.searchdata_url_impression
WHERE url NOT LIKE ("%sql%") AND query LIKE ("%sql%")
GROUP BY year_month, url
QUALIFY RANK() OVER(PARTITION BY year_month ORDER BY sum_imp DESC) <= 3
ORDER BY year_month, sum_imp DESC
035
SQL文（クエリ）
WITH master AS (
SELECT
* EXCEPT(hyouka),
SPLIT(hyouka) AS array_hyouka
FROM jissen.V_g_form
)
SELECT row_hyouka, COUNT(*) AS respondent_count, ROUND(AVG(nps), 2) AS avg_nps
FROM (
SELECT nps, row_hyouka
FROM master
CROSS JOIN UNNEST(array_hyouka) AS row_hyouka
)
GROUP BY row_hyouka
ORDER BY 3 DESC
結果テーブル
解説
上記の解答は、午前10時前の解答を除外した、ビュー［V_g_form］を利用した回答例です。ビューを利用しない場合には、WITH句を以下の通りに変更する必要があります。
WITH master AS (
SELECT
* EXCEPT(hyouka),
SPLIT(hyouka) AS array_hyouka
FROM jissen.g_form
WHERE
PARSE_DATETIME("%Y/%m/%d %H:%M:%S", timestamp) >= DATETIME("2024-05-05 10:00:00")
)
WITH句で、SPLIT関数を利用して［hyouka］カラムの内容を配列に変更しておき、本体のクエリでUNNESTしてフラット化した1列n行のテーブルを、元のテーブルにCROSS JOINしています。
あとは、評価コメントが格納された［row_hyouka］の内容ごとに、行数をカウントしたものをユーザー数［nps］をAVG関数で平均したものを［avg_nps］として取得しています。
