with t1 as(
select
   a.MEMBER_CODE,a.ITEM_CODE as ITEM_A ,b.ITEM_CODE as ITEM_B
from
    PURCHASE_HISTORY as a join
    PURCHASE_HISTORY as b 
on
    a.ITEM_CODE<>b.ITEM_CODE
    and
    a.MEMBER_CODE=b.MEMBER_CODE
)
,a_b_buyer as(
select
    ITEM_A,ITEM_B
    ,count(distinct MEMBER_CODE)as a_b_buyer
from
    t1
group by
    ITEM_A,ITEM_B
)
,a_buyer as
(select
    ITEM_CODE as ITEM_A
    ,count(distinct MEMBER_CODE)as a_buyer
from
    PURCHASE_HISTORY
group by
    ITEM_CODE
)
,b_buyer as (
select
    ITEM_CODE as ITEM_B
    ,count(distinct MEMBER_CODE)as b_buyer
from
    PURCHASE_HISTORY
group by
    ITEM_CODE

)
,uu as(
select
    count(distinct MEMBER_CODE)as uu
from
    PURCHASE_HISTORY
)
,matome as(
select
    a_b_buyer.ITEM_A,a_b_buyer.ITEM_B,a_b_buyer,a_buyer.a_buyer,b_buyer.b_buyer,uu
from
    a_b_buyer
left join 
    a_buyer on a_b_buyer.ITEM_A=a_buyer.ITEM_A
left join 
    b_buyer on a_b_buyer.ITEM_B=b_buyer.ITEM_B
cross join
    uu
)

select
   ITEM_A,ITEM_B
    ,round((cast(a_b_buyer as float)/uu )*100,5) as SUPPORT
    ,round(cast(a_b_buyer as float)/a_buyer*100,5) as CONFIDENCE
    ,round((cast(a_b_buyer as float)/a_buyer)/(cast(b_buyer as float)/uu),5)as LIFT
from
    matome
order by
    5 desc
    ,3 desc
    ,4 desc
    ,1 desc