WITH T1 AS(
    SELECT
        SUM(
            CASE KIND_CODE
                WHEN '100' THEN TOTAL_VALUE
                ELSE 0
            END
        ) AS TTL_SAL,
        SUM(
            CASE KIND_CODE
                WHEN '150' THEN TOTAL_VALUE
                ELSE 0
            END
        ) AS TTL_SHP,
        PF_CODE
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        PF_CODE
),
T2 AS(
    SELECT
        TTL_SAL,
        TTL_SHP,
        (RANK() OVER(ORDER BY TTL_SAL DESC, TTL_SHP ASC, PF_CODE ASC) + 2) / 3 AS NO
    FROM
        T1
),
T3 AS(
    SELECT
        NO,
        SUM(TTL_SAL) AS TTL_SAL,
        SUM(TTL_SHP) AS TTL_SHP
    FROM
        T2
    GROUP BY
        NO
),
T4 AS(
    SELECT
        NO,
        TTL_SAL,
        ROUND(
            100.0 * TTL_SAL / SUM(TTL_SAL) OVER(), 1
        ) AS PER_SAL,
        ROUND(
            100.0 * SUM(TTL_SAL) OVER(ORDER BY NO ASC) / SUM(TTL_SAL) OVER(), 1
        ) AS CUM_SAL,
        TTL_SAL / TTL_SHP AS AVG_SAL
    FROM T3
    ORDER BY
        NO ASC
)
SELECT
    *
    FROM T4