SELECT CASE WEEKDAY.WEEK_NO
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
    ELSE ''
 END AS WEEK
,IFNULL(SALES_AVG.AVG_CNT, 0) AS AVG_CNT
,CASE
    WHEN LENGTH(IFNULL(SALES_AVG.AVG_AMT, 0)) > 6
    THEN SUBSTR(SALES_AVG.AVG_AMT, 1, LENGTH(SALES_AVG.AVG_AMT) - 6) || ',' || SUBSTR(SALES_AVG.AVG_AMT, LENGTH(SALES_AVG.AVG_AMT) - 5, 3) || ',' || SUBSTR(SALES_AVG.AVG_AMT, LENGTH(SALES_AVG.AVG_AMT) -2, 3)
    WHEN LENGTH(IFNULL(SALES_AVG.AVG_AMT, 0)) > 3
    THEN SUBSTR(SALES_AVG.AVG_AMT, 1, LENGTH(SALES_AVG.AVG_AMT) - 3) || ',' || SUBSTR(SALES_AVG.AVG_AMT, LENGTH(SALES_AVG.AVG_AMT) - 2, 3)
    ELSE IFNULL(SALES_AVG.AVG_AMT, 0)
 END || '円' AS AVG_AMT
FROM (
	SELECT '0' AS WEEK_NO
	UNION ALL
	SELECT '1' AS WEEK_NO
	UNION ALL
	SELECT '2' AS WEEK_NO
	UNION ALL
	SELECT '3' AS WEEK_NO
	UNION ALL
	SELECT '4' AS WEEK_NO
	UNION ALL
	SELECT '5' AS WEEK_NO
	UNION ALL
	SELECT '6' AS WEEK_NO
) AS WEEKDAY
LEFT OUTER JOIN (
	SELECT SALES_DATA.WEEK_NO
	,CAST(ROUND(SUM(SALES_CNT) / 4, 0) AS INTEGER) AS AVG_CNT
	,CAST(ROUND(SUM(SALES_AMT) / 4, 0) AS INTEGER) AS AVG_AMT
	FROM (
		SELECT SALES_DATE
		,STRFTIME('%w', SALES_DATE) AS WEEK_NO
		,COUNT(1) AS SALES_CNT
		,SUM(SALES_AMT) AS SALES_AMT
		FROM SALES
		WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
		AND SALES_TYPE <> 2
		AND UPDATED_NO IS NULL
		GROUP BY SALES_DATE
	) AS SALES_DATA
	GROUP BY SALES_DATA.WEEK_NO
) AS SALES_AVG
ON SALES_AVG.WEEK_NO = WEEKDAY.WEEK_NO
ORDER BY WEEKDAY.WEEK_NO ASC
;