WITH temp AS (
        SELECT 0 AS x
        UNION
        SELECT 1 AS x
        UNION
        SELECT 2 AS x
        UNION
        SELECT 3 AS x
        UNION
        SELECT 4 AS x
        UNION
        SELECT 5 AS x
        UNION
        SELECT 6 AS x
        UNION
        SELECT 7 AS x
        UNION
        SELECT 8 AS x
        UNION
        SELECT 9 AS x
), dates AS (
    SELECT
        DISTINCT
        DATE('2022-08-01', '+'|| CAST(a.x * 10 + b.x AS INT) || ' days') AS ref_dates
        , SUBSTR('日月火水木金土', strftime("%w", DATE('2022-08-01', '+'|| CAST(a.x * 10 + b.x AS INT) || ' days'), 'localtime')+1, 1) AS day_of_week
    FROM 
        temp a, temp b
    WHERE
        a.x * 10 + b.x <= 30
), confirmed_cnt AS (
    SELECT
        DATE(CONFIRMED_AT) AS ref_dates
        , COUNT(DISTINCT user_code) AS total
    FROM
        users
    WHERE
        valid_flg = '1'
        AND CONFIRMED_AT IS NOT NULL
    GROUP BY
        DATE(CONFIRMED_AT) 
) 
SELECT
    dates.ref_dates AS REGIST_DATE
    , dates.day_of_week AS WK
    , CASE
        WHEN confirmed_cnt.ref_dates IS NULL THEN 0
        ELSE confirmed_cnt.total
    END AS total
FROM
    dates
    LEFT OUTER JOIN
        confirmed_cnt
        ON dates.ref_dates = confirmed_cnt.ref_dates