with t0 as (
select item_code as i, count(distinct(member_code)) as c
from PURCHASE_HISTORY
group by 1
order by 1
),
t1 as (
select p0.item_code as i0,
p1.item_code as i1,
count(distinct(p0.member_code)) as c
from PURCHASE_HISTORY as p0
inner join PURCHASE_HISTORY as p1
on p0.item_code!=p1.item_code and
p0.member_code=p1.member_code
group by 1,2
order by 1,2
)
select i0 as 'ITEM_A',
i1 as 'ITEM_B',
round(t1.c*100.0/(select count(distinct(MEMBER_CODE)) from PURCHASE_HISTORY),5) as 'SUPPORT',
round(t1.c*100.0/x.c,5) as 'CONFIDENCE',
round(t1.c*1.0/x.c/y.c*(select count(distinct(MEMBER_CODE)) from PURCHASE_HISTORY),5) as 'LIFT'
from t1
inner join t0 as x
on t1.i0=x.i
inner join t0 as y
on t1.i1=y.i
order by 5 desc,3 desc,4 desc,1 desc