WITH basedata AS (
	SELECT
	  SALES_DATE
	  ,COUNT(*) AS dataCnt
	  ,SUM(SALES_AMT) AS totalamt
	FROM SALES
	WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
		AND SALES_TYPE <> 2
		AND UPDATED_NO IS NULL
	GROUP BY 1
)
,dates(dateline) AS (
	SELECT '2024-03-01'
	UNION ALL
	SELECT date(dateline, '+1 day')
	FROM dates
	WHERE date(dateline, '+1 day') <= '2024-03-28'
)
,timeline AS (
	SELECT 
		dateline
		,CASE strftime('%w', dateline)
		    WHEN '0' THEN '日'
		    WHEN '1' THEN '月'
		    WHEN '2' THEN '火'
		    WHEN '3' THEN '水'
		    WHEN '4' THEN '木'
		    WHEN '5' THEN '金'
		    WHEN '6' THEN '土'
		  	END AS day_of_week
		,CASE WHEN dataCnt IS NULL THEN 0 ELSE dataCnt END AS dataCnt
		,CASE WHEN totalamt IS NULL THEN 0 ELSE totalamt END AS totalamt
	FROM dates AS t0
	LEFT OUTER JOIN basedata AS t1
		ON t0.dateline = t1.SALES_DATE
)
SELECT
	day_of_week AS WEEK
	,ROUND(AVG(dataCnt),0) AS AVG_CNT
	,printf('%,.0f円', AVG(totalamt)) AS AVG_AMT
FROM timeline
GROUP BY 1
ORDER BY strftime('%w', dateline)