WITH WEEKDAY AS (
    SELECT '日' AS WEEK, 0 AS WEEKDAY UNION ALL
    SELECT '月' AS WEEK, 1 AS WEEKDAY UNION ALL
    SELECT '火' AS WEEK, 2 AS WEEKDAY UNION ALL
    SELECT '水' AS WEEK, 3 AS WEEKDAY UNION ALL
    SELECT '木' AS WEEK, 4 AS WEEKDAY UNION ALL
    SELECT '金' AS WEEK, 5 AS WEEKDAY UNION ALL
    SELECT '土' AS WEEK, 6 AS WEEKDAY
), SALES_PER_DAY AS (
    SELECT
        SALES.SALES_DATE,
        IFNULL(COUNT(SALES.SALES_AMT), 0) AS CNT,
        IFNULL(SUM(SALES.SALES_AMT), 0) AS 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.SALES_DATE
), SALES_PER_WEEK AS (
    SELECT
        WEEKDAY.WEEKDAY,
        WEEKDAY.WEEK,
        ROUND(IFNULL(SUM(SALES_PER_DAY.CNT)/4.0, 0)) AS AVG_CNT,
        CAST(ROUND(IFNULL(SUM(SALES_PER_DAY.AMT)/4.0, 0)) AS INTEGER) AS AVG_AMT
    FROM
        WEEKDAY
    LEFT JOIN
        SALES_PER_DAY ON
        CAST(STRFTIME('%w', SALES_PER_DAY.SALES_DATE) AS INTEGER) = WEEKDAY.WEEKDAY
    GROUP BY
        WEEKDAY.WEEKDAY
)
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
    SALES_PER_WEEK
ORDER BY
    WEEKDAY