WITH WEEK_GROUPED_SALES AS (
    SELECT
        WEEK_NUM
        , COUNT(SALES_AMT) AS NUM_OF_SALES
        , SUM(SALES_AMT) AS TOTAL_SALES_AMT
    FROM (
        SELECT
            SALES_DATE
            , strftime('%w', SALES_DATE) AS WEEK_NUM
            , SALES_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
        WEEK_NUM
)
, WEEK_NUM_TABLE AS (
    SELECT '0' AS WEEK_NUM
    UNION
    SELECT '1'
    UNION
    SELECT '2'
    UNION
    SELECT '3'
    UNION
    SELECT '4'
    UNION
    SELECT '5'
    UNION
    SELECT '6'
)
SELECT
    WEEK
    , AVG_CNT
    , AVG_AMT
    , 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 (
    SELECT
        CASE
            WHEN WNT.WEEK_NUM = '0' THEN '日'
            WHEN WNT.WEEK_NUM = '1' THEN '月'
            WHEN WNT.WEEK_NUM = '2' THEN '火'
            WHEN WNT.WEEK_NUM = '3' THEN '水'
            WHEN WNT.WEEK_NUM = '4' THEN '木'
            WHEN WNT.WEEK_NUM = '5' THEN '金'
            WHEN WNT.WEEK_NUM = '6' THEN '土'
            END
            AS WEEK
        , IFNULL(ROUND(WGS.NUM_OF_SALES / 4.0), 0) AS AVG_CNT
        , CAST(IFNULL(ROUND(WGS.TOTAL_SALES_AMT / 4.0), 0) AS INTEGER) AS AVG_AMT
    FROM
        WEEK_NUM_TABLE AS WNT
    LEFT JOIN
        WEEK_GROUPED_SALES AS WGS ON WNT.WEEK_NUM = WGS.WEEK_NUM
)
;