WITH tmp AS (
    SELECT
        SALES_NO
        , SALES_AMT
        , CASE 
            WHEN STRFTIME('%w', SALES_DATE) = '0' THEN '日'
            WHEN STRFTIME('%w', SALES_DATE) = '1' THEN '月'
            WHEN STRFTIME('%w', SALES_DATE) = '2' THEN '火'
            WHEN STRFTIME('%w', SALES_DATE) = '3' THEN '水'
            WHEN STRFTIME('%w', SALES_DATE) = '4' THEN '木'
            WHEN STRFTIME('%w', SALES_DATE) = '5' THEN '金'
            WHEN STRFTIME('%w', SALES_DATE) = '6' THEN '土'
            END
            AS WEEK
    FROM
        SALES
    WHERE
        SALES.SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
        AND
        SALES_TYPE != 2   -- 2 (売上返品)のデータ以外
        AND
        UPDATED_NO IS NULL
)
, WEEK_ORDER AS (
    SELECT '日' AS WEEK
    UNION ALL
    SELECT '月' AS WEEK
    UNION ALL
    SELECT '火' AS WEEK
    UNION ALL
    SELECT '水' AS WEEK
    UNION ALL
    SELECT '木' AS WEEK
    UNION ALL
    SELECT '金' AS WEEK
    UNION ALL
    SELECT '土' AS WEEK
)
, tmp2 AS (
    SELECT
        WEEK_ORDER.WEEK
        , CASE
            WHEN COUNT(tmp.SALES_NO) IS NULL THEN 0
            ELSE ROUND(COUNT(tmp.SALES_NO) / 4.0)
            END
            AS AVG_CNT
        , CAST(ROUND(IFNULL(SUM(tmp.SALES_AMT) / 4.0, 0)) AS INTEGER)
            AS AVG_AMT
    FROM
        WEEK_ORDER
    LEFT JOIN
        tmp ON WEEK_ORDER.WEEK = tmp.WEEK
    GROUP BY
        tmp.WEEK
    ORDER BY
        CASE
            WHEN tmp.WEEK = '日' THEN 0
            WHEN tmp.WEEK = '月' THEN 1
            WHEN tmp.WEEK = '火' THEN 2
            WHEN tmp.WEEK = '水' THEN 3
            WHEN tmp.WEEK = '木' THEN 4
            WHEN tmp.WEEK = '金' THEN 5
            WHEN tmp.WEEK = '土' THEN 6
        END
)
SELECT
    WEEK
    , AVG_CNT
    , CASE
        WHEN LENGTH(AVG_AMT) > 6
        THEN
            SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT) - 6) || ',' ||
            SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 5, 3) || ',' ||
            SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 2, 3) || '円'
        WHEN LENGTH(AVG_AMT) > 3
        THEN
            SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT) - 3) || ',' ||
            SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 2, 3) || '円'
        ELSE
        AVG_AMT || '円'
        END AS AVG_AMT
FROM
    tmp2
;