WITH
  -- 売上の降順テーブル
  SALES AS (
    SELECT
      SURVEY_YEAR,
      PF_CODE,
      TOTAL_VALUE AS SALES,
      -- 全体売上合計
      (
        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,
      PF_CODE asc
  ),
  -- 売上と店舗数をガッチャンコして連番を振ったテーブル
  SUB AS (
    SELECT
      SALES.PF_CODE,
      SALES.SALES AS SALES,
      SHOPS.SHOPS AS SHOPS,
      SALES.TOTAL_SALES AS TOTAL_SALES,
      -- 1位から順に3つずつIDを付与
      (
        ROW_NUMBER() OVER (
          ORDER BY
            SALES.SALES desc
        ) -1
      ) / 3 + 1 AS GROUP_CODE
    FROM
      SALES
      INNER JOIN SHOPS ON SALES.PF_CODE = SHOPS.PF_CODE
    ORDER BY
      SALES.SALES desc,
      SHOPS.SHOPS asc,
      SALES.PF_CODE asc
  )
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,
  CAST((CAST(SUM(SALES) AS REAL) / SUM(SHOPS)) AS INT) AS AVG_SAL
FROM
  SUB
GROUP BY
  GROUP_CODE