WITH dates(d, wk) AS (
  VALUES
      ('2024-03-01', '金')
    , ('2024-03-02', '土')
    , ('2024-03-03', '日')
    , ('2024-03-04', '月')
    , ('2024-03-05', '火')
    , ('2024-03-06', '水')
    , ('2024-03-07', '木')
    , ('2024-03-08', '金')
    , ('2024-03-09', '土')
    , ('2024-03-10', '日')
    , ('2024-03-11', '月')
    , ('2024-03-12', '火')
    , ('2024-03-13', '水')
    , ('2024-03-14', '木')
    , ('2024-03-15', '金')
    , ('2024-03-16', '土')
    , ('2024-03-17', '日')
    , ('2024-03-18', '月')
    , ('2024-03-19', '火')
    , ('2024-03-20', '水')
    , ('2024-03-21', '木')
    , ('2024-03-22', '金')
    , ('2024-03-23', '土')
    , ('2024-03-24', '日')
    , ('2024-03-25', '月')
    , ('2024-03-26', '火')
    , ('2024-03-27', '水')
    , ('2024-03-28', '木')
),
week_order(week, o) as (
  VALUES
      ('日', 1)
    , ('月', 2)
    , ('火', 3)
    , ('水', 4)
    , ('木', 5)
    , ('金', 6)
    , ('土', 7)
),
target as (
  select
    *
  from
    SALES
  where
      SALES_TYPE != '2'
  and UPDATED_NO is null
),
simple_summary as (
  select
      d
    , count(target.SALES_DATE) as CNT
    , coalesce(sum(SALES_AMT), 0) as AMT
    , wk
  from
      dates
      left outer join target on dates.d = target.SALES_DATE
  group by
      d
),
final_summary as (
  select
      wk as WEEK
    , round(avg(CNT)) as AVG_CNT
    , round(avg(AMT)) as AVG_AMT
  from
      simple_summary
  group by
      wk
)
select
    WEEK
  , printf("%,d", AVG_CNT) || '円' as AVG_CNT
  , printf("%,d", AVG_AMT) || '円' as AVG_AMT
from
             final_summary su
  inner join week_order    od using (WEEK)
order by
  od.o asc
;