WITH SUB AS (SELECT PF_CODE,
TOTAL_SAL,
TOTAL_SHOP,
SUM(TOTAL_SAL) OVER() AS TOTAL2019,
NTILE(10) OVER(ORDER BY TOTAL_SHOP DESC, TOTAL_SHOP ASC, PF_CODE ASC) AS NO
FROM (SELECT PF_CODE,
SUM(CASE WHEN KIND_CODE = '100' THEN TOTAL_VALUE ELSE 0 END) AS TOTAL_SAL,
SUM(CASE WHEN KIND_CODE = '150' THEN TOTAL_VALUE ELSE 0 END) AS TOTAL_SHOP
FROM CONVENIENCE
WHERE SURVEY_YEAR = 2019
GROUP BY PF_CODE))
SELECT NO,
SUM(TOTAL_SAL) AS TTL_SAL,
ROUND(100.0 * SUM(TOTAL_SAL) / TOTAL2019, 1) AS PER_SAL,
ROUND(100.0 * SUM(TOTAL_SAL) OVER(ORDER BY NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / TOTAL2019) AS CUM_SAL,
SUM(TOTAL_SAL) / SUM(TOTAL_SHOP) AS AVG_SAL
FROM SUB
GROUP BY NO
ORDER BY NO