WITH
C AS (SELECT * FROM CONVENIENCE WHERE SURVEY_YEAR = 2019 AND KIND_CODE = '100'),
T AS (SELECT * FROM CONVENIENCE WHERE SURVEY_YEAR = 2019 AND KIND_CODE = '150'),
RR AS (SELECT (ROW_NUMBER() OVER (ORDER BY C.TOTAL_VALUE DESC, T.TOTAL_VALUE ASC)-1)/3+1 AS NO, C.PF_CODE, C.TOTAL_VALUE FROM C JOIN T USING (PF_CODE)),
TT AS (SELECT NO, SUM(T.TOTAL_VALUE) AS TT FROM RR JOIN T USING (PF_CODE) GROUP BY NO),
RR2 AS (SELECT NO, SUM(TOTAL_VALUE) AS TTL, SUM(SUM(TOTAL_VALUE)) OVER (ORDER BY NO) AS CUM FROM RR GROUP BY NO)
SELECT NO, TTL AS TTL_SAL,
(SELECT ROUND(100.0*TTL/SUM(C.TOTAL_VALUE), 1) FROM C) AS PER_SAL,
(SELECT ROUND(100.0*CUM/SUM(C.TOTAL_VALUE), 1) FROM C) AS CUM_SAL,
(SELECT ROUND(TTL/SUM(TT.TT)) FROM TT WHERE RR2.NO = TT.NO) AS AVG_SAL
FROM RR2 GROUP BY NO;