--日付テーブルを作る
with day1 as(
select '2022-08-01' as day
union all
select '2022-08-02'
union all
select '2022-08-03'
union all
select '2022-08-04'
union all
select '2022-08-05'
union all
select '2022-08-06'
union all
select '2022-08-07'
union all
select '2022-08-08'
union all
select '2022-08-09'
union all
select '2022-08-10'
union all
select '2022-08-11'
union all
select '2022-08-12'
union all
select '2022-08-13'
union all
select '2022-08-14'
union all
select '2022-08-15'
union all
select '2022-08-16'
union all
select '2022-08-17'
union all
select '2022-08-18'
union all
select '2022-08-19'
union all
select '2022-08-20'
union all
select '2022-08-21'
union all
select '2022-08-22'
union all
select '2022-08-23'
union all
select '2022-08-24'
union all
select '2022-08-25'
union all
select '2022-08-26'
union all
select '2022-08-27'
union all
select '2022-08-28'
union all
select '2022-08-29'
union all
select '2022-08-30'
union all
select '2022-08-31'
), cal as (select 1 from USERS)

select 
day1.day as "REGIST_DATE",
case strftime('%w', day1.day)
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            when '6' then '土'
            else 'undefined'
        end as  "WK",
ifnull(TOTAL,0) as "TOTAL"
from day1
left join (
select 
strftime('%Y-%m-%d' ,CONFIRMED_AT) as "REGIST_DATE2",
count(*) as TOTAL
 from USERS
where CONFIRMED_AT between '2022-08-01' and '2022-08-31' and VALID_FLG='1'
group by strftime('%Y-%m-%d' ,CONFIRMED_AT)
order by REGIST_DATE2
) as temp on temp.REGIST_DATE2 = day1.day
order by day1.day