WITH dates(d, wk) AS (
    VALUES
        ('2022-08-01', '月')
      , ('2022-08-02', '火')
      , ('2022-08-03', '水')
      , ('2022-08-04', '木')
      , ('2022-08-05', '金')
      , ('2022-08-06', '土')
      , ('2022-08-07', '日')
      , ('2022-08-08', '月')
      , ('2022-08-09', '火')
      , ('2022-08-10', '水')
      , ('2022-08-11', '木')
      , ('2022-08-12', '金')
      , ('2022-08-13', '土')
      , ('2022-08-14', '日')
      , ('2022-08-15', '月')
      , ('2022-08-16', '火')
      , ('2022-08-17', '水')
      , ('2022-08-18', '木')
      , ('2022-08-19', '金')
      , ('2022-08-20', '土')
      , ('2022-08-21', '日')
      , ('2022-08-22', '月')
      , ('2022-08-23', '火')
      , ('2022-08-24', '水')
      , ('2022-08-25', '木')
      , ('2022-08-26', '金')
      , ('2022-08-27', '土')
      , ('2022-08-28', '日')
      , ('2022-08-29', '月')
      , ('2022-08-30', '火')
      , ('2022-08-31', '水')
),
simple_sum AS (
    select
        substring(CONFIRMED_AT, 0, 11) AS REGIST_DATE
      , COUNT(*) as C
    from
        USERS
    WHERE
        '2022-08-01' <= CONFIRMED_AT AND CONFIRMED_AT < '2022-09-01'
    AND VALID_FLG='1'
    GROUP BY
        REGIST_DATE
)
SELECT
    dates.d                                    AS REGIST_DATE
  , dates.wk                                   AS WK
  , iif(simple_sum.c IS NULL, 0, simple_sum.c) AS TOTAL
FROM
                    dates 
    LEFT OUTER JOIN simple_sum
    on dates.d = simple_sum.REGIST_DATE
ORDER BY
    d