WITH WEEK AS ( 
  SELECT
    '0' AS NUM
    , '日' AS WEEK 
  UNION ALL 
  SELECT
    '1' AS NUM
    , '月' AS WEEK 
  UNION ALL 
  SELECT
    '2' AS NUM
    , '火' AS WEEK 
  UNION ALL 
  SELECT
    '3' AS NUM
    , '水' AS WEEK 
  UNION ALL 
  SELECT
    '4' AS NUM
    , '木' AS WEEK 
  UNION ALL 
  SELECT
    '5' AS NUM
    , '金' AS WEEK 
  UNION ALL 
  SELECT
    '6' AS NUM
    , '土' AS WEEK
) 
, SUM AS ( 
  SELECT
    STRFTIME('%w', SALES_DATE) AS NUM
    , ROUND(COUNT(*) / 4.0) AS CNT
    , ROUND(SUM(SALES_AMT) / 4.0) 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
    STRFTIME('%w', SALES_DATE)
) 
SELECT
  W.WEEK
  , IFNULL(CNT, 0) AS AVG_CNT
  , CASE 
    WHEN AMT IS NULL 
      THEN '0円' 
    WHEN LENGTH(AMT) > 8 
      THEN SUBSTR(AMT, 1, LENGTH(AMT) - 8) || ',' || SUBSTR(AMT, LENGTH(AMT) - 7, 3) || ',' || SUBSTR(AMT, LENGTH(AMT) - 4, 3) || '円' 
    WHEN LENGTH(AMT) > 5 
      THEN SUBSTR(AMT, 1, LENGTH(AMT) - 5) || ',' || SUBSTR(AMT, LENGTH(AMT) - 4, 3) || '円' 
    ELSE AMT 
    END AS AVG_AMT 
FROM
  WEEK AS W 
  LEFT OUTER JOIN SUM AS S 
    ON W.NUM = S.NUM 
ORDER BY
  W.NUM;