with amounts as (
    select
        pf_code,
        total_value as total_amounts
    from convenience
    where survey_year = '2019' and kind_code = '100'
),

shops as (
    select
        pf_code,
        total_value as total_shops
    from convenience
    where survey_year = '2019' and kind_code = '150'
),

pivot as (
    select
        a.pf_code,
        total_amounts,
        total_shops
    from amounts a
    join shops s using(pf_code)
),

grouped as (
    select
        row_number() over (order by total_amounts desc) as num,
        sum(total_amounts) over w as total_amounts,
        sum(total_shops) over w as total_shops
    from pivot
    window w as (order by total_amounts desc range between current row and 3 following)
),

grouped1 as (
select *
from grouped
where num % 3 = 1)

select
    row_number() over w2 as NO,
    total_amounts as TTL_SAL,
    round(cast(total_amounts as real) / cast(sum(total_amounts) over w2 as real) * 100, 1) as PER_SAL,
    round(cast(sum(total_amounts) over w1 as real) / cast(sum(total_amounts) over w2 as real) * 100, 1) as CUM_SAL,
    round(cast(total_amounts as real) / cast(total_shops as real), 0) as AVG_SAL
from grouped1
window w1 as (order by total_amounts desc range between unbounded preceding and current row),
w2 as (order by total_amounts desc range between unbounded preceding and unbounded following);