CREATE TEMP TABLE d (REGIST_DATE DATE);

INSERT INTO d (REGIST_DATE)
values
(date('2022-08-01'))
,(date('2022-08-02'))
,(date('2022-08-03'))
,(date('2022-08-04'))
,(date('2022-08-05'))
,(date('2022-08-06'))
,(date('2022-08-07'))
,(date('2022-08-08'))
,(date('2022-08-09'))
,(date('2022-08-10'))
,(date('2022-08-11'))
,(date('2022-08-12'))
,(date('2022-08-13'))
,(date('2022-08-14'))
,(date('2022-08-15'))
,(date('2022-08-16'))
,(date('2022-08-17'))
,(date('2022-08-18'))
,(date('2022-08-19'))
,(date('2022-08-20'))
,(date('2022-08-21'))
,(date('2022-08-22'))
,(date('2022-08-23'))
,(date('2022-08-24'))
,(date('2022-08-25'))
,(date('2022-08-26'))
,(date('2022-08-27'))
,(date('2022-08-28'))
,(date('2022-08-29'))
,(date('2022-08-30'))
,(date('2022-08-31'))
;

with tmp as(
SELECT
    date(SUBSTR(CONFIRMED_AT ,1 ,10)) AS REGIST_DATE
    ,COUNT(1) AS TOTAL
FROM
    USERS
WHERE
    SUBSTR(CONFIRMED_AT ,1 ,7) = '2022-08'
    AND
    VALID_FLG=1
GROUP BY REGIST_DATE
)

select 
    d.REGIST_DATE
    ,CASE
     WHEN strftime('%w', d.REGIST_DATE) = '0' THEN '日'
     WHEN strftime('%w', d.REGIST_DATE) = '1' THEN '月'
     WHEN strftime('%w', d.REGIST_DATE) = '2' THEN '火'
     WHEN strftime('%w', d.REGIST_DATE) = '3' THEN '水'
     WHEN strftime('%w', d.REGIST_DATE) = '4' THEN '木'
     WHEN strftime('%w', d.REGIST_DATE) = '5' THEN '金'
     WHEN strftime('%w', d.REGIST_DATE) = '6' THEN '土'
     END AS WK
    ,CASE WHEN tmp.TOTAL is null then 0 else tmp.TOTAL end as TOTAL 
from d
left join tmp
on d.REGIST_DATE = tmp.REGIST_DATE