WITH
  -- 2019年全体売上合計
  TOTAL_SALES AS (
    SELECT
      SUM(TOTAL_VALUE) AS TOTAL_AMT
    FROM
      CONVENIENCE
    WHERE
      SURVEY_YEAR = 2019
      AND KIND_CODE = 100
  ),
  DECILE_GROUP AS (
    SELECT
      C1.PF_CODE,
      C1.TOTAL_VALUE AS TOTAL_SAL,
      C2.TOTAL_VALUE AS TOTAL_SHOP,
      NTILE (10) OVER (
        ORDER BY
          C1.TOTAL_VALUE desc,
          C2.TOTAL_VALUE asc,
          C1.PF_CODE asc
      ) AS DC_NO
    FROM
      CONVENIENCE C1
      INNER JOIN CONVENIENCE AS C2 ON C1.PF_CODE = C2.PF_CODE
      AND C2.SURVEY_YEAR = 2019
      AND C2.KIND_CODE = 150
    WHERE
      C1.SURVEY_YEAR = 2019
      AND C1.KIND_CODE = 100
  ),
  DC_SUMMARY AS (
    SELECT
      DC_NO,
      SUM(TOTAL_SAL) AS DC_SAL,
      SUM(TOTAL_SHOP) AS DC_SHOP
    FROM
      DECILE_GROUP
    GROUP BY
      DC_NO
  )
SELECT
  DS.DC_NO AS NO,
  DS.DC_SAL AS TTL_SAL,
  ROUND(CAST(DS.DC_SAL AS REAL) / TS.TOTAL_AMT * 100, 1) AS PER_SAL,
  ROUND(
    CAST(
      SUM(DS.DC_SAL) OVER (
        ORDER BY
          DS.DC_NO asc
      ) AS REAL
    ) / TS.TOTAL_AMT * 100,
    1
  ) AS CUM_SAL,
  CAST((CAST(DS.DC_SAL AS REAL) / DS.DC_SHOP) AS INT) AS AVG_SAL
FROM
  DC_SUMMARY AS DS
  CROSS JOIN TOTAL_SALES AS TS
ORDER BY
  DS.DC_NO asc;