SQL問題 テーブルを結合してセグメント別の集計をする(1)

問題:11月1日から11月10日の間に購入したことのある会員とない会員のそれぞれの月別のレコード数を数えよ

kiso1の5つExcelファイルにあるテーブル5つから必要なデータを選ぶこと。 使い方はSQL学習用サンプルデータを参照。

いきなりクエリを書こうとすると難しいので
・11月に購入経験のある会員リストを作る
・そのリストを使ってPOSで区別がつくようにフラグを立てる
・月別、フラグ別で集計する
と段階を経て考える。

フラグについては別のテーブルを結合してフラグを立てるを参考。

解答:
11月購入あり・11月レコード数:157
11月購入なし・11月レコード数:19
11月購入あり・12月レコード数:60
11月購入なし・12月レコード数:10

kiso1_posをわけているだけなのでレコード数が変わったらおかしい、という視点で検算もしておこう。

解説:ヒントに書いた順番でサブクエリを使って段階的に書いたが1つのクエリでももちろん書けるので是非とも挑戦してみて欲しい。

週別であればDATE_TRUNCのmonthをweekに変えればよい。DATE_TRUNCについては日付や時間を切り詰める TRUNCを見直す。

WITH
buy_nov as (
SELECT distinct customer_id
FROM kiso1_pos pos
WHERE date>=’2019-11-01′
AND date<=’2019-11-10′
)
,pos_flag_nov as (
SELECT
DATE_TRUNC(date,month) as month
,CASE WHEN buy_nov.customer_id IS NOT NULL THEN 1 ELSE 0 END flag_nov
FROM kiso1_pos pos
LEFT JOIN buy_nov
USING(customer_id)
)
SELECT
month
,flag_nov
,count(1)
FROM pos_flag_nov
GROUP BY 1,2
ORDER BY 1,2

SQL問題一覧はこちら

2020年2月13日SQL問題

Posted by shinu