WITH CAL AS (
      SELECT date('2022-08-01') d
UNION SELECT date('2022-08-02') d
UNION SELECT date('2022-08-03') d
UNION SELECT date('2022-08-04') d
UNION SELECT date('2022-08-05') d
UNION SELECT date('2022-08-06') d
UNION SELECT date('2022-08-07') d
UNION SELECT date('2022-08-08') d
UNION SELECT date('2022-08-09') d
UNION SELECT date('2022-08-10') d
UNION SELECT date('2022-08-11') d
UNION SELECT date('2022-08-12') d
UNION SELECT date('2022-08-13') d
UNION SELECT date('2022-08-14') d
UNION SELECT date('2022-08-15') d
UNION SELECT date('2022-08-16') d
UNION SELECT date('2022-08-17') d
UNION SELECT date('2022-08-18') d
UNION SELECT date('2022-08-19') d
UNION SELECT date('2022-08-20') d
UNION SELECT date('2022-08-21') d
UNION SELECT date('2022-08-22') d
UNION SELECT date('2022-08-23') d
UNION SELECT date('2022-08-24') d
UNION SELECT date('2022-08-25') d
UNION SELECT date('2022-08-26') d
UNION SELECT date('2022-08-27') d
UNION SELECT date('2022-08-28') d
UNION SELECT date('2022-08-29') d
UNION SELECT date('2022-08-30') d
UNION SELECT date('2022-08-31') d
)

SELECT
  d AS REGIST_DATE
 ,substr('日月火水木金土', strftime("%w", d)+1, 1) WK
 ,ifnull(U.CNT, 0) TOTAL
FROM CAL
LEFT JOIN (
  SELECT
    date(CONFIRMED_AT) CONFIRMED_AT, COUNT(*) CNT
  FROM USERS
  WHERE VALID_FLG = '1'
  GROUP BY date(CONFIRMED_AT)
) U ON CAL.d = U.CONFIRMED_AT