WITH
SALES AS (
    SELECT
        PF_CODE,
        TOTAL_VALUE
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019 AND
        KIND_CODE = '100'
),
STORES AS (
    SELECT
        PF_CODE,
        TOTAL_VALUE
    FROM
        CONVENIENCE
    WHERE
        SURVEY_YEAR = 2019 AND
        KIND_CODE = '150'
),
TOTAL AS (
    SELECT
        NO,
        SUM(SALES) AS TTL_SAL,
        FLOOR(SUM(SALES) / SUM(STORES)) AS AVG_SAL
    FROM (
        SELECT
            (
                RANK() OVER (
                    ORDER BY
                        SALES.TOTAL_VALUE DESC,
                        STORES.TOTAL_VALUE DESC,
                        STORES.PF_CODE ASC
                ) + 2
            ) / 3 AS NO,
            SALES.PF_CODE,
            SALES.TOTAL_VALUE AS SALES,
            STORES.TOTAL_VALUE AS STORES
        FROM
            SALES
        INNER JOIN
            STORES ON
            STORES.PF_CODE = SALES.PF_CODE
    ) AS TOTAL
    GROUP BY
        NO
    ORDER BY
        NO
)
SELECT
    A.NO,
    A.TTL_SAL,
    ROUND(
        A.TTL_SAL * 100.0 /
        (SELECT SUM(B.TTL_SAL) FROM TOTAL AS B),
    1) AS PER_SAL,
    ROUND(
        (SELECT SUM(B.TTL_SAL) FROM TOTAL AS B WHERE B.NO <= A.NO )* 100.0 /
        (SELECT SUM(B.TTL_SAL) FROM TOTAL AS B),
    1) AS CUM_SAL,
    A.AVG_SAL
FROM
    TOTAL AS A