with b1 as (
	select 
		 sales_amt
		,sales_date
	from sales
	where  sales_date between date('2024-03-01') AND date('2024-03-28')
	and  sales_type <> 2 and updated_NO IS NULL
	order by sales_date
)
, b2 as (
	select 
		 count(*) cnt
		,sum(sales_amt)  sum_amt
		,sales_date
	from b1
	group by sales_date
)
, b3 as (
	select
		(CAST(strftime('%d', sales_date) AS INTEGER ) % 7) AS WEEK
		,CAST(ROUND( 1.0 * sum(cnt) / count(*) ) AS INTERGER) AVG_CNT
		,CAST(ROUND( 1.0 * sum(sum_amt) / count(*)) AS INTEGER) AVG_AMT
	from b2
	group by (CAST(strftime('%d', sales_date) AS INTEGER ) % 7) 
),  b4 as (
select '日' WEEK ,IFNULL((SELECT AVG_CNT FROM b3 WHERE WEEK = 3), 0) AVG_CNT, IFNULL( (SELECT FORMAT("%,d", AVG_AMT) || '円' AS AVG_AMT FROM b3 WHERE WEEK = 3), '0円') AVG_AMT
UNION
select '月' WEEK ,IFNULL((SELECT AVG_CNT FROM b3 WHERE WEEK = 4), 0) AVG_CNT, IFNULL( (SELECT FORMAT("%,d", AVG_AMT) || '円' AS AVG_AMT FROM b3 WHERE WEEK = 4), '0円') AVG_AMT
UNION
select '火' WEEK ,IFNULL((SELECT AVG_CNT FROM b3 WHERE WEEK = 5), 0) AVG_CNT, IFNULL( (SELECT FORMAT("%,d", AVG_AMT) || '円' AS AVG_AMT FROM b3 WHERE WEEK = 5), '0円') AVG_AMT
UNION
select '水' WEEK ,IFNULL((SELECT AVG_CNT FROM b3 WHERE WEEK = 6), 0) AVG_CNT, IFNULL( (SELECT FORMAT("%,d", AVG_AMT) || '円' AS AVG_AMT FROM b3 WHERE WEEK = 6), '0円') AVG_AMT
UNION
select '木' WEEK ,IFNULL((SELECT AVG_CNT FROM b3 WHERE WEEK = 0), 0) AVG_CNT, IFNULL( (SELECT FORMAT("%,d", AVG_AMT) || '円' AS AVG_AMT FROM b3 WHERE WEEK = 0), '0円') AVG_AMT
UNION
select '金' WEEK ,IFNULL((SELECT AVG_CNT FROM b3 WHERE WEEK = 1), 0) AVG_CNT, IFNULL( (SELECT FORMAT("%,d", AVG_AMT) || '円' AS AVG_AMT FROM b3 WHERE WEEK = 1), '0円') AVG_AMT
UNION
select '土' WEEK ,IFNULL((SELECT AVG_CNT FROM b3 WHERE WEEK = 2), 0) AVG_CNT, IFNULL( (SELECT FORMAT("%,d", AVG_AMT) || '円' AS AVG_AMT FROM b3 WHERE WEEK = 2), '0円') AVG_AMT
)
select WEEK, AVG_CNT, AVG_AMT
from b4
order by
case WEEK
WHEN '日' THEN 1
WHEN '月' THEN 2
WHEN '火' THEN 3
WHEN '水' THEN 4
WHEN '木' THEN 5
WHEN '金'  THEN 6
WHEN '土' THEN 7
END
	;