with kind100 as(
	select
		 PF_CODE , TOTAL_VALUE as SAL
		,sum(TOTAL_VALUE) over () as SALTOTAL2019
	from
		CONVENIENCE
	where
		SURVEY_YEAR = 2019
	and KIND_CODE = '100'
)
, kind150 as(
	select
		 PF_CODE , TOTAL_VALUE as TENPOSU
	from
		CONVENIENCE
	where
		SURVEY_YEAR = 2019
	and KIND_CODE = '150'
)
, sub1 as(
	select
		a.PF_CODE, SAL, SALTOTAL2019, TENPOSU,
		(row_number() over (order by SAL desc, TENPOSU, a.PF_CODE)-1) / 3 as group_id
	from kind100 a inner join kind150 b on a.PF_CODE = b.PF_CODE
)
, sub2 as(
	select
		group_id +1 as NO, SUM(SAL) as SAL, MIN(SALTOTAL2019) as SALTOTAL2019, sum(TENPOSU) as TENPOSU
	from sub1
	group by group_id
)

select NO, SAL as TTL_SAL
	,round(SAL * 1.0 / SALTOTAL2019 * 1.0 * 100, 1) as PER_SAL
	,round(sum(SAL) over (order by NO) * 1.0  / SALTOTAL2019 * 1.0 * 100, 1) as CUM_SAL
	,round(SAL  / TENPOSU, 0) as AVG_SAL
from sub2
ORDER BY NO