WITH WEEKDAYS AS (
    SELECT '0' AS WEEKDAY, '日' AS WEEK UNION ALL
    SELECT '1', '月' UNION ALL
    SELECT '2', '火' UNION ALL
    SELECT '3', '水' UNION ALL
    SELECT '4', '木' UNION ALL
    SELECT '5', '金' UNION ALL
    SELECT '6', '土'
)
SELECT
    WEEKDAYS.WEEK AS WEEK,
    IFNULL(AVG_CNT, 0) AS AVG_CNT,
    CASE
        WHEN AVG_AMT_INT >= 1000000 THEN SUBSTR(AVG_AMT_INT, 1, LENGTH(AVG_AMT_INT) - 6) || ',' || SUBSTR(AVG_AMT_INT, LENGTH(AVG_AMT_INT) - 5, 3) || ',' || SUBSTR(AVG_AMT_INT, LENGTH(AVG_AMT_INT) - 2, 3)
        WHEN AVG_AMT_INT >= 1000 THEN SUBSTR(AVG_AMT_INT, 1, LENGTH(AVG_AMT_INT) - 3) || ',' || SUBSTR(AVG_AMT_INT, LENGTH(AVG_AMT_INT) - 2, 3)
        ELSE IFNULL(AVG_AMT_INT, 0)
    END || '円' AS AVG_AMT
FROM
    WEEKDAYS
LEFT JOIN
    (
        SELECT
            SALES_DATE,
            SALES_TYPE,
            UPDATED_NO,
            SALES_NO,
            SALES_AMT,
            IFNULL(ROUND(COUNT(SALES_NO) * 1.0 / COUNT(DISTINCT strftime('%Y-%m-%d', SALES_DATE))), 0) AS AVG_CNT,
            CAST(ROUND(SUM(SALES_AMT) * 1.0 / COUNT(DISTINCT strftime('%Y-%m-%d', SALES_DATE))) AS INTEGER) AS AVG_AMT_INT
        FROM
            SALES
        GROUP BY
            strftime('%w', SALES_DATE)
    ) AS SALES
ON
    WEEKDAYS.WEEKDAY = strftime('%w', SALES.SALES_DATE)
    AND SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
    AND SALES_TYPE != 2
    AND UPDATED_NO IS NULL
GROUP BY
    WEEKDAYS.WEEKDAY
ORDER BY
    WEEKDAYS.WEEKDAY;