WITH CONV_2019 AS (
    SELECT
        PF_CODE
        , MAX(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE END) AS SAL_AMT
        , MAX(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE END) AS STORE_NUM
        , ROW_NUMBER() OVER(
            ORDER BY
                MAX(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE END) DESC
                , MAX(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE END) ASC
                , PF_CODE ASC
            ) AS RANKING
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019
    GROUP BY
        PF_CODE
    ORDER BY
        SAL_AMT DESC
)
, SUM_SAL_AMT_2019 AS (
    SELECT
        SUM(SAL_AMT) AS SAL_AMT
    FROM
        CONV_2019
)


SELECT
    ROW_NUMBER() OVER(
            ORDER BY
                sub.SUM_SAL_AMT DESC
            ) AS NO
    , sub.SUM_SAL_AMT AS TTL_SAL
    , ROUND(sub.SUM_SAL_AMT * 100.0 / SUM_SAL_AMT_2019.SAL_AMT, 1) AS PER_SAL
    , ROUND(
            SUM(sub.SUM_SAL_AMT) OVER(
                ORDER BY
                    sub.SUM_SAL_AMT DESC
                )
            * 100.0 / SUM_SAL_AMT_2019.SAL_AMT
        , 1)
        AS CUM_SAL
    , sub.SUM_SAL_AMT / sub.SUM_STORE_NUM
FROM (
    SELECT
        PF_CODE
        , SAL_AMT
        , STORE_NUM
        , SUM(SAL_AMT) OVER (ROWS 2 PRECEDING) AS SUM_SAL_AMT
        , SUM(STORE_NUM) OVER (ROWS 2 PRECEDING) AS SUM_STORE_NUM
        , RANKING
    FROM
        CONV_2019
) AS sub
CROSS JOIN
    SUM_SAL_AMT_2019
WHERE
    sub.RANKING % 3 = 0
;