with MEMBER as (
	select
		count(distinct MEMBER_CODE) * 1.0 as m_count
	from PURCHASE_HISTORY
)
,item_cnt as (
	select
	    ITEM_CODE,
		count(*) as i_count
	from PURCHASE_HISTORY
	group by ITEM_CODE
)
,sub as (
	SELECT
		distinct a.item_code item_a, b.item_code item_b
		,count(a.MEMBER_CODE) over (partition by a.item_code, b.item_code) * 1.0 as ab_count
		,ia.i_count * 1.0 as a_count
		,ib.i_count * 1.0 as b_count
		,m_count
	FROM PURCHASE_HISTORY a
	JOIN PURCHASE_HISTORY b
		 ON a.session_id = b.session_id
	join  item_cnt ia
		on a.item_code = ia.item_code
	join  item_cnt ib
		on b.item_code = ib.item_code
	cross join MEMBER
	WHERE a.item_code != b.item_code
)
select
	item_a as ITEM_A, item_b as ITEM_B,
	round(ab_count / m_count * 100.0, 6) as SUPPORT,
	round(ab_count / a_count * 100.0, 6) as CONFIDENCE,
	round((ab_count / a_count) / (b_count / m_count) , 6) as LIFT
from sub
order by
	LIFT desc, SUPPORT desc, CONFIDENCE desc, item_a desc