【SQL】クエリの可読性がUP！コメントアウトの活用法

SELECT
    user_id  #シャープをつけると、コメントアウトになるよ
FROM
    users

DECLARE d1, d2 DATE;
SET d1 = "2022-8-1";
SET d2 = "2022-8-31";

WITH
purchase_user_list AS (
SELECT
    user_id,
    DATE(purchased_at, "Asia/Tokyo") AS purchased_date,
    RANK() OVER(PARTITION BY user_id ORDER BY purchased_at) AS purchase_times
FROM
    purchases
WHERE
    DATE(user_created_at, "Asia/Tokyo") BETWEEN d1 AND d2
    AND
    DATE(purchased_at, "Asia/Tokyo") BETWEEN d1 AND d2
),

p_count AS (
SELECT
    purchased_date,
    COUNT(user_id) AS n_of_purchase,
    SUM(CASE WHEN purchase_times >= 2 THEN 1 ELSE 0 END) AS n_of_repeat_purchase
FROM
    purchase_user_list
GROUP BY
    purchased_date
)

SELECT
    days,
    p_count.n_of_purchase,
    p_count.n_of_repeat_purchase,
    CASE 
        WHEN p_count.n_of_purchase = 0 OR p_count.n_of_purchase IS NULL THEN 0 
        ELSE p_count.n_of_repeat_purchase / p_count.n_of_purchase 
    END AS repeat_purchase_rate
FROM
    unnest(GENERATE_DATE_ARRAY(d1, d2)) AS days
    LEFT OUTER JOIN p_count
        ON days = p_count.purchase_date
ORDER BY
    days

/*d1からd2間の登録者の購入数中のリピート購入率を、購入日別で算出*/
DECLARE d1, d2 DATE;
SET d1 = "2022-8-1";
SET d2 = "2022-8-31";

WITH
--d1からd2間の登録者の購入レコード。user_id, 購入日, そのユーザーにとって何回目の購入か を算出。
purchase_user_list AS (
SELECT
    user_id,
    DATE(purchased_at, "Asia/Tokyo") AS purchased_date,
    RANK() OVER(PARTITION BY user_id ORDER BY purchased_at) AS purchase_times
FROM
    purchases
WHERE
    DATE(user_created_at, "Asia/Tokyo") BETWEEN d1 AND d2
    AND
    DATE(purchased_at, "Asia/Tokyo") BETWEEN d1 AND d2
),

--purchase_user_listを元に、購入日別の購入数とリピート購入数を算出。
p_count AS (
SELECT
    purchased_date,
    COUNT(user_id) AS n_of_purchase,
    SUM(CASE WHEN purchase_times >= 2 THEN 1 ELSE 0 END) AS n_of_repeat_purchase
FROM
    purchase_user_list
GROUP BY
    purchased_date
)

--p_countテーブルを元に、購入日別の購入数, リピート購入数, リピート購入率を算出。
SELECT
    *,
    CASE 
        WHEN n_of_purchase = 0 OR n_of_purchase IS NULL THEN 0  #0除算エラーやNULL値出力を防ぐために、率の分母が0もしくはNULLの場合は0と出力。
        ELSE n_of_repeat_purchase / n_of_purchase 
    END AS repeat_purchase_rate
FROM
    p_count
ORDER BY
    purchase_date

SELECT
    DATE(purchased_at) AS purchased_date,
    SUM(CASE WHEN product_name = "干し柿" THEN 1 ELSE 0 END) AS n_of_hosigaki_purchases
FROM
    purchases
WHERE
--    DATE(user_created_at, "Asia/Tokyo") BETWEEN d1 AND d2
--    AND
    DATE(purchased_at, "Asia/Tokyo") BETWEEN d1 AND d2
GROUP BY
    purchased_date
ORDER BY
    purchased_date