Slide 20
Slide 20 text
© )JLBSV5BLBIBTIJ
ଟগվળ͞ΕΔ͕·ͩͳ42-
$IBU(15Λ׆༻ͨ͠σʔλੳʹ͓͚Δ42-ͷੜ
ᶄ$IBU(15 ᶅ42-$3&"503
WITH
-- 2022ʹจ͞Εͨߪೖσʔλͷநग़
order_2022 AS (
SELECT *
FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31'
AND is_canceled = 0
),
-- Ϣʔβʔ͝ͱͷؒߪೖ݅ͷܭࢉ
user_purchase_count AS (
SELECT user_id, COUNT(DISTINCT order_id) AS purchase_count
FROM order_2022
GROUP BY user_id
),
-- ϢʔβʔΛHML͝ͱʹྨ͢Δ
user_hml AS (
SELECT user_id,
CASE
WHEN purchase_count >= 500 THEN 'ϔϏʔ'
WHEN purchase_count >= 300 THEN 'ϛυϧ'
ELSE 'ϥΠτ'
END AS hml
FROM user_purchase_count
),
-- HML͝ͱͷϢʔβʔΛूܭ͢Δ
hml_user_count AS (
SELECT hml, COUNT(DISTINCT user_id) AS user_count
FROM user_hml
GROUP BY hml
)
-- ࠷ऴతͳग़ྗ݁Ռ
SELECT hml, user_count
FROM hml_user_count
ORDER BY hml DESC
WITH purchase_counts AS (
SELECT
user_id,
COUNT(*) AS cnt
FROM
orders
WHERE
is_canceled = 0 AND
order_date BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY
user_id
)
SELECT
CASE
WHEN cnt >= 500 THEN 'ϔϏʔ'
WHEN cnt >= 300 THEN 'ϛυϧ'
ELSE 'ϥΠτ'
END AS HML,
COUNT(*) AS user_count
FROM
purchase_counts JOIN users ON purchase_counts.user_id = users.user_id
WHERE
users.is_deleted = 0
GROUP BY
HML;