-- select * from CONVENIENCE;
with SALES as (select 
PF_CODE, TOTAL_VALUE as AMOUNT
from CONVENIENCE
where KIND_CODE = '100' and SURVEY_YEAR = 2019)
, STORES as (select 
PF_CODE, TOTAL_VALUE as CNT
from CONVENIENCE
where KIND_CODE = '150' and SURVEY_YEAR = 2019
)
, SALES_GROUPS as (
select SALES.PF_CODE, SALES.AMOUNT as AMOUNT, STORES.CNT, row_number() over (order by SALES.AMOUNT desc, STORES.CNT, SALES.PF_CODE asc) as rk 
from SALES join STORES on SALES.PF_CODE = STORES.PF_CODE
order by SALES.AMOUNT desc, STORES.CNT asc, SALES.PF_CODE asc
)
, AGG_SALES_GROUPS as (
select ((SALES_GROUPS.rk - 1) / 3) + 1 as NO, sum(AMOUNT) as TTL_SAL , sum(CNT) as SUM_CNT
from SALES_GROUPS
group by ((SALES_GROUPS.rk - 1) / 3)
)
select NO, 
TTL_SAL, 
ROUND((1.0 * TTL_SAL * 100 / sum(TTL_SAL) over ()), 1) as PER_SAL,
ROUND(1.0 * SUM(TTL_SAL) over(ROWS UNBOUNDED PRECEDING) * 100 / sum(TTL_SAL) over (), 1) as CUM_SAL,
FLOOR(1.0 * TTL_SAL / SUM_CNT) as AVG_SAL
from AGG_SALES_GROUPS