WITH DATE_TBL AS (
    SELECT
        '2022-08-01' AS cldate,
        STRFTIME('%w', '2022-08-01') AS wkno,
        SUBSTR('日月火水木金土', STRFTIME('%w', '2022-08-01') + 1, 1) AS week
    UNION ALL
    SELECT
        DATE(cldate, '+1 days'),
        STRFTIME('%w', DATE(cldate, '+1 days')),
        SUBSTR('日月火水木金土', STRFTIME('%w', DATE(cldate, '+1 days')) + 1, 1)
    FROM
        DATE_TBL
    WHERE
        cldate < '2022-08-31'
)
SELECT
    cldate AS resist_date,
    week AS wk,
    COUNT(cldate) AS total
FROM
    DATE_TBL
-- DATE_TBLの全レコードとマッチするUSERSのレコードを取得
LEFT OUTER JOIN
    USERS
ON
    DATE(USERS.CONFIRMED_AT) = DATE_TBL.cldate
AND
    USERS.VALID_FLG = '1'
GROUP BY
    cldate
ORDER BY
    resist_date DESC;