WITH tmp AS
(
    SELECT
        CASE cast(strftime('%w', SALES_DATE) AS integer)
            WHEN 0 then '日'
            WHEN 1 then '月'
            WHEN 2 then '火'
            WHEN 3 then '水'
            WHEN 4 then '木'
            WHEN 5 then '金'
        ELSE '土' END AS WEEK
        , SALES_DATE
        , COUNT(*) AS cnt
        , SUM(SALES_AMT) AS sales_amt
    FROM SALES
    WHERE SALES_TYPE <> 2 AND UPDATED_NO IS NOT NULL
    GROUP BY 1, 2
)
SELECT WEEK, ROUND(AVG(cnt)) AS AVG_CNT, printf("%,d円", ROUND(AVG(sales_amt))) AS AVG_AMT
FROM tmp
WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
GROUP BY WEEK
ORDER BY strftime('%w', SALES_DATE)
;