SELECT
    NO,
    TTL_SAL,
    PER_SAL,
    ROUND(CAST(SUM(TTL_SAL) OVER(ORDER BY NO ASC) AS FLOAT) / CAST(SUM(TTL_SAL) OVER() AS FLOAT) * 100, 1) AS CUM_SAL,
    CAST(CAST(TTL_SAL AS FLOAT) / CAST(TOTAL_SHOP AS FLOAT) AS INT) AS AVG_SAL
FROM (
    SELECT
        NO,
        SUM(TOTAL_SHOP) AS TOTAL_SHOP,
        SUM(TOTAL_YEN) AS TTL_SAL,
        ROUND(CAST(SUM(TOTAL_YEN) AS FLOAT) / CAST(ALL_YEN AS FLOAT) * 100, 1) AS PER_SAL
    FROM (
        SELECT
            PF_CODE,
            TOTAL_YEN,
            TOTAL_SHOP,
            (ROW_NUMBER() OVER(ORDER BY TOTAL_YEN DESC) - 1) / 3 + 1 AS NO,
            SUM(TOTAL_YEN) OVER() AS ALL_YEN
        FROM (
            SELECT
                PF_CODE,
                SUM(CASE WHEN KIND_CODE = 100 THEN TOTAL_VALUE ELSE 0 END) AS TOTAL_YEN,
                SUM(CASE WHEN KIND_CODE = 150 THEN TOTAL_VALUE ELSE 0 END) AS TOTAL_SHOP
            FROM
                CONVENIENCE
            WHERE
                SURVEY_YEAR = 2019
                -- AND KIND_CODE = 100
            GROUP BY
                PF_CODE
        )
    )
    GROUP BY
        NO
)
-- AS LOGS
-- LEFT JOIN (
--     SELECT
--     FROM
--         CONVENIENCE
--     WHERE
--         SURVEY_YEAR = 2019
--         AND KIND_CODE = 100
-- )