WITH TMP(ORD, WEEK, AVG_CNT, AVG_AMT_NF) AS(
    SELECT
        (DAY+4)%7,
        CASE 
            WHEN DAY%7=1 THEN '金'
            WHEN DAY%7=2 THEN '土'
            WHEN DAY%7=3 THEN '日'
            WHEN DAY%7=4 THEN '月'
            WHEN DAY%7=5 THEN '火'
            WHEN DAY%7=6 THEN '水'
            WHEN DAY%7=0 THEN '木'
        END AS WEEK,
        ROUND(AVG(CNT)),
        CAST(CAST(ROUND(AVG(SUM_AMT)) AS INT) AS CHAR)
    FROM (
        SELECT
            CAST(SUBSTR(SALES_DATE, 9, 2) AS INT) AS DAY,
            SUM(CASE WHEN SALES_TYPE<>2 AND UPDATED_NO IS NULL THEN 1 ELSE 0 END) AS CNT,
            SUM(CASE WHEN SALES_TYPE<>2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) AS SUM_AMT
        FROM SALES
        WHERE 
            SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
        GROUP BY SALES_DATE
        ) AS DAILY
    GROUP BY DAY%7
)

SELECT
    WEEK,
    AVG_CNT,
    LTRIM(coalesce(SUBSTR(AVG_AMT_NF,-6,-3),'') 
        || ',' 
        || coalesce(SUBSTR(AVG_AMT_NF,-3,-3),'')
        || ','
        || coalesce(SUBSTR(AVG_AMT_NF,LENGTH(AVG_AMT_NF)+1,-3),'')
        || '円', ',') AS AVG_AMT
FROM TMP
ORDER BY ORD ASC;