SELECT X.date1 AS REGIST_DATE
    ,CASE WHEN strftime("%w", X.date1, 'localtime') = '0' THEN '日'
          WHEN strftime("%w", X.date1, 'localtime') = '1' THEN '月'
          WHEN strftime("%w", X.date1, 'localtime') = '2' THEN '火'
          WHEN strftime("%w", X.date1, 'localtime') = '3' THEN '水'
          WHEN strftime("%w", X.date1, 'localtime') = '4' THEN '木'
          WHEN strftime("%w", X.date1, 'localtime') = '5' THEN '金'
          WHEN strftime("%w", X.date1, 'localtime') = '6' THEN '土'
          END AS WK
    ,case when Y.REGIST_DATE IS NULL THEN 0
        else Y.TOTAL END AS TOTAL
FROM (
    select date('2022-08-01', 'localtime') as date1
        UNION select date('2022-08-01', '+1 days') as date1
        UNION select date('2022-08-01', '+2 days') as date1
        UNION select date('2022-08-01', '+3 days') as date1
        UNION select date('2022-08-01', '+4 days') as date1
        UNION select date('2022-08-01', '+5 days') as date1
        UNION select date('2022-08-01', '+6 days') as date1
        UNION select date('2022-08-01', '+7 days') as date1
        UNION select date('2022-08-01', '+8 days') as date1
        UNION select date('2022-08-01', '+9 days') as date1
        UNION select date('2022-08-01', '+10 days') as date1
        UNION select date('2022-08-01', '+11 days') as date1
        UNION select date('2022-08-01', '+12 days') as date1
        UNION select date('2022-08-01', '+13 days') as date1
        UNION select date('2022-08-01', '+14 days') as date1
        UNION select date('2022-08-01', '+15 days') as date1
        UNION select date('2022-08-01', '+16 days') as date1
        UNION select date('2022-08-01', '+17 days') as date1
        UNION select date('2022-08-01', '+18 days') as date1
        UNION select date('2022-08-01', '+19 days') as date1
        UNION select date('2022-08-01', '+20 days') as date1
        UNION select date('2022-08-01', '+21 days') as date1
        UNION select date('2022-08-01', '+22 days') as date1
        UNION select date('2022-08-01', '+23 days') as date1
        UNION select date('2022-08-01', '+24 days') as date1
        UNION select date('2022-08-01', '+25 days') as date1
        UNION select date('2022-08-01', '+26 days') as date1
        UNION select date('2022-08-01', '+27 days') as date1
        UNION select date('2022-08-01', '+28 days') as date1
        UNION select date('2022-08-01', '+29 days') as date1
        UNION select date('2022-08-01', '+30 days') as date1
    ) X
    LEFT OUTER JOIN (
    SELECT
        date(CONFIRMED_AT, 'localtime') AS REGIST_DATE
        ,COUNT(*) AS TOTAL
    FROM USERS
    WHERE VALID_FLG = '1' AND CONFIRMED_AT IS NOT NULL
        AND REGIST_DATE >= '2022-08-01' AND REGIST_DATE <= '2022-08-31'
    GROUP BY REGIST_DATE
    ) Y
        ON X.date1 = Y.REGIST_DATE
ORDER BY X.date1