with dt as (
select '2022-08-01' as d 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' 
)
select d 
,case 
    when substring(d,9,2)%7 = 1 then '月'
    when substring(d,9,2)%7 = 2 then '火'
    when substring(d,9,2)%7 = 3 then '水'
    when substring(d,9,2)%7 = 4 then '木'
    when substring(d,9,2)%7 = 5 then '金'
    when substring(d,9,2)%7 = 6 then '土'
    when substring(d,9,2)%7 = 0 then '日'
end as date
,ifnull(c.c,0)

from dt
left outer join 
(
    select
    substring(confirmed_at,0,11) as t
    ,count(*) as c
    from users
    where valid_flg='1' 
    and confirmed_at is not null
    and confirmed_at >= '2022-08-01 00:00:00'
    and confirmed_at < '2022-09-01 00:00:00'
    group by t
) as c
on dt.d=c.t