SELECT
    TILE_NO AS 'NO',
    TTL_SAL,
    PER_SAL,
    ROUND(
        SUM(PER_SAL) OVER (
            ORDER BY
                PER_SAL DESC ROWS UNBOUNDED PRECEDING
        ),
        1
    ) AS CUM_SAL,
    AVG_SAL
FROM
    (
        SELECT
            TILE_NO,
            SUM (SALES_VALUE) AS TTL_SAL,
            ROUND (
                SUM(SALES_VALUE) * 100.0 / (
                    SELECT
                        SUM(SALES_VALUE)
                    FROM
                        (
                            SELECT
                                PF_CODE,
                                TOTAL_VALUE AS SALES_VALUE
                            FROM
                                CONVENIENCE
                            WHERE
                                SURVEY_YEAR = 2019
                                AND KIND_CODE = '100'
                        )
                ),
                1
            ) AS PER_SAL,
            SUM(SALES_VALUE) / SUM(STORE_COUNT) AS AVG_SAL
        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;