with all_days 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-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

),
base as (
SELECT 
    strftime('%Y-%m-%d', CONFIRMED_AT) AS REGIST_DATE,
    -- strftime('%a', CONFIRMED_AT) AS WK,
    substr('日月火水木金土', strftime('%a', CONFIRMED_AT)+1, 1) AS WK,
    COUNT(*) AS TOTAL
FROM USERS
WHERE VALID_FLG = '1' AND strftime('%Y/%m', CONFIRMED_AT) = '2022/08'
GROUP BY REGIST_DATE, WK
ORDER BY REGIST_DATE ASC
)
select
    a.REGIST_DATE,
    -- a.WK,
    substr('日月火水木金土', strftime('%w', a.REGIST_DATE)+1, 1) AS WK,
    (case when b.TOTAL is null then 0 else b.TOTAL end) as TOTAL
from all_days a
left join base b on a.REGIST_DATE = b.REGIST_DATE
ORDER BY a.REGIST_DATE ASC;