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;