-- 売上データの件数と金額合計を曜日別に集計
WITH 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)
),
-- 曜日テーブルを生成
WeekDays AS (
  SELECT '0' AS WeekDay UNION ALL
  SELECT '1' UNION ALL
  SELECT '2' UNION ALL
  SELECT '3' UNION ALL
  SELECT '4' UNION ALL
  SELECT '5' UNION ALL
  SELECT '6'
)
-- 最終的な集計と表示
SELECT
  CASE WeekDays.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,
  COALESCE(ROUND(AVG(SalesData.Cnt)), 0) AS AVG_CNT,
  CASE 
    -- 3桁以下
    WHEN LENGTH(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'') <= 3 THEN CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'円'
    -- 4桁から6桁
    WHEN LENGTH(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'') <= 6 THEN SUBSTR(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'', 1, LENGTH(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'') - 3) || ',' || SUBSTR(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'', -3) || '円'
    -- 7桁以上
    WHEN LENGTH(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'') > 6 THEN SUBSTR(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'', 1, LENGTH(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'') - 6) || ',' || SUBSTR(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'', -6, 3) || ',' || SUBSTR(CAST(COALESCE(AVG(SalesData.TotalAmt), 0) AS INT)||'', -3) || '円'
  END AS AVG_AMT
FROM WeekDays
LEFT JOIN SalesData ON WeekDays.WeekDay = SalesData.WeekDay
GROUP BY WeekDays.WeekDay
ORDER BY WeekDays.WeekDay;