WITH SALES_AMOUNT AS (
    SELECT PF_CODE, TOTAL_VALUE AS AMOUNT
    FROM CONVENIENCE
    WHERE KIND_CODE = '100'
        AND SURVEY_YEAR = 2019
),
SHOP_COUNT AS (
    SELECT PF_CODE, TOTAL_VALUE AS CNT
    FROM CONVENIENCE
    WHERE KIND_CODE = '150'
        AND SURVEY_YEAR = 2019
),
GROUPED AS (
    SELECT NTILE(10) OVER (ORDER BY AMOUNT DESC, CNT, PF_CODE) AS CLASS, *
    FROM SALES_AMOUNT NATURAL INNER JOIN SHOP_COUNT
),
ALL_AMOUNT AS (
    SELECT SUM(AMOUNT) AS ALL_AMOUNT FROM SALES_AMOUNT
),
ALL_CNT AS (
    SELECT SUM(CNT) AS ALL_CNT FROM SHOP_COUNT
)


SELECT CLASS AS NO,
    SUM(AMOUNT) AS TTL_SAL,
    ROUND(100.0 * SUM(AMOUNT) / ALL_AMOUNT, 1) AS PER_SAL,
    ROUND(100.0 * SUM(SUM(AMOUNT)) OVER (ORDER BY CLASS) / ALL_AMOUNT, 1) AS CUM_SAL,
    SUM(AMOUNT) / SUM(CNT) AS AVG_SAL
FROM GROUPED CROSS JOIN ALL_AMOUNT CROSS JOIN ALL_CNT
GROUP BY CLASS, ALL_AMOUNT, ALL_CNT