WITH RECURSIVE
GENERATE_SERIES(X) AS (
 SELECT 0 UNION ALL SELECT X+1 FROM GENERATE_SERIES LIMIT 7
)
SELECT 
CASE E.WEEKNO
WHEN 0 THEN "日"
WHEN 1 THEN "月"
WHEN 2 THEN "火"
WHEN 3 THEN "水"
WHEN 4 THEN "木"
WHEN 5 THEN "金"
WHEN 6 THEN "土" END AS WEEK,
E.AVG_CNT AS AVG_CNT,
CASE
WHEN LENGTH(E.AVG_AMT) > 6
THEN SUBSTR(E.AVG_AMT,1,LENGTH(E.AVG_AMT)-6) || ',' || SUBSTR(E.AVG_AMT,LENGTH(E.AVG_AMT)-5,3) || ',' || SUBSTR(E.AVG_AMT,length(AVG_AMT)-2,3)
WHEN LENGTH(E.AVG_AMT) > 3
THEN SUBSTR(E.AVG_AMT,1,LENGTH(E.AVG_AMT)-3) || ',' || SUBSTR(E.AVG_AMT,length(AVG_AMT)-2,3)
ELSE E.AVG_AMT END || '円' AS AVG_AMT
FROM (
SELECT
X AS WEEKNO,
CAST(ROUND(IFNULL(S.CNT, 0)*1.0/4.0) AS INT) AS AVG_CNT,
CAST(ROUND(IFNULL(S.AMT, 0)*1.0/4.0) AS INT) AS AVG_AMT
FROM GENERATE_SERIES AS G
LEFT OUTER JOIN (
    SELECT STRFTIME("%w", SALES_DATE) AS WEEKNO, COUNT(*) AS CNT, SUM(SALES_AMT) AS AMT
    FROM SALES
    WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
        AND SALES_TYPE != 2
        AND UPDATED_NO IS NULL
    GROUP BY WEEKNO
    ORDER BY WEEKNO
) AS S
ON CAST(S.WEEKNO AS INT) = G.X
) AS E;