WITH cal AS
(
SELECT
    DATE('2022-08-01') AS dt
UNION ALL
SELECT
    DATE(_tbl.dt, '+1 day') AS dt
FROM cal AS _tbl
WHERE
    _tbl.dt < '2022-08-31'
),
u AS (
SELECT
    DATE(CONFIRMED_AT) AS REGIST_DATE
    , COUNT(1) AS TOTAL
FROM
    USERS
WHERE
    VALID_FLG = '1'
GROUP BY
    1
)
SELECT
    dt AS REGIST_DATE
    , substr('日月火水木金土', strftime("%w", dt)+1, 1) AS WK
    , IFNULL(TOTAL, 0) AS TOTAL
FROM
    cal
    LEFT OUTER JOIN u ON cal.dt = u.REGIST_DATE
ORDER BY
    REGIST_DATE