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 DATE(CONFIRMED_AT) BETWEEN DATE('2022-08-01') AND DATE('2022-08-31')
    GROUP BY
        1
)
SELECT
    dates.ref_dates AS REGIST_DATE
    , dates.day_of_week AS WK
    , COALESCE(confirmed_cnt.total, 0) AS TOTAL
FROM
    dates
    LEFT OUTER JOIN
        confirmed_cnt
        ON dates.ref_dates = confirmed_cnt.ref_dates