WITH DateTable AS (
    SELECT '2022-08-01' AS MyDate
    UNION ALL
    SELECT DATE(MyDate, '+1 day')
    FROM DateTable
    WHERE MyDate < '2022-08-31'
)
SELECT 
    d.MyDate REGIST_DATE,
    CASE
        strftime("%w",d.MyDate)
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
        else 'undefined'
    end as WK,
    COUNT(u.CONFIRMED_AT) TOTAL
FROM DateTable d
    LEFT JOIN USERS u on d.MyDate = date(datetime(u.CONFIRMED_AT)) AND u.VALID_FLG = '1' 
-- WHERE u.VALID_FLG = '1' 
GROUP BY d.MyDate
ORDER BY 1