CREATE TEMPORARY TABLE tmp_nohibrid as 
select 
date(CONFIRMED_AT) AS REGIST_DATE
,COUNT(*) AS TOTAL
FROM USERS
WHERE VALID_FLG='1'
and date(CONFIRMED_AT) BETWEEN '2022-08-01' AND '2022-08-31'
GROUP BY date(CONFIRMED_AT);

INSERT INTO tmp_nohibrid VALUES('2022-08-01',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-02',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-03',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-04',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-05',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-06',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-07',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-08',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-09',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-10',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-11',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-12',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-13',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-14',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-15',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-16',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-17',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-18',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-19',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-20',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-21',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-22',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-23',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-24',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-25',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-26',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-27',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-28',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-29',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-30',0);
INSERT INTO tmp_nohibrid VALUES('2022-08-31',0);


SELECT 
REGIST_DATE
,WK
,SUM(TOTAL) AS TOTAL
FROM 
(
select 
REGIST_DATE
    ,CASE CAST(strftime('%w', REGIST_DATE) AS INT)
    WHEN 0 THEN '日'
    WHEN 1 THEN '月'
    WHEN 2 THEN '火'
    WHEN 3 THEN '水'
    WHEN 4 THEN '木'
    WHEN 5 THEN '金'
    WHEN 6 THEN '土'
    END AS WK
,TOTAL AS TOTAL
FROM tmp_nohibrid
)a
GROUP BY 
REGIST_DATE
,WK
ORDER BY 
REGIST_DATE