WITH RECURSIVE calendar(calendar_date) AS (
    SELECT
        DATE('2022-08-01') AS calendar_date
    UNION ALL
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        -- ここが終わりの日付
        calendar_date < DATE('2022-08-31')
)

SELECT
    date(a.calendar_date) AS REGIST_DATE
    , (
    case strftime('%w', a.calendar_date)
         when '0' then '日'
         when '1' then '月'
         when '2' then '火'
       when '3' then '水'
      when '4' then '木'
         when '5' then '金'
           when '6' then '土'
          else 'undefined'
       end 
    ) AS WK 
    , 
    (
    case VALID_FLG
         when '1' then COUNT(USER_CODE)
         when '0' then 0
         else 0
         end
    )
 AS TOTAL
FROM
calendar AS a
LEFT JOIN 
(
SELECT
    CONFIRMED_AT,
    date(CONFIRMED_AT) AS t_date,
    VALID_FLG,
    USER_CODE
FROM
    USERS 
WHERE
    -- VALID_FLG='1' aND 
    --CONFIRMED_AT IS NOT NULL aND
    CONFIRMED_AT > "2022-07-31 23:59:59" AND CONFIRMED_AT < "2022-09-01 00:00:00"
) AS b ON b.t_date = a.calendar_date



GROUP BY date(CONFIRMED_AT)
ORDER BY
    REGIST_DATE ASC;