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",(date(datetime(u.CONFIRMED_AT))))
    when '0' then 'Sunday'
    when '1' then 'Monday'
    when '2' then 'Tuesday'
    when '3' then 'Wednesday'
    when '4' then 'Thursday'
    when '5' then 'Friday'
    when '6' then 'Saturday'
    else 'undefined'
end as WK,
COUNT(*) TOTAL
FROM USERS u
JOIN DateTable d on d.MyDate = date(datetime(u.CONFIRMED_AT))


WHERE u.VALID_FLG = '1'
GROUP BY d.MyDate
ORDER BY 1