WITH
    SaleStoreData AS (
        SELECT
            PF_CODE
          , SUM(
                CASE
                    WHEN KIND_CODE = '100' THEN TOTAL_VALUE
                    ELSE 0
                END
            ) AS TOTAL_SALE
          , SUM(
                CASE
                    WHEN KIND_CODE = '150' THEN TOTAL_VALUE
                    ELSE 0
                END
            ) AS STORE_COUNT
        FROM
            CONVENIENCE
        WHERE
            SURVEY_YEAR = 2019
        GROUP BY
            PF_CODE
    )
  , RankedData AS (
        SELECT
            PF_CODE
          , TOTAL_SALE
          , STORE_COUNT
          , RANK() OVER (
                ORDER BY
                    TOTAL_SALE DESC
                  , STORE_COUNT
                  , PF_CODE
            ) AS RANK
        FROM
            SaleStoreData
    )
  , GroupedData AS (
        SELECT
            CEIL(RANK / 3.0) AS NO
          , SUM(TOTAL_SALE) AS TTL_SAL
          , SUM(STORE_COUNT) AS TTL_STORE
          , CAST(SUM(TOTAL_SALE) AS FLOAT) / (
                SELECT
                    SUM(TOTAL_SALE)
                FROM
                    SaleStoreData
            ) * 100 AS PER_SAL
        FROM
            RankedData
        GROUP BY
            CEIL(RANK / 3.0)
    )
  , CumulativeData AS (
        SELECT
            NO
          , TTL_SAL
          , PER_SAL
          , SUM(PER_SAL) OVER (
                ORDER BY
                    NO
            ) AS CUM_SAL
          , TTL_STORE
        FROM
            GroupedData
    )
SELECT
    NO
  , TTL_SAL
  , ROUND(PER_SAL, 1) AS PER_SAL
  , ROUND(CUM_SAL, 1) AS CUM_SAL
  , TTL_SAL / TTL_STORE AS AVG_SAL
FROM
    CumulativeData
ORDER BY
    NO;