WITH TMP AS(
SELECT
    DATE('2022-08-01') AS DATE
UNION ALL
SELECT
    DATE(DATE,'+1 DAY')
FROM
    TMP
WHERE
    DATE<DATE('2022-08-31')
)
SELECT
    TMP.DATE AS REGIST_DATE
    ,CASE STRFTIME('%w',TMP.DATE)
        WHEN '0' THEN '日'
        WHEN '1' THEN '月'
        WHEN '2' THEN '火'
        WHEN '3' THEN '水'
        WHEN '4' THEN '木'
        WHEN '5' THEN '金'
        WHEN '6' THEN '土'
    END AS WK
    ,COUNT(USERS.USER_CODE) AS TOTAL
FROM
    TMP
LEFT JOIN
    USERS 
ON
    STRFTIME('%Y-%m-%d',USERS.CONFIRMED_AT) = STRFTIME('%Y-%m-%d',TMP.DATE)
AND
    USERS.VALID_FLG = '1'
GROUP BY
    TMP.DATE
ORDER BY
    TMP.DATE ASC;