SELECT CONFIRMED_AT AS REGIST_DATE, 
CASE WHEN strftime('%w', CONFIRMED_AT) = "0" THEN "日"
 WHEN strftime('%w', CONFIRMED_AT) = "1" THEN "月"
 WHEN strftime('%w', CONFIRMED_AT) = "2" THEN "火"
 WHEN strftime('%w', CONFIRMED_AT) = "3" THEN "水"
 WHEN strftime('%w', CONFIRMED_AT) = "4" THEN "木"
 WHEN strftime('%w', CONFIRMED_AT) = "5" THEN "金"
 WHEN strftime('%w', CONFIRMED_AT) = "6" THEN "土"
ELSE "日"
END AS WK,
SUM(VALID_FLG) AS TOTAL FROM (
SELECT SUBSTRING(CONFIRMED_AT, 0, 11) AS CONFIRMED_AT, VALID_FLG FROM USERS WHERE VALID_FLG = 1 AND "2022-08-01" <= CONFIRMED_AT AND CONFIRMED_AT < "2022-09-01"
UNION ALL
SELECT "2022-08-31" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-30" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-29" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-28" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-27" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-26" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-25" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-24" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-23" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-22" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-21" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-20" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-19" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-18" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-17" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-16" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-15" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-14" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-13" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-12" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-11" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-10" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-09" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-08" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-07" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-06" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-05" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-04" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-03" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-02" AS CONFIRMED_AT, 0 AS VALID_FLG
UNION ALL
SELECT "2022-08-01" AS CONFIRMED_AT, 0 AS VALID_FLG
) GROUP BY CONFIRMED_AT
ORDER BY REGIST_DATE