WITH pre AS(
SELECT
    PF_CODE,
    SUM(TOTAL_VALUE) FILTER(WHERE KIND_CODE = '100') AS 販売額,
    SUM(TOTAL_VALUE) FILTER(WHERE KIND_CODE = '150') AS 店舗数
FROM
    CONVENIENCE
WHERE
    SURVEY_YEAR = 2019
GROUP BY
    PF_CODE
),
pre2 AS(
SELECT
    PF_CODE,
    販売額,
    店舗数,
    RANK() OVER(ORDER BY 販売額 DESC, 店舗数, PF_CODE) AS ランク
FROM
    pre
),
pre3 AS(
SELECT
    CASE 
        WHEN ランク = 1 THEN 1
        WHEN ランク = 2 THEN 1
        WHEN ランク = 3 THEN 1
        WHEN ランク = 4 THEN 2
        WHEN ランク = 5 THEN 2
        WHEN ランク = 6 THEN 2
        WHEN ランク = 7 THEN 3
        WHEN ランク = 8 THEN 3
        WHEN ランク = 9 THEN 3
        WHEN ランク = 10 THEN 4
        WHEN ランク = 11 THEN 4
        WHEN ランク = 12 THEN 4
        WHEN ランク = 13 THEN 5
        WHEN ランク = 14 THEN 5
        WHEN ランク = 15 THEN 5
        WHEN ランク = 16 THEN 6
        WHEN ランク = 17 THEN 6
        WHEN ランク = 18 THEN 6
        WHEN ランク = 19 THEN 7
        WHEN ランク = 20 THEN 7
        WHEN ランク = 21 THEN 7
        WHEN ランク = 22 THEN 8
        WHEN ランク = 23 THEN 8
        WHEN ランク = 24 THEN 8
        WHEN ランク = 25 THEN 9
        WHEN ランク = 26 THEN 9
        WHEN ランク = 27 THEN 9
        WHEN ランク = 28 THEN 10
        WHEN ランク = 29 THEN 10
        WHEN ランク = 30 THEN 10
    ELSE 2 END AS NO,
    SUM(販売額) AS TTL_SAL,
    SUM(店舗数) AS 店舗数
FROM
    pre2
GROUP BY
    NO
),
pre4 AS(
SELECT
    NO,
    TTL_SAL,
    (SELECT SUM(TTL_SAL) FROM pre3) AS 合計額,
    ROUND(
        CAST(TTL_SAL AS real) / CAST( (SELECT SUM(TTL_SAL) FROM pre3) AS real) * 100
    ,1) AS PER_SAL,
    SUM(TTL_SAL) OVER(ORDER BY NO) AS 累計額,
    TRUNC(SUM(TTL_SAL) / SUM(店舗数)) AS AVG_SAL
       -- ROUND(
        --        CAST(TTL_SAL AS real) / CAST(SUM(TTL_SAL) over(ORDER BY NO DESC) AS real) * 100
        --,1) AS CUM_SAL
    
FROM
    pre3
GROUP BY
    NO
)
SELECT
    NO,
    TTL_SAL,
    PER_SAL,
    --累計額,
    --合計額,
    ROUND(
        CAST(累計額 AS real) / CAST(合計額 AS real)  * 100
        --/ 合計額 AS CUM_SAL
    ,1) AS CUM_SAL,
    AVG_SAL
FROM
    pre4
GROUP BY
    NO
ORDER BY
    NO