WITH AUGUST(HIDUKE,WD_NUM) AS (
    --WD_NUM=0:日曜日～
    SELECT 
        '2022-08-01', STRFTIME('%w','2022-08-01')
    UNION ALL
    SELECT 
        DATE(HIDUKE,'+1 day'), STRFTIME('%w',DATE(HIDUKE,'+1 day'))
    FROM AUGUST
    WHERE
        HIDUKE<'2022-08-31'
    )
    
SELECT
    HIDUKE AS REGIST_DATE,
    CASE
        WHEN WD_NUM='0' THEN '日'
        WHEN WD_NUM='1' THEN '月'
        WHEN WD_NUM='2' THEN '火'
        WHEN WD_NUM='3' THEN '水'
        WHEN WD_NUM='4' THEN '木'
        WHEN WD_NUM='5' THEN '金'
        WHEN WD_NUM='6' THEN '土'
    END AS WK,
    IFNULL(CNT,0) AS TOTAL
FROM (
    SELECT
        COUNT(USER_CODE) AS CNT,
        DATE(CONFIRMED_AT) AS DAY
    FROM
        USERS
    WHERE
        VALID_FLG='1'
        AND IFNULL(CONFIRMED_AT,'2999-01-01') BETWEEN '2022-08-01' AND '2022-08-31' 
    GROUP BY
        DAY
    ) AS SUB
    RIGHT OUTER JOIN AUGUST
        ON AUGUST.HIDUKE=SUB.DAY
ORDER BY
    REGIST_DATE
;