--【問題４】曜日別売上分析
WITH
WEEK_TABLE AS(
	SELECT	'0' AS WEEK_NUM , '日' AS WEEK
UNION	SELECT	'1'		, '月'
UNION	SELECT	'2'		, '火'
UNION	SELECT	'3'		, '水'
UNION	SELECT	'4'		, '木'
UNION	SELECT	'5'		, '金'
UNION	SELECT	'6'		, '土'
),
BASE AS(
SELECT
	SALES_DATE
,	strftime('%w',SALES_DATE) AS WEEK_NUM
,	COUNT(SALES_AMT) AS CNT
,	SUM(SALES_AMT) AS AMT
FROM
	SALES
WHERE
	SALES_TYPE <> 2
AND	UPDATED_NO IS NULL
AND	SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
GROUP BY
	SALES_DATE
),
BASE2 AS(
SELECT
	A.WEEK_NUM
,	A.WEEK
,	ROUND(SUM(COALESCE(B.CNT,0)) / 4.0,0) AS AVG_CNT
,	CAST(ROUND(SUM(COALESCE(B.AMT,0)) / 4.0,0) AS INTEGER) AS AVG_AMT
FROM
	WEEK_TABLE A
LEFT OUTER JOIN
	BASE B
ON
	A.WEEK_NUM = B.WEEK_NUM
GROUP BY
	A.WEEK_NUM
,	A.WEEK
)
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
	BASE2
ORDER BY
	WEEK_NUM