WITH DATA AS (
SELECT SALES_AMT, SALES_DATE
FROM SALES
WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
AND SALES_TYPE <> 2
AND UPDATED_NO IS NULL),
DAYS AS (
SELECT '日' AS day
  UNION ALL SELECT '月'
  UNION ALL SELECT '火'
  UNION ALL SELECT '水'
  UNION ALL SELECT '木'
  UNION ALL SELECT '金'
  UNION ALL SELECT '土'
)
SELECT DAYS.DAY AS WEEK, COALESCE(B.AVG_CNT, 0) AS AVG_CNT, COALESCE(B.AVG_AMT, 0) || '円' AS AVG_AMT
FROM 
DAYS
LEFT JOIN
(SELECT
CASE strftime('%w', SALES_DATE)
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
END as WEEK,
COUNT(*) AS AVG_CNT, SUM(SALES_AMT) / (CASE COUNT(*) WHEN 0 THEN 1 ELSE COUNT(*) END) AS AVG_AMT 
FROM DATA
GROUP BY WEEK) AS B
ON DAYS.DAY = B.WEEK