WITH DateRange AS (
  SELECT date('2024-03-01', '+' || (a + b * 7) || ' days') AS SaleDate
  FROM (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) a,
       (SELECT 0 b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) b
  WHERE date('2024-03-01', '+' || (a + b * 7) || ' days') <= '2024-03-28'
),
SalesData AS (
  SELECT
    strftime('%w', SALES_DATE) AS WeekDay,
    COUNT(*) AS Cnt,
    SUM(SALES_AMT) AS TotalAmt
  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
  CASE WeekDay
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
  END AS WEEK,
  IFNULL(ROUND(AVG(Cnt), 0), 0) AS AVG_CNT,
  IFNULL(ROUND(AVG(TotalAmt), 0), 0) AS AVG_AMT
FROM DateRange
LEFT JOIN SalesData ON WeekDay = strftime('%w', SaleDate)
WHERE WEEK IS NOT NULL
GROUP BY WeekDay
ORDER BY WeekDay;