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 0 AS TYPE
    UNION ALL
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
    UNION ALL
    SELECT 6
), T3 AS(
    SELECT
        TYPE,
        COUNT(*) AS CNT,
        SUM(SALES_AMT) AS AMT
    FROM
        T1
    GROUP BY
        TYPE
), T4 AS(
    SELECT
        T2.TYPE,
        CASE
            WHEN CNT IS NOT NULL THEN CNT
            ELSE 0
        END AS CNT,
        CASE
            WHEN AMT IS NOT NULL THEN AMT
            ELSE 0
        END AS AMT
    FROM
        T2
    LEFT OUTER JOIN
        T3 ON T2.TYPE = T3.TYPE
), T5 AS(
    SELECT
        TYPE,
        ROUND(CNT * 0.25, 0) AS AVG_CNT,
        CAST(ROUND(AMT * 0.25, 0) AS INTEGER) AS AVG_AMT
    FROM
        T4
), T6 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)
            ELSE (AVG_AMT / 1000000) || ',' || ((AVG_AMT / 1000) % 1000) || ',' || (AVG_AMT % 1000)
        END || '円' AS AVG_AMT
    FROM
        T5
    ORDER BY
        TYPE ASC
)

SELECT
    *
FROM
    T6

-- SELECT
--     COUNT(*) AS CNT,
--     SUM(SALES_AMT) AS AMT,