WITH DATE_TBL AS (
    SELECT
        DATE('2022-08-01') AS date
    UNION ALL
    SELECT
        DATE(date, '+ 1 day')
    FROM
        DATE_TBL
    WHERE
        date < DATE('2022-08-31')
)

SELECT
    DATE(CONFIRMED_AT) AS REGIST_DATE
    ,SUBSTR('日月火水木金土', strftime('%w', DATE(CONFIRMED_AT)) + 1, 1) AS WK
    ,CASE WHEN COUNT(DATE(CONFIRMED_AT)) <> 0
          THEN COUNT(DATE(CONFIRMED_AT))
          ELSE 0
          END AS TOTAL
FROM
    USERS
INNER JOIN
    DATE_TBL
  ON
    USERS.CONFIRMED_AT = DATE_TBL.date
WHERE
    CONFIRMED_AT BETWEEN '2022-08-01' AND '2022-08-31'
    AND VALID_FLG = '1'
GROUP BY
    DATE(CONFIRMED_AT)
ORDER BY
    REGIST_DATE ASC;