WITH
  SALES_SUM AS (
    SELECT
      strftime ('%w', SALES_DATE) AS WEEK_NO,
      COUNT(SALES_NO) AS SUM_DATE,
      SUM(SALES_AMT) AS SUM_AMT
    FROM
      SALES
    WHERE
      SALES_TYPE != 2
      AND UPDATED_NO IS NULL
      AND SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
    GROUP BY
      SALES_DATE
  ),
  WEEK_TBL AS (
    -- 再帰クエリで1週間分のデータを作成する（日付は何日でも構わない）
    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
      -- WITH句のテーブル名を指定して、上記のSELECT文で作成した項目を使用可能とする
      WEEK_TBL
    WHERE
      -- 7日分のデータを対象にする
      CLDATE < '2024-03-07'
  )
SELECT
  CASE WEEK_TBL.WEEK_NO
    WHEN "0" THEN "日"
    WHEN "1" THEN "月"
    WHEN "2" THEN "火"
    WHEN "3" THEN "水"
    WHEN "4" THEN "木"
    WHEN "5" THEN "金"
    WHEN "6" THEN "土"
    WHEN "7" THEN "日"
    ELSE "不明"
  END AS WEEK,
  IFNULL (ROUND(CAST(SUM(SUM_DATE) AS REAL) / 4), 0) AS AVG_CNT,
  IFNULL (printf ("%,d", CAST(ROUND(CAST(SUM(SUM_AMT) AS REAL) / 4) AS INT)), 0) || "円" AS AVG_AMT
FROM
  WEEK_TBL
  LEFT OUTER JOIN SALES_SUM ON SALES_SUM.WEEK_NO = WEEK_TBL.WEEK_NO
GROUP BY
  WEEK_TBL.WEEK_NO
ORDER BY
  WEEK_TBL.WEEK_NO;