WITH S as (
    SELECT SALES_DATE, SALES_AMT
    FROM SALES
    WHERE SALES_DATE >= '2024-03-01' and SALES_DATE <= '2024-03-28'
      and SALES_TYPE <> '2'
      and UPDATED_NO is null
), S2 as (
    SELECT SALES_DATE, SUM(SALES_AMT) SALES_AMT, COUNT(*) CNT
    FROM S
    GROUP BY SALES_DATE
), S3 as (
    SELECT strftime('%w', SALES_DATE) WEEK, cast(round(SUM(SALES_AMT) / 4.0) as integer) SALES_AMT, round(SUM(CNT) / 4.0) CNT
    FROM S2
    GROUP BY strftime('%w', SALES_DATE)
), W as (
    SELECT '0' WEEK
    UNION SELECT '1' WEEK
    UNION SELECT '2' WEEK
    UNION SELECT '3' WEEK
    UNION SELECT '4' WEEK
    UNION SELECT '5' WEEK
    UNION SELECT '6' WEEK
)

SELECT 
  CASE
    WHEN W.WEEK = '0' THEN '日'
    WHEN W.WEEK = '1' THEN '月'
    WHEN W.WEEK = '2' THEN '火'
    WHEN W.WEEK = '3' THEN '水'
    WHEN W.WEEK = '4' THEN '木'
    WHEN W.WEEK = '5' THEN '金'
    WHEN W.WEEK = '6' THEN '土' END WEEK
, ifnull(S3.CNT, 0) as AVG_CNT
, case
  when S3.SALES_AMT is null then '0'
  when length(S3.SALES_AMT) > 6
     then substr(S3.SALES_AMT,1,length(S3.SALES_AMT)-6) || ',' || substr(S3.SALES_AMT,length(S3.SALES_AMT)-5,3) || ',' || substr(S3.SALES_AMT,length(S3.SALES_AMT)-2,3)
  when  length(S3.SALES_AMT) > 3
     then substr(S3.SALES_AMT,1,length(S3.SALES_AMT)-3) || ',' || substr(S3.SALES_AMT,length(S3.SALES_AMT)-2,3)
  else  S3.SALES_AMT
 end || '円' AS AVG_AMT

FROM W 
LEFT JOIN S3 ON W.WEEK = S3.WEEK
ORDER BY W.WEEK