WITH RECURSIVE DATES(date) AS(
    VALUES('2022-08-01')
    UNION ALL
    SELECT
        date(date, '+1 day')
    FROM
        DATES
    WHERE
        date < '2022-08-31'
)
, tmp AS(
SELECT
    *
    , CASE WHEN CONFIRMED_AT IS NOT NULL THEN 1 ELSE IFNULL(CONFIRMED_AT, 0) END AS count
FROM
    DATES
    LEFT OUTER JOIN
    USERS
    ON DATES.date = date(USERS.CONFIRMED_AT) AND VALID_FLG = '1'
)
SELECT
    STRFTIME('%Y-%m-%d',date) AS REGIST_DATE
    , CASE STRFTIME('%w',date)
        WHEN '1' THEN '月'
        WHEN '2' THEN '火'
        WHEN '3' THEN '水'
        WHEN '4' THEN '木'
        WHEN '5' THEN '金'
        WHEN '6' THEN '土'
        WHEN '0' THEN '日'
        ELSE null END
    AS WK
    ,sum(count) AS TOTAL
FROM
    tmp
GROUP BY
    REGIST_DATE
ORDER BY
    REGIST_DATE ASC
;