WITH SALES_SUM AS(
 SELECT
  STRFTIME('%w', SALES_DATE) AS WEEK_NO,
  SUBSTR('日月火水木金土', STRFTIME('%w', SALES_DATE) + 1, 1) AS WEEK_NAME,
  ROUND(CAST(COUNT(SALES_NO) AS REAL) / 4) AS AVG_CNT,
  CAST(ROUND(CAST(SUM(SALES_AMT) AS REAL) / 4) AS INT) AS AVG_SA_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
  WEEK_NAME 
),
WEEK_TBL AS (
 SELECT
  '2024-03-01' AS CLDATE, 
  STRFTIME('%w', '2024-03-01') AS WEEK_NO,
  SUBSTR('日月火水木金土', STRFTIME('%w', '2024-03-01') + 1, 1) AS WEEK_NAME
 UNION ALL
 SELECT
  DATE (CLDATE, '+1 days'), 
  STRFTIME('%w', DATE (CLDATE, '+1 days')),
  SUBSTR('日月火水木金土',STRFTIME('%w', DATE (CLDATE, '+1 days')) + 1, 1)
 FROM
  WEEK_TBL
 WHERE
  CLDATE < '2024-03-07'
)

SELECT
 WEEK_TBL.WEEK_NAME AS WEEK,
 IFNULL(AVG_CNT,0) AS AVG_CNT,
 CASE 
  WHEN LENGTH(AVG_SA_AMT) > 6 
   THEN SUBSTR(AVG_SA_AMT, 1, LENGTH(AVG_SA_AMT) - 6) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 5, 3) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 2, 3) 
  WHEN LENGTH(AVG_SA_AMT) > 3 
   THEN SUBSTR(AVG_SA_AMT, 1, LENGTH(AVG_SA_AMT) - 3) || ',' || SUBSTR(AVG_SA_AMT, LENGTH(AVG_SA_AMT) - 2, 3)
  ELSE IFNULL(AVG_SA_AMT, '0') 
  END || '円' AS AVG_AMT 
FROM
 WEEK_TBL
 LEFT OUTER JOIN SALES_SUM
  ON SALES_SUM.WEEK_NO = WEEK_TBL.WEEK_NO

ORDER BY
 WEEK_TBL.WEEK_NO ASC;