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
    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 a.calendar_date
ORDER BY
    REGIST_DATE ASC;