WITH RECURSIVE
  cnt(x) AS (VALUES(julianday('2022-08-01'))
  UNION ALL
   SELECT x+1 FROM cnt WHERE x < julianday('2022-08-31'))
,
date_list AS(
SELECT strftime('%Y-%m-%d', x) AS REGIST_DATE FROM cnt
),
user_count AS(
SELECT
    DATE(CONFIRMED_AT) AS REGIST_DATE,
    COUNT(DISTINCT USER_CODE) TOTAL
FROM USERS
WHERE
    VALID_FLG = "1"
    AND 
    CONFIRMED_AT IS NOT NULL
GROUP BY 1
)
SELECT
    REGIST_DATE,
    CASE CAST(strftime("%w",REGIST_DATE) AS INT64)
        WHEN 0 THEN "日"
        WHEN 1 THEN "月"
        WHEN 2 THEN "火"
        WHEN 3 THEN "水"
        WHEN 4 THEN "木"
        WHEN 5 THEN "金"
        WHEN 6 THEN "土"
        END AS WK,
    IFNULL(TOTAL,0) AS TOTAL
FROM date_list
LEFT JOIN user_count
USING(REGIST_DATE)
ORDER BY REGIST_DATE