CREATE TEMPORARY TABLE daily_data (
  date DATE
);

INSERT INTO daily_data (date) VALUES
  ('2022-08-01'),
  ('2022-08-02'),
  ('2022-08-03'),
  ('2022-08-04'),
  ('2022-08-05'),
  ('2022-08-06'),
  ('2022-08-07'),
  ('2022-08-08'),
  ('2022-08-09'),
  ('2022-08-10'),
  ('2022-08-11'),
  ('2022-08-12'),
  ('2022-08-13'),
  ('2022-08-14'),
  ('2022-08-15'),
  ('2022-08-16'),
  ('2022-08-17'),
  ('2022-08-18'),
  ('2022-08-19'),
  ('2022-08-20'),
  ('2022-08-21'),
  ('2022-08-22'),
  ('2022-08-23'),
  ('2022-08-24'),
  ('2022-08-25'),
  ('2022-08-26'),
  ('2022-08-27'),
  ('2022-08-28'),
  ('2022-08-29'),
  ('2022-08-30'),
  ('2022-08-31');
SELECT dd.date,
CASE 
    WHEN strftime('%w', dd.date) = "0" THEN "日"
    WHEN strftime('%w', dd.date) = "1" THEN "月"
    WHEN strftime('%w', dd.date) = "2" THEN "火"
    WHEN strftime('%w', dd.date) = "3" THEN "水"
    WHEN strftime('%w', dd.date) = "4" THEN "木"
    WHEN strftime('%w', dd.date) = "5" THEN "金"
    WHEN strftime('%w', dd.date) = "6" THEN "土"
END as WK,
count(REGIST_DATE) as TOTAL
FROM daily_data dd
LEFT OUTER JOIN (SELECT strftime('%Y-%m-%d', CONFIRMED_AT) as REGIST_DATE FROM USERS) ON REGIST_DATE =dd.date
GROUP BY dd.date