WITH SAL AS (
	SELECT TOTAL_VALUE SAL_100
		,PF_CODE
	FROM CONVENIENCE
	WHERE SURVEY_YEAR = '2019'
	AND KIND_CODE = 100
),
SHOP AS (
	SELECT TOTAL_VALUE SHOP_150
		,PF_CODE
	FROM CONVENIENCE
	WHERE SURVEY_YEAR = '2019'
	AND KIND_CODE = 150
)
SELECT *
FROM
(
	SELECT SAL_100
		,SHOP_150
		,rank() over (partition by A.PF_CODE order by SAL_100 DESC,SHOP_150) as TEAM
	FROM SAL A
		,SHOP B
	WHERE A.PF_CODE = B.PF_CODE
)