WITH TOTAL_DATA AS (
SELECT SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) AS ALL_HANBAI,
       SUM(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE ELSE 0 END) AS ALL_TENPO
  FROM CONVENIENCE
  WHERE SURVEY_YEAR = '2019'
),
DETAIL_DATA AS (
SELECT PF_CODE,
       HANBAI,
       TENPO,
       NTILE(10) OVER (ORDER BY HANBAI DESC,TENPO DESC,PF_CODE ASC) AS GP_CD
  FROM (SELECT PF_CODE,
               SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) AS HANBAI,
               SUM(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE ELSE 0 END) AS TENPO
          FROM CONVENIENCE
         WHERE SURVEY_YEAR = '2019'
         GROUP BY PF_CODE
       )
),
GP_TANI_DATA AS (
SELECT GP_CD,
       SUM(HANBAI) AS GP_HANBAI,
       SUM(TENPO) AS GP_TENPO
  FROM DETAIL_DATA
  GROUP BY GP_CD
)
SELECT GP_CD AS NO,
       GP_HANBAI AS TTL_SAL,
       ROUND(CAST(GP_HANBAI AS REAL) / ALL_HANBAI * 100,1) AS PER_SAL,
       ROUND(CAST(SUM(GP_HANBAI) OVER (ORDER BY GP_CD ASC) AS REAL) / ALL_HANBAI * 100,1) AS CUM_SAL,
       ROUND(GP_HANBAI / GP_TENPO,0) AS AVG_SAL
  FROM GP_TANI_DATA,TOTAL_DATA
 ORDER BY NO ASC