WITH SUB AS (
    SELECT 
        PF_CODE,
        SUM(CASE WHEN KIND_CODE='100' THEN TOTAL_VALUE ELSE 0 END) AS SAL,
        SUM(CASE WHEN KIND_CODE='150' THEN TOTAL_VALUE ELSE 0 END) AS SHOP,
        RANK()OVER(ORDER BY 
            SUM(CASE WHEN KIND_CODE='100' THEN TOTAL_VALUE ELSE 0 END) DESC,
            SUM(CASE WHEN KIND_CODE='150' THEN TOTAL_VALUE ELSE 0 END) ASC, 
            PF_CODE ASC
            ) AS RANKING
    FROM CONVENIENCE
    WHERE SURVEY_YEAR=2019
    GROUP BY PF_CODE
    ORDER BY RANKING ASC
    ),
SUB2 AS (
    SELECT
        CASE 
            WHEN MOD(RANKING,3)=1 THEN CAST(RANKING/3 AS INTEGER)+1
            WHEN MOD(RANKING,3)=2 THEN CAST(RANKING/3 AS INTEGER)+1
            WHEN MOD(RANKING,3)=0 THEN CAST(RANKING/3 AS INTEGER)
        END AS NO,
        SUM(SAL) AS TTL_SAL,
        ROUND(
            CAST(SUM(SAL) AS REAL)/CAST((SELECT SUM(SAL) FROM SUB) AS REAL)*100
            ,1) AS PER_SAL,
        ROUND(
            CAST(SUM(SAL) AS REAL)/CAST((SELECT SUM(SHOP) FROM SUB) AS REAL)*100
            ,0) AS AVG_SAL
    FROM
        SUB
    GROUP BY NO
    ORDER BY RANKING
    )
SELECT * FROM SUB2;