WITH
  -- 売上の降順テーブル
  SALES AS (
    SELECT
      SURVEY_YEAR,
      PF_CODE,
      TOTAL_VALUE AS SALES,
      -- 1位から順に3つずつIDを付与
      (
        ROW_NUMBER() OVER (
          ORDER BY
            TOTAL_VALUE desc
        ) -1
      ) / 3 + 1 AS GROUP_CODE,
      -- 全体売上合計
      (
        SELECT
          SUM(TOTAL_VALUE)
        FROM
          CONVENIENCE
        WHERE
          SURVEY_YEAR = 2019
          AND KIND_CODE = 100
      ) AS TOTAL_SALES
    FROM
      CONVENIENCE
    WHERE
      SURVEY_YEAR = 2019
      AND KIND_CODE = 100
    ORDER BY
      TOTAL_VALUE desc,
      PF_CODE asc
  ),
  -- 店舗数のテーブル
  SHOPS AS (
    SELECT
      SURVEY_YEAR,
      PF_CODE,
      TOTAL_VALUE AS SHOPS
    FROM
      CONVENIENCE
    WHERE
      SURVEY_YEAR = 2019
      AND KIND_CODE = 150
    ORDER BY
      TOTAL_VALUE desc
  )
SELECT
  GROUP_CODE AS NO,
  SUM(SALES) AS TTL_SAL,
  ROUND(CAST(SUM(SALES) AS REAL) / TOTAL_SALES * 100, 1) AS PER_SAL,
  ROUND(
    CAST(
      SUM(SUM(SALES)) OVER (
        ROWS BETWEEN unbounded preceding
        AND CURRENT ROW
      ) AS REAL
    ) / TOTAL_SALES * 100,
    1
  ) AS CUM_SAL,
  SUM(SALES) / SUM(SHOPS) AS AVG_SAL
FROM
  SALES
  INNER JOIN SHOPS ON SALES.PF_CODE = SHOPS.PF_CODE
GROUP BY
  GROUP_CODE
ORDER BY
  SALES desc,
  SHOPS asc,
  SALES.PF_CODE asc