WITH T1 AS(
    SELECT
        SUM(
            CASE KIND_CODE
                WHEN 100 THEN TOTAL_VALUE
                ELSE 0
            END
        ) AS VAL,
        SUM(
            CASE KIND_CODE
                WHEN 150 THEN TOTAL_VALUE
                ELSE 0
            END
        ) AS CNT,
        RANK() OVER(
            ORDER BY
                SUM(TOTAL_VALUE) DESC,
                COUNT(*) ASC,
                PF_CODE ASC
        ) AS RNK
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        PF_CODE
), T2 AS(
    SELECT
        SUM(VAL) AS VAL,
        SUM(CNT) AS CNT,
        (RNK + 2) / 3 AS GRP
    FROM
        T1
    WHERE
        RNK <= 30
    GROUP BY
        GRP
    ORDER BY
        GRP ASC
), T3 AS(
    SELECT
        T2.GRP,
        T2.VAL,
        T2.CNT,
        SUM(
            CASE
                WHEN MT2.GRP <= T2.GRP THEN MT2.VAL
                ELSE 0
            END
        ) AS CUMVAL,
        SUM(
            MT2.VAL
        ) AS ALLVAL
    FROM
        T2
        CROSS JOIN
            T2 AS MT2
    GROUP BY
        T2.GRP
)
SELECT
    GRP AS NO,
    VAL AS TTL_SAL,
    ROUND(100.0 * VAL / ALLVAL, 1) AS PER_SAL,
    ROUND(100.0 * CUMVAL / ALLVAL, 1) AS CUM_SAL,
    VAL / CNT AS AVG_SAL
FROM
    T3
ORDER BY
    NO ASC