WITH USERS_SUM AS ( 
    SELECT
        STRFTIME('%Y-%m-%d', CONFIRMED_AT) AS REGIST_DATE
        ,count(USER_CODE) AS TOTAL
    FROM
        USERS
    WHERE 
        VALID_FLG='1'
     AND CONFIRMED_AT BETWEEN '2022-08-01 00:00:00' AND '2022-08-31 23:59:59'
    GROUP BY
        REGIST_DATE
),
WEEK_TBL AS ( 
    SELECT
        '2022-08-01' AS REGIST_DATE
        , SUBSTR('日月火水木金土', STRFTIME('%w', '2022-08-01') + 1, 1) AS WK 
    UNION ALL 
    SELECT
        DATE (REGIST_DATE, '+1 days')
        , SUBSTR( 
            '日月火水木金土'
            , STRFTIME('%w', DATE (REGIST_DATE, '+1 days')) + 1
            , 1
        ) 
    FROM
        WEEK_TBL 
    WHERE
        REGIST_DATE < '2022-08-31'
) 
SELECT 
WEEK_TBL.REGIST_DATE,
WEEK_TBL.WK,
IFNULL(USERS_SUM.TOTAL,0) TOTAL
from WEEK_TBL left outer join USERS_SUM
on WEEK_TBL.REGIST_DATE = USERS_SUM.REGIST_DATE
order by WEEK_TBL.REGIST_DATE