SELECT CASE SALES_AVG.WEEK_NO
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
    ELSE ''
 END AS WEEK
,SALES_AVG.AVG_CNT
,CASE
    WHEN LENGTH(SALES_AVG.AVG_AMT) > 6
    THEN SUBSTR(SALES_AVG.AVG_AMT,1,LENGTH(SALES_AVG.AVG_AMT)-6) || ',' || SUBSTR(SALES_AVG.AVG_AMT,LENGTH(SALES_AVG.AVG_AMT)-5,3) || ',' || SUBSTR(SALES_AVG.AVG_AMT,LENGTH(SALES_AVG.AVG_AMT)-2,3)
    WHEN LENGTH(SALES_AVG.AVG_AMT) > 3
    THEN SUBSTR(SALES_AVG.AVG_AMT,1,LENGTH(SALES_AVG.AVG_AMT)-3) || ',' || SUBSTR(SALES_AVG.AVG_AMT,LENGTH(SALES_AVG.AVG_AMT)-2,3)
    ELSE SALES_AVG.AVG_AMT
END || '円' AS AVG_AMT
FROM (
SELECT SALES_DATA.WEEK_NO
,COUNT(1) AS AVG_CNT
,ROUND(AVG(SALES_AMT), 0) AS AVG_AMT
FROM (
SELECT
STRFTIME('%w', SALES_DATE) AS WEEK_NO
,SALES_AMT
FROM SALES
WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
AND SALES_TYPE <> 2
AND UPDATED_NO IS NULL
) AS SALES_DATA
GROUP BY SALES_DATA.WEEK_NO
) AS SALES_AVG
UNION ALL
SELECT '0' AS WEEK_NO
, 0 AS AVG_CNT
, 0 AS AVG_AMT
ORDER BY SALES_AVG.WEEK_NO ASC
;