with cte(WEEK) as (VALUES('日'),('月'),('火'),('水'),('木'),('金'),('土'))
SELECT
  ifnull(sss.WEEK,w.WEEK) AS WEEK,
ifnull(sss.AVG_CNT,0) AS AVG_CNT,
  CASE
    WHEN length(ifnull(sss.AVG_AMT,0)) < 6 THEN ifnull(sss.AVG_AMT,0)
    WHEN length(ifnull(sss.AVG_AMT,0)) = 6 THEN substr(ifnull(sss.AVG_AMT,0),1,1) || ',' || substr(ifnull(sss.AVG_AMT,0),2,3)
    WHEN length(ifnull(sss.AVG_AMT,0)) = 7 THEN substr(ifnull(sss.AVG_AMT,0),1,2) || ',' || substr(ifnull(sss.AVG_AMT,0),3,3)
    WHEN length(ifnull(sss.AVG_AMT,0)) = 8 THEN substr(ifnull(sss.AVG_AMT,0),1,3) || ',' || substr(ifnull(sss.AVG_AMT,0),4,3)
    WHEN length(ifnull(sss.AVG_AMT,0)) = 9 THEN substr(ifnull(sss.AVG_AMT,0),1,1) || ',' || substr(ifnull(sss.AVG_AMT,0),2,3) || ',' || substr(ifnull(sss.AVG_AMT,0),5,3)
    WHEN length(ifnull(sss.AVG_AMT,0)) = 10 THEN substr(ifnull(sss.AVG_AMT,0),1,2) || ',' || substr(ifnull(sss.AVG_AMT,0),3,3) || ',' || substr(ifnull(sss.AVG_AMT,0),6,3)
    WHEN length(ifnull(sss.AVG_AMT,0)) = 11 THEN substr(ifnull(sss.AVG_AMT,0),1,3) || ',' || substr(ifnull(sss.AVG_AMT,0),4,3) || ',' || substr(ifnull(sss.AVG_AMT,0),7,3)
    ELSE ifnull(sss.AVG_AMT,0)
  END || '円' AS AVG_AMT
FROM (
SELECT
  ss.WEEK AS WEEK,
  ROUND(SUM(ss.CNT) /4.0) AS AVG_CNT,
  ROUND(SUM(ss.AMT) /4.0) AS AVG_AMT
FROM (
    SELECT s.WEEK, s.SALES_DATE, SUM(s.SALES_AMT) AS AMT, COUNT(1) AS CNT
    FROM (
        SELECT *, 
        CASE
            WHEN SALES_DATE = '2024-03-01' THEN '金'
            WHEN SALES_DATE = '2024-03-02' THEN '土'
            WHEN SALES_DATE = '2024-03-03' THEN '日'
            WHEN SALES_DATE = '2024-03-04' THEN '月'
            WHEN SALES_DATE = '2024-03-05' THEN '火'
            WHEN SALES_DATE = '2024-03-06' THEN '水'
            WHEN SALES_DATE = '2024-03-07' THEN '木'
            WHEN SALES_DATE = '2024-03-08' THEN '金'
            WHEN SALES_DATE = '2024-03-09' THEN '土'
            WHEN SALES_DATE = '2024-03-10' THEN '日'
            WHEN SALES_DATE = '2024-03-11' THEN '月'
            WHEN SALES_DATE = '2024-03-12' THEN '火'
            WHEN SALES_DATE = '2024-03-13' THEN '水'
            WHEN SALES_DATE = '2024-03-14' THEN '木'
            WHEN SALES_DATE = '2024-03-15' THEN '金'
            WHEN SALES_DATE = '2024-03-16' THEN '土'
            WHEN SALES_DATE = '2024-03-17' THEN '日'
            WHEN SALES_DATE = '2024-03-18' THEN '月'
            WHEN SALES_DATE = '2024-03-19' THEN '火'
            WHEN SALES_DATE = '2024-03-20' THEN '水'
            WHEN SALES_DATE = '2024-03-21' THEN '木'
            WHEN SALES_DATE = '2024-03-22' THEN '金'
            WHEN SALES_DATE = '2024-03-23' THEN '土'
            WHEN SALES_DATE = '2024-03-24' THEN '日'
            WHEN SALES_DATE = '2024-03-25' THEN '月'
            WHEN SALES_DATE = '2024-03-26' THEN '火'
            WHEN SALES_DATE = '2024-03-27' THEN '水'
            WHEN SALES_DATE = '2024-03-28' THEN '木'
        END
         AS WEEK
        FROM SALES
        WHERE SALES_TYPE <> 2 AND UPDATED_NO IS NULL
    ) s 
    GROUP BY s.SALES_DATE, s.WEEK
) ss
GROUP BY WEEK
) AS sss
FULL OUTER JOIN cte AS w ON sss.WEEK = w.WEEK
ORDER BY 
  CASE
    WHEN sss.WEEK = '日' THEN 1
    WHEN sss.WEEK = '月' THEN 2
    WHEN sss.WEEK = '火' THEN 3
    WHEN sss.WEEK = '水' THEN 4
    WHEN sss.WEEK = '木' THEN 5
    WHEN sss.WEEK = '金' THEN 6
    WHEN sss.WEEK = '土' THEN 7
  END