with cte as (
select
    a.ITEM_CODE as ITEM_A
    ,b.ITEM_CODE as ITEM_B
    ,(select count(distinct MEMBER_CODE) from PURCHASE_HISTORY where ITEM_CODE=a.ITEM_CODE) as  BUY_A
    ,(select count(distinct MEMBER_CODE) from PURCHASE_HISTORY where ITEM_CODE=b.ITEM_CODE) as  BUY_B
    ,(
        select 
            count(distinct a2.MEMBER_CODE) 
        from
            PURCHASE_HISTORY as a2
            inner join
            PURCHASE_HISTORY as b2
            on a2.MEMBER_CODE=b2.MEMBER_CODE
            and a2.ITEM_CODE=a.ITEM_CODE
            and b2.ITEM_CODE=b.ITEM_CODE
    ) as BUY_AB
    ,(select count(distinct MEMBER_CODE) from PURCHASE_HISTORY) as TOTAL
from
    (select distinct ITEM_CODE from PURCHASE_HISTORY) as a
    cross join
    (select distinct ITEM_CODE from PURCHASE_HISTORY) as b
where
    ITEM_A!=ITEM_B
)
select
    ITEM_A
    ,ITEM_B
    ,round(100.0*BUY_AB/TOTAL,5) as SUPPORT
    ,round(100.0*BUY_AB/BUY_A,5) as CONFIDENCE
    ,round((1.0*BUY_AB/BUY_A)/(1.0*BUY_B/TOTAL),5) as LIFT
from
    cte
where
    BUY_AB!=0
order by
    LIFT desc
    ,SUPPORT desc
    ,CONFIDENCE desc
    ,ITEM_A desc