WITH T1 AS(
    SELECT
        (CAST(SUBSTR(SALES_DATE, 9, 2) AS INTEGER) + 4) % 7 AS TYPE,
        SALES_AMT
    FROM
        SALES
    WHERE
        SALES_TYPE != 2
        AND UPDATED_NO IS NULL
        AND SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
), T2 AS(
    SELECT
        TYPE,
        COUNT(*) AS CNT,
        SUM(SALES_AMT) AS AMT
    FROM
        T1
    GROUP BY
        TYPE
), T3 AS(
    SELECT 0 AS TYPE
    UNION ALL
    SELECT
        TYPE + 1 AS TYPE
    FROM
        T3
    WHERE
        TYPE < 6
), T4 AS(
    SELECT
        T3.TYPE,
        IFNULL(CAST(ROUND(CNT * 0.25, 0) AS INTEGER), 0) AS AVG_CNT,
        IFNULL(CAST(ROUND(AMT * 0.25, 0) AS INTEGER), 0) AS AVG_AMT
    FROM
        T3
    LEFT OUTER JOIN
        T2 ON T2.TYPE = T3.TYPE
), T5 AS(
    SELECT
        CASE TYPE
            WHEN 0 THEN '日'
            WHEN 1 THEN '月'
            WHEN 2 THEN '火'
            WHEN 3 THEN '水'
            WHEN 4 THEN '木'
            WHEN 5 THEN '金'
            WHEN 6 THEN '土'
        END AS WEEK,
        AVG_CNT,
        CASE
            WHEN AVG_AMT < 1000 THEN AVG_AMT
            WHEN AVG_AMT < 1000000 THEN (AVG_AMT / 1000) || ',' || (AVG_AMT % 1000)
            WHEN AVG_AMT < 1000000000 THEN (AVG_AMT / 1000000) || ',' || ((AVG_AMT / 1000) % 1000) || ',' || (AVG_AMT % 1000)
        END || '円' AS AVG_AMT
    FROM
        T4
    ORDER BY
        TYPE ASC
)
SELECT
    *
FROM
    T5