WITH SALES_SUM AS ( 
   -- 売上データを集計する
   SELECT
        -- STRFTIMEで、売上日の曜日番号を取得する
        STRFTIME('%w', SALES_DATE) AS WEEK_NO
        -- 曜日番号より、曜日の文字列より該当する曜日1文字を取得する 
        , SUBSTR('日月火水木金土', STRFTIME('%w', SALES_DATE) + 1, 1) AS WEEK_NAME
        -- 曜日毎のデータ件数を集計し、4週間の4で割る事により1日の平均件数を求める
        , ROUND(CAST(COUNT(SALES_NO) AS REAL) / 4) AS AVG_CNT
        -- 曜日毎の売上金額合計を集計し、4週間の4で割る事により1日の平均売上金額を求める
        , CAST(ROUND(CAST(SUM(SALES_AMT) AS REAL) / 4) AS INT) AS AVG_SA_AMT 
    FROM
        SALES 
    WHERE
        -- 4週間分の売上データを対象とする
        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 ( 
    -- 再帰クエリで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
    WEEK_TBL.WEEK_NAME AS WEEK
    , IFNULL(AVG_CNT, 0) AS AVG_CNT
    -- 桁数によりカンマを付加する場所を判定して、金額を3桁毎に分割してカンマを追加する
    , 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;