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
)
,kakomae AS (
	SELECT
		day_of_week AS WEEK
		,strftime('%w', dateline) AS daynum
		,ROUND(AVG(dataCnt),0) AS AVG_CNT
		,ROUND(AVG(totalamt),0) AS AVG_AMT
	FROM timeline
	GROUP BY 1,2
)
,kako AS (
SELECT
	WEEK
	,daynum
	,AVG_CNT
	,REPLACE(CAST(AVG_AMT AS TEXT),'.0','') AS AVG_AMT
FROM kakomae
)
SELECT
	WEEK
	,AVG_CNT
	,CASE WHEN LENGTH(AVG_AMT) > 6
			THEN SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT)-6) || ',' ||
		      SUBSTR(AVG_AMT, LENGTH(AVG_AMT)-5, 3) || ',' ||
		      SUBSTR(AVG_AMT, LENGTH(AVG_AMT)-2, 3) || '円'
		WHEN LENGTH(AVG_AMT) > 3
		 	THEN SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT)-3) || ',' ||
		      SUBSTR(AVG_AMT, LENGTH(AVG_AMT)-2, 3) || '円'
		ELSE AVG_AMT || '円'
	END AS AVG_AMT
FROM kako
ORDER BY daynum