with amts as (
select * from convenience
where survey_year = 2019 and KIND_CODE = 100
)
, stores as (
select * from convenience
where survey_year = 2019 and KIND_CODE = 150
)
, total_amts as (
select sum(total_value) as tamt from amts
)
, aas as (
select 
amts.PF_CODE as code
, amts.TOTAL_VALUE as amt
, stores.TOTAL_VALUE as store
from 
amts join stores on amts.PF_CODE = stores.PF_CODE
)
, aasr as (
select *
, rank() over(order by amt desc, store asc, code asc) as ra
from aas
)
, aasrg as (
select 
((ra - 1) / 3) + 1 as ra
, sum(amt) as TTL_SAL
, FLOOR(1.0 * sum(amt) / sum(store)) as AVG_SAL 
from aasr join total_amts
group by (ra-1) / 3
order by TTL_SAL desc
)
select
ra as NO
, aasrg.TTL_SAL as TTL_SAL
, ROUND(100.0 * aasrg.TTL_SAL / total_amts.tamt, 1) as PER_SAL
, ROUND(100.0 * sum(aasrg.TTL_SAL) over (order by ra) / total_amts.tamt, 1) as CUM_SAL
, aasrg.AVG_SAL as AVG_SAL
from aasrg join total_amts
order by NO
-- select * from aasr
-- select * from aasr