WITH tmp AS(
SELECT *, substr(CONFIRMED_AT, 0, 11) AS day
FROM USERS
WHERE CONFIRMED_AT IS NOT NULL
AND   VALID_FLG = '1'
)
,
days AS (
    SELECT '2022-08-01' as d
    UNION ALL SELECT '2022-08-02' as d
    UNION ALL SELECT '2022-08-03' as d
    UNION ALL SELECT '2022-08-04' as d
    UNION ALL SELECT '2022-08-05' as d
    UNION ALL SELECT '2022-08-06' as d
    UNION ALL SELECT '2022-08-07' as d
    UNION ALL SELECT '2022-08-08' as d
    UNION ALL SELECT '2022-08-09' as d
    UNION ALL SELECT '2022-08-10' as d
    UNION ALL SELECT '2022-08-11' as d
    UNION ALL SELECT '2022-08-12' as d
    UNION ALL SELECT '2022-08-13' as d
    UNION ALL SELECT '2022-08-14' as d
    UNION ALL SELECT '2022-08-15' as d
    UNION ALL SELECT '2022-08-16' as d
    UNION ALL SELECT '2022-08-17' as d
    UNION ALL SELECT '2022-08-18' as d
    UNION ALL SELECT '2022-08-19' as d
    UNION ALL SELECT '2022-08-20' as d
    UNION ALL SELECT '2022-08-21' as d
    UNION ALL SELECT '2022-08-22' as d
    UNION ALL SELECT '2022-08-23' as d
    UNION ALL SELECT '2022-08-24' as d
    UNION ALL SELECT '2022-08-25' as d
    UNION ALL SELECT '2022-08-26' as d
    UNION ALL SELECT '2022-08-27' as d
    UNION ALL SELECT '2022-08-28' as d
    UNION ALL SELECT '2022-08-29' as d
    UNION ALL SELECT '2022-08-30' as d
    UNION ALL SELECT '2022-08-31' as d
)

SELECT
    d AS REGIST_DATE
    ,CASE strftime('%w', d)
        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(USER_CODE) AS TOTAL
FROM days
LEFT OUTER JOIN tmp ON days.d = tmp.day
GROUP BY d
ORDER BY d