SELECT
   DTAB.DT AS REGIST_DATE
  ,CASE STRFTIME('%w', DTAB.DT)
       WHEN '0' then '日'
       WHEN '1' then '月'
       WHEN '2' then '火'
       WHEN '3' then '水'
       WHEN '4' then '木'
       WHEN '5' then '金'
       WHEN '6' then '土'
   END AS WK
  ,CASE WHEN UTAB.TOTAL IS NULL THEN 0 ELSE UTAB.TOTAL END AS TOTAL
FROM
(
  SELECT DT
  FROM (
  SELECT '2022-08-01' AS DT UNION
  SELECT '2022-08-02' AS DT UNION
  SELECT '2022-08-03' AS DT UNION
  SELECT '2022-08-04' AS DT UNION
  SELECT '2022-08-05' AS DT UNION
  SELECT '2022-08-06' AS DT UNION
  SELECT '2022-08-07' AS DT UNION
  SELECT '2022-08-08' AS DT UNION
  SELECT '2022-08-09' AS DT UNION
  SELECT '2022-08-10' AS DT UNION
  SELECT '2022-08-11' AS DT UNION
  SELECT '2022-08-12' AS DT UNION
  SELECT '2022-08-13' AS DT UNION
  SELECT '2022-08-14' AS DT UNION
  SELECT '2022-08-15' AS DT UNION
  SELECT '2022-08-16' AS DT UNION
  SELECT '2022-08-17' AS DT UNION
  SELECT '2022-08-18' AS DT UNION
  SELECT '2022-08-19' AS DT UNION
  SELECT '2022-08-20' AS DT UNION
  SELECT '2022-08-21' AS DT UNION
  SELECT '2022-08-22' AS DT UNION
  SELECT '2022-08-23' AS DT UNION
  SELECT '2022-08-24' AS DT UNION
  SELECT '2022-08-25' AS DT UNION
  SELECT '2022-08-26' AS DT UNION
  SELECT '2022-08-27' AS DT UNION
  SELECT '2022-08-28' AS DT UNION
  SELECT '2022-08-29' AS DT UNION
  SELECT '2022-08-30' AS DT UNION
  SELECT '2022-08-31' AS DT
  )
) DTAB
LEFT JOIN
 (
  SELECT
    SUBSTR(CONFIRMED_AT, 1, 10) AS REGIST_DATE
   ,COUNT(*) AS TOTAL
  FROM
    USERS
  WHERE VALID_FLG='1'
    AND SUBSTR(CONFIRMED_AT, 1, 10) >= '2022-08-01'
    AND SUBSTR(CONFIRMED_AT, 1, 10) < '2022-09-01'
  GROUP BY
    SUBSTR(CONFIRMED_AT, 1, 10)
 ) UTAB
ON DTAB.DT = UTAB.REGIST_DATE
ORDER BY 1