WITH SALES AS (
    SELECT
        PF_CODE
        ,SAL_VAL
        ,STORE_VAL
        ,ROW_NUMBER() OVER(ORDER BY SAL_VAL DESC, STORE_VAL DESC, PF_CODE) AS RANKING
        ,(ROW_NUMBER() OVER(ORDER BY SAL_VAL DESC, STORE_VAL, PF_CODE)-1)/3+1 AS GROUP_NO
    FROM (
        SELECT
            PF_CODE
            ,MAX(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE NULL END) AS SAL_VAL 
           ,MAX(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE ELSE NULL END) AS STORE_VAL 
        FROM CONVENIENCE
        WHERE
            SURVEY_YEAR = 2019
        GROUP BY PF_CODE
    )
),
TOTAL_SALES AS (
    SELECT
        SUM(SAL_VAL) AS TOTAL_SALES
    FROM
        SALES
),
GROUP_SALES AS (
    SELECT
        GROUP_NO
        ,SUM(SAL_VAL) AS SAL_VAL
        ,SUM(STORE_VAL) AS STORE_VAL
    FROM
        SALES
    GROUP BY
        GROUP_NO
)
SELECT
    GROUP_NO AS NO
    ,SAL_VAL AS TTL_SAL
    ,ROUND(SAL_VAL / CAST(TOTAL_SALES AS FLOAT) * 100, 1) AS PER_SAL
    ,ROUND((SUM(SAL_VAL) OVER (ORDER BY GROUP_NO)) / CAST(TOTAL_SALES AS FLOAT) * 100, 1) AS CUM_SAL
    ,SAL_VAL / STORE_VAL AS AVG_SAL
FROM GROUP_SALES
CROSS JOIN TOTAL_SALES
ORDER BY
    GROUP_NO;