with tbl_agg_day as (
select
    sales_date
    , count(1) cnt
    , sum(sales_amt) amt
        --, count(1)
    , 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 '金'
    WHEN 6 THEN '土'
    end as week
    , CASE CAST(STRFTIME('%w', sales_date) AS INTEGER)
    WHEN 0 THEN 0
    WHEN 1 THEN 1
    WHEN 2 THEN 2
    WHEN 3 THEN 3
    WHEN 4 THEN 4
    WHEN 5 THEN 5
    WHEN 6 THEN 6
    end as week_no
from sales
where 1=1
    and sales_type!=2
    and updated_no is null
group by sales_date
)

, tbl_agg as (
select
    week
    , week_no
    , sum(cnt)/count(1) avg_cnt
    , sum(amt)/count(1) avg_amt
from tbl_agg_day
group by week, week_no
)




select week, avg_cnt
    ,   REPLACE(
    SUBSTR(
      '000000000000' || CAST(avg_amt AS TEXT), 
      -12
    ),
    SUBSTR(
      '000000000000' || CAST(avg_amt AS TEXT),
      -9, 3
    ),
    ',' || SUBSTR(
      '000000000000' || CAST(avg_amt AS TEXT), 
      -9, 3
    )
  ) || '円' AS avg_amt
from tbl_agg
order by week_no