WITH t1 AS (
SELECT '2022-08-01' AS REGIST_DATE
UNION ALL
SELECT '2022-08-02' AS REGIST_DATE
UNION ALL
SELECT '2022-08-03' AS REGIST_DATE
UNION ALL
SELECT '2022-08-03' AS REGIST_DATE
UNION ALL
SELECT '2022-08-04' AS REGIST_DATE
UNION ALL
SELECT '2022-08-05' AS REGIST_DATE
UNION ALL
SELECT '2022-08-06' AS REGIST_DATE
UNION ALL
SELECT '2022-08-07' AS REGIST_DATE
UNION ALL
SELECT '2022-08-08' AS REGIST_DATE
UNION ALL
SELECT '2022-08-09' AS REGIST_DATE
UNION ALL
SELECT '2022-08-10' AS REGIST_DATE
UNION ALL
SELECT '2022-08-11' AS REGIST_DATE
UNION ALL
SELECT '2022-08-12' AS REGIST_DATE
UNION ALL
SELECT '2022-08-13' AS REGIST_DATE
UNION ALL
SELECT '2022-08-14' AS REGIST_DATE
UNION ALL
SELECT '2022-08-15' AS REGIST_DATE
UNION ALL
SELECT '2022-08-16' AS REGIST_DATE
UNION ALL
SELECT '2022-08-17' AS REGIST_DATE
UNION ALL
SELECT '2022-08-18' AS REGIST_DATE
UNION ALL
SELECT '2022-08-19' AS REGIST_DATE
UNION ALL
SELECT '2022-08-20' AS REGIST_DATE
UNION ALL
SELECT '2022-08-21' AS REGIST_DATE
UNION ALL
SELECT '2022-08-22' AS REGIST_DATE
UNION ALL
SELECT '2022-08-23' AS REGIST_DATE
UNION ALL
SELECT '2022-08-24' AS REGIST_DATE
UNION ALL
SELECT '2022-08-25' AS REGIST_DATE
UNION ALL
SELECT '2022-08-26' AS REGIST_DATE
UNION ALL
SELECT '2022-08-27' AS REGIST_DATE
UNION ALL
SELECT '2022-08-28' AS REGIST_DATE
UNION ALL
SELECT '2022-08-29' AS REGIST_DATE
UNION ALL
SELECT '2022-08-30' AS REGIST_DATE
UNION ALL
SELECT '2022-08-31' AS REGIST_DATE
)

SELECT
    t1.REGIST_DATE
    , CASE
        WHEN strftime('%w', t1.REGIST_DATE) = '0' THEN '日' 
        WHEN strftime('%w', t1.REGIST_DATE) = '1' THEN '月' 
        WHEN strftime('%w', t1.REGIST_DATE) = '2' THEN '火' 
        WHEN strftime('%w', t1.REGIST_DATE) = '3' THEN '水' 
        WHEN strftime('%w', t1.REGIST_DATE) = '4' THEN '木' 
        WHEN strftime('%w', t1.REGIST_DATE) = '5' THEN '金' 
        WHEN strftime('%w', t1.REGIST_DATE) = '6' THEN '土' 
        END AS WK
    , COUNT(DISTINCT USER_CODE) AS TOTAL
    
FROM t1 LEFT OUTER JOIN USERS
    ON USERS.CONFIRMED_AT BETWEEN t1.REGIST_DATE || ' 00:00:00' AND t1.REGIST_DATE || ' 23:59:59'
    AND USERS.VALID_FLG = '1'
    AND USERS.CONFIRMED_AT BETWEEN '2022-08-01 00:00:00' AND '2022-08-31 23:59:59'
    
GROUP BY 1,2
ORDER BY 1 ASC