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
)


SELECT
    WEEK_ORDER.WEEK
    , ROUND(COUNT(tmp.SALES_NO) / 4.0, -1) AS AVG_CNT
    , CASE
        WHEN tmp.SALES_AMT IS NULL THEN '0円'
        ELSE printf('%,d', ROUND(SUM(tmp.SALES_AMT) / 4.0, -1)) || '円'
        END
        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
;