WITH 
recursive generate_week(x) AS (SELECT 0 UNION ALL SELECT x+1 FROM generate_week limit 7),
s(WEEK, AVG_CNT, AVG_AMT) AS (SELECT strftime('%w', SALES_DATE)+0, ROUND(count(*)/4.0), ROUND(SUM(SALES_AMT)/4.0)
FROM SALES WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
AND SALES_TYPE <> 2 AND UPDATED_NO IS NULL
GROUP BY strftime('%w', SALES_DATE))
SELECT CASE x WHEN 0 THEN '日' WHEN 1 THEN '月' WHEN 2 THEN '火' WHEN 3 THEN '水' WHEN 4 THEN '木' WHEN 5 THEN '金' ELSE '土' END AS WEEK,
IFNULL(AVG_CNT, 0) AS AVG_CNT,
printf('%,d円', IFNULL(AVG_AMT, 0)) AS AVG_AMT
FROM generate_week LEFT JOIN s ON x = WEEK;