WITH
    TOTAL_SAL AS (
        SELECT
            SUM(TOTAL_VALUE) AS TOTAL_AMT
        FROM
            CONVENIENCE
        WHERE
            SURVEY_YEAR = 2019
            AND KIND_CODE = '100'
    ),
    DECILE_GROUP AS (
        SELECT
            c1.PF_CODE,
            c1.TOTAL_VALUE AS TOTAL_SAL,
            c2.TOTAL_VALUE AS TOTAL_SHOP,
            (
                ROW_NUMBER() OVER (
                    ORDER BY
                        C1.TOTAL_VALUE DESC,
                        C2.TOTAL_VALUE ASC,
                        C1.PF_CODE ASC
                ) -1
            ) / 3 AS DC_NO
        FROM
            CONVENIENCE AS c1
            INNER JOIN CONVENIENCE AS c2 ON c1.PF_CODE = c2.PF_CODE
            AND c2.SURVEY_YEAR = 2019
            AND c2.KIND_CODE = '150'
        WHERE
            c1.SURVEY_YEAR = 2019
            AND c1.KIND_CODE = '100'
    ),
    DC_SUMMARY AS (
        SELECT
            DC_NO,
            SUM(TOTAL_SAL) AS DC_SAL,
            SUM(TOTAL_SHOP) AS DC_SHOP
        FROM
            DECILE_GROUP
        GROUP BY
            DC_NO
    )
SELECT
    ds.DC_NO + 1 AS NO,
    ds.DC_SAL AS TTL_SAL,
    ROUND((CAST(ds.DC_SAL AS REAL) / ts.TOTAL_AMT * 100), 1) AS PER_SAL,
    ROUND(
        CAST(
            SUM(ds.DC_SAL) OVER (
                ORDER BY
                    ds.DC_NO ASC
            ) AS REAL
        ) / ts.TOTAL_AMT * 100.0,
        1
    ) AS CUM_SAL,
    CAST((CAST(ds.DC_SAL AS REAL) / ds.DC_SHOP) AS INT) AS AVG_SAL
FROM
    DC_SUMMARY AS ds
    CROSS JOIN TOTAL_SAL AS ts
ORDER BY
    DS.DC_NO ASC;