WITH subq AS (
    SELECT
        SALES_NO
        ,SALES_DATE
        ,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
        ,SALES_AMT
    FROM
        SALES
    WHERE
        SALES_DATE BETWEEN '2024-03-1' AND '2024-03-28'
        AND SALES_TYPE <>2
        AND UPDATED_NO IS NULL
    ORDER BY
        SALES_DATE ASC
    )
    
SELECT
    week AS WEEK
    ,ROUND(COUNT(SALES_AMT)) AS AVG_CNT
    , printf('%,d',ROUND(AVG(SALES_AMT))) || '円' AS AVG_AMT
FROM
    subq
GROUP BY
    week
ORDER BY
    WEEK ASC;