with wk0 as 
( select sum (total_value)  as tol 
        from convenience 
        where survey_year = 2019 and kind_code = '100'
) 
, wk1 as
( select pf_code, total_value as gaku
        from convenience
    where survey_year = 2019 and kind_code = '100'
)
, wk2 as 
( select CON.pf_code, wk1.gaku as gaku, total_value as ten 
        from convenience as CON
        inner join wk1 on wk1.pf_code = CON.pf_code
    where CON.survey_year = 2019 and CON.kind_code = '150'
)
, wk3 as
( select *
    , floor (((ROW_NUMBER() over (order by gaku desc , ten , pf_code)) -1 ) /3) + 1 as NO
    from wk2
)
select TT.NO 
    , TT.TTL_SAL
    , round(cast(TT.TTL_SAL as real) * 100 / TT.TOTAL, 1) as PER_SAL
    , round(cast(sum(TTL_SAL) over(order by NO) as real) * 100 / TOTAL, 1) as CUM_SAL
    , floor(cast(TT.TTL_SAL as real) / TT.TTL_STR) as AVG_SAL
    
from (
        select NO
            , sum(gaku) as TTL_SAL
            , sum(ten) as TTL_STR
            , ( select tol TOTAL from wk0 ) as TOTAL
        from wk3
        group by NO
    ) as TT
    
order by NO