WITH TOTAL_DATA AS (
SELECT SUM(TOTAL_VALUE) AS ALL_HANBAI
  FROM CONVENIENCE
  WHERE SURVEY_YEAR = '2019'
    AND KIND_CODE = '100'
),
DETAIL_DATA AS (
SELECT WK1.PF_CODE,
       WK1.TOTAL_VALUE AS HANBAI,
       WK2.TOTAL_VALUE AS TENPO,
       NTILE(10) OVER (ORDER BY WK1.TOTAL_VALUE DESC,WK2.TOTAL_VALUE DESC,WK1.PF_CODE ASC) AS GP_CD
  FROM CONVENIENCE WK1 INNER JOIN CONVENIENCE WK2
                          ON WK1.PF_CODE = WK2.PF_CODE
                         AND WK2.KIND_CODE = '150'
                         AND WK1.SURVEY_YEAR = WK2.SURVEY_YEAR
 WHERE WK1.SURVEY_YEAR = '2019'
   AND WK1.KIND_CODE = '100'
),
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,
       CAST((CAST(GP_HANBAI AS REAL) / GP_TENPO) AS INT) AS AVG_SAL
  FROM GP_TANI_DATA CROSS JOIN TOTAL_DATA
 ORDER BY NO ASC