SELECT
    TILE_NO AS 'NO',
    TTL_SAL,
    ROUND (
        TTL_SAL * 100.0 / SUM_ALL,
        1
    ) AS PER_SAL,
    ROUND(
        SUM(TTL_SAL) OVER (
            ORDER BY
                TTL_SAL DESC ROWS UNBOUNDED PRECEDING
        ) * 100.0 / SUM_ALL,
        1
    ) AS CUM_SAL,
    AVG_SAL
FROM
    (
        SELECT
            TILE_NO,
            SUM(SALES_VALUE) AS TTL_SAL,
            SUM(SALES_VALUE) / SUM(STORE_COUNT) AS AVG_SAL,
            (
                SELECT
                    SUM(TOTAL_VALUE)
                FROM
                    CONVENIENCE
                WHERE
                    SURVEY_YEAR = 2019
                    AND KIND_CODE = '100'
            ) AS SUM_ALL
        FROM
            (
                SELECT
                    PF_CODE,
                    SALES_VALUE,
                    STORE_COUNT,
                    NTILE (10) OVER (
                        ORDER BY
                            SALES_VALUE DESC,
                            STORE_COUNT ASC,
                            PF_CODE ASC
                    ) AS TILE_NO
                FROM
                    (
                        SELECT
                            PF_CODE,
                            SALES_VALUE,
                            STORE_COUNT
                        FROM
                            (
                                SELECT
                                    PF_CODE,
                                    TOTAL_VALUE AS SALES_VALUE
                                FROM
                                    CONVENIENCE
                                WHERE
                                    SURVEY_YEAR = 2019
                                    AND KIND_CODE = '100'
                            ) AS A
                            INNER JOIN (
                                SELECT
                                    PF_CODE,
                                    TOTAL_VALUE AS STORE_COUNT
                                FROM
                                    CONVENIENCE
                                WHERE
                                    SURVEY_YEAR = 2019
                                    AND KIND_CODE = '150'
                            ) AS B USING (PF_CODE)
                    )
            )
        GROUP BY
            TILE_NO
    )
ORDER BY
    TILE_NO ASC;