with day(kensu,kingaku,SALES_DATE_d) as(
SELECT  COUNT(*) OVER ( PARTITION BY SALES_DATE) ,SALES_AMT,SALES_DATE
FROM SALES
WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
AND SALES_TYPE <> '2'
AND UPDATED_NO IS NULL
),
avgken (week,kingaku,kensu) as(
SELECT DISTINCT CASE strftime('%w',SALES_DATE_d) 
         WHEN '0' THEN '日'
         WHEN '1' THEN '月'
         WHEN '2' THEN '火'
         WHEN '3' THEN '水'
         WHEN '4' THEN '木'
         WHEN '5' THEN '金'
         WHEN '6' THEN '土'
        end,
         SUM(kingaku) OVER ( PARTITION BY strftime('%w',SALES_DATE_d)),
         AVG(kensu) OVER ( PARTITION BY strftime('%w',SALES_DATE_d))
FROM day
)

SELECT week,round(kensu) AVG_CNT,round(kingaku) AVG_AMT
FROM avgken
WHERE week