WITH t1 AS (
    SELECT * FROM CONVENIENCE WHERE SURVEY_YEAR = 2019
),
total AS (
    SELECT SUM(TOTAL_VALUE) AS TOTAL FROM t1 WHERE KIND_CODE = 100
),
t2 AS (
    SELECT 
        PF_CODE, 
        SUM(CASE WHEN KIND_CODE = 100 THEN TOTAL_VALUE ELSE 0 END) AS SALES,
        SUM(CASE WHEN KIND_CODE = 150 THEN TOTAL_VALUE ELSE 0 END) AS STORE_COUNT
    FROM t1
    GROUP BY PF_CODE
),
t3 AS (
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY SALES DESC, STORE_COUNT ASC, PF_CODE ASC) AS ROW_NUM
    FROM t2
),
t4 AS (
    SELECT 
        (ROW_NUM - 1) / 3 AS GROUP_ID,
        SUM(SALES) AS GROUP_SALES,
        SUM(STORE_COUNT) AS GROUP_STORE_COUNT
    FROM t3
    GROUP BY GROUP_ID
)
SELECT 
    GROUP_ID + 1 AS NO,
    GROUP_SALES AS TTL_SAL,
    ROUND((GROUP_SALES * 100.0 / total.TOTAL), 1) AS PER_SAL,
    ROUND((SUM(GROUP_SALES) OVER (ORDER BY GROUP_ID) * 100.0 / total.TOTAL), 1) AS CUM_SAL,
    FLOOR(GROUP_SALES / GROUP_STORE_COUNT) AS AVG_SAL
FROM t4, total