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
,COUNT(1) AS AVG_CNT
,ROUND(AVG(SALES_AMT), 0) AS AVG_AMT
FROM (
SELECT
STRFTIME('%w', SALES_DATE) AS WEEK_NO
,SALES_AMT
FROM SALES
WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
AND SALES_TYPE <> 2
AND UPDATED_NO IS NULL
UNION ALL
SELECT 0 AS WEEK_NO
, 0 AS SALES_AMT
) 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
;