WITH RECURSIVE
  cnt(x) AS (VALUES(julianday(replace('2022/08/01', '/', '-') ))
  UNION ALL
   SELECT x+1 FROM cnt WHERE x < julianday(replace('2022/08/31', '/', '-') ))
SELECT 
    strftime('%Y-%m-%d', x) AS "REGIST_DATE" ,
    (CASE 
        WHEN strftime('%w', x) == "0" THEN "日"
        WHEN strftime('%w', x) == "1" THEN "月"
        WHEN strftime('%w', x) == "2" THEN "火"
        WHEN strftime('%w', x) == "3" THEN "水"
        WHEN strftime('%w', x) == "4" THEN "木"
        WHEN strftime('%w', x) == "5" THEN "金"
        WHEN strftime('%w', x) == "6" THEN "土"
        ELSE strftime('%w', x)
    END) AS "WK",
    CASE WHEN temp_users.count IS NULL THEN 0 ELSE temp_users.count END AS "TOTAL"
FROM cnt
LEFT OUTER JOIN (
    SELECT 
        strftime('%Y-%m-%d', CONFIRMED_AT) AS "CONFIRMED_AT",
        count(*) AS "count"
    FROM USERS
    WHERE VALID_FLG = 1
    AND strftime('%Y-%m-%d', CONFIRMED_AT) >= "2022-08-01" AND strftime('%Y-%m-%d', CONFIRMED_AT) <= "2022-08-31"
    GROUP BY strftime('%Y-%m-%d', CONFIRMED_AT)
) AS temp_users ON temp_users.CONFIRMED_AT = strftime('%Y-%m-%d', x)



-- SELECT 
--     strftime('%Y/%m/%d', x) AS "REGIST_DATE"
-- FROM cnt

    -- SELECT 
    --     -- strftime('%Y-%m-%d', CONFIRMED_AT) AS "REGIST_DATE",
    --     DATES.DATEEND AS "REGIST_DATE",
    --     (CASE 
    --         WHEN strftime('%w', DATES.CONFIRMED_AT) == 0 THEN "日"
    --         WHEN strftime('%w', DATES.CONFIRMED_AT) == 1 THEN "月"
    --         WHEN strftime('%w', DATES.CONFIRMED_AT) == 2 THEN "火"
    --         WHEN strftime('%w', DATES.CONFIRMED_AT) == 3 THEN "水"
    --         WHEN strftime('%w', DATES.CONFIRMED_AT) == 4 THEN "木"
    --         WHEN strftime('%w', DATES.CONFIRMED_AT) == 5 THEN "金"
    --         WHEN strftime('%w', DATES.CONFIRMED_AT) == 6 THEN "土"
    --     END) AS "WK"
    -- FROM DATES