with datearray as (
select date("2022-08-01") as ymd
union all select date("2022-08-02") as ymd
union all select date("2022-08-03") as ymd
union all select date("2022-08-04") as ymd
union all select date("2022-08-05") as ymd
union all select date("2022-08-06") as ymd
union all select date("2022-08-07") as ymd
union all select date("2022-08-08") as ymd
union all select date("2022-08-09") as ymd
union all select date("2022-08-10") as ymd
union all select date("2022-08-11") as ymd
union all select date("2022-08-12") as ymd
union all select date("2022-08-13") as ymd
union all select date("2022-08-14") as ymd
union all select date("2022-08-15") as ymd
union all select date("2022-08-16") as ymd
union all select date("2022-08-17") as ymd
union all select date("2022-08-18") as ymd
union all select date("2022-08-19") as ymd
union all select date("2022-08-20") as ymd
union all select date("2022-08-21") as ymd
union all select date("2022-08-22") as ymd
union all select date("2022-08-23") as ymd
union all select date("2022-08-24") as ymd
union all select date("2022-08-25") as ymd
union all select date("2022-08-26") as ymd
union all select date("2022-08-27") as ymd
union all select date("2022-08-28") as ymd
union all select date("2022-08-29") as ymd
union all select date("2022-08-30") as ymd
union all select date("2022-08-31") as ymd
)
select
    ymd as REGIST_DATE
    , case strftime("%w",ymd)
        when "0" then "日"
        when "1" then "月"
        when "2" then "火"
        when "3" then "水"
        when "4" then "木"
        when "5" then "金"
        when "6" then "土"
        end as WK
    , ifnull(sum(valid_flg),0) as TOTAL
from
    datearray
    left join users on date(users.confirmed_at) = ymd
group by 1,2
order by 1
;