with
  a as (
    select
      *,
      date(CONFIRMED_AT) as adt
    from
      USERS
    where
      VALID_FLG = '1'
      and CONFIRMED_AT is not null
      and date(CONFIRMED_AT) between '2022-08-01' and '2022-09-00'
  ),
  b as (
    select
      '2022-08-' || substr('0' || seq, -2, 2) as dt
    from
      (
        select
          no as seq
        from
          (
            WITH
              SEQ AS (
                SELECT
                  1 as no
                UNION ALL
                SELECT
                  no + 1 as no
                FROM
                  SEQ
                WHERE
                  no + 1 <= 31
              )
            select
              *
            from
              SEQ
          ) as SEQ
      )
  )
select
  b.dt as REGIST_DATE,
  substr("日月火水木金土", strftime('%w', b.dt) + 1, 1) as WK,
  count(a.CONFIRMED_AT) as TOTAL
from
  b
  left join a on b.dt = a.adt
group by
  b.dt
order by b.dt