select
 REGIST_DATE,
 WK,
 TOTAL
from
(
select
 date(CONFIRMED_AT) AS REGIST_DATE,
  case strftime('%w', date(CONFIRMED_AT))
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
    ELSE NULL
    END AS WK,
 COUNT(*) AS TOTAL
 from 
  USERS
WHERE
 VALID_FLG='1'
 and 
 date(CONFIRMED_AT) BETWEEN date('2022-08-01') and date('2022-08-31')
GROUP By 
 date(CONFIRMED_AT)

union
select
 date(CONFIRMED_AT) AS REGIST_DATE,
  case strftime('%w', date(CONFIRMED_AT))
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
    ELSE NULL
    END AS WK,
 0 AS TOTAL
 from 
  USERS
WHERE
 VALID_FLG='0'
 and 
 date(CONFIRMED_AT) BETWEEN date('2022-08-01') and date('2022-08-31')
GROUP By 
 date(CONFIRMED_AT)
 )
 order by REGIST_DATE