with I as (
select
    distinct ITEM_CODE
from
    PURCHASE_HISTORY
), Items as (
select
    i1.ITEM_CODE as ITEM_A,
    i2.ITEM_CODE as ITEM_B,
    '0' as id
from
    I as i1
    cross join
    I as i2
where
    i1.ITEM_CODE <> i2.ITEM_CODE
), all_count as (
select
    '0' as id,
    count(distinct MEMBER_CODE) as all_count
from
    PURCHASE_HISTORY
), temp as (
select
    ITEM_A,
    ITEM_B,
    max(all_count.all_count) as all_count,
    count(distinct A.MEMBER_CODE) as A_count,
    count(distinct B.MEMBER_CODE) as B_count,
    count(distinct case when B.MEMBER_CODE = A.MEMBER_CODE then A.MEMBER_CODE else null end) as A_B_count
from
    Items
    left outer join
    PURCHASE_HISTORY as A
    on Items.ITEM_A=A.ITEM_CODE
    left outer join
    PURCHASE_HISTORY as B
    on Items.ITEM_B=B.ITEM_CODE
    left outer join
    all_count
    on all_count.id=Items.id
group by
     ITEM_A,
    ITEM_B
)
select
    ITEM_A,
    ITEM_B,
    round(A_B_count* 100.0/ all_count ,5) as SUPPORT,
    round(A_B_count* 100.0/ A_count ,5) as CONFIDENCE,
    round(A_B_count*all_count*1.0/ (1.0*B_count*A_count),5) as LIFT
from
    temp
where
    A_count>0 or B_count>0
order by
    LIFT desc,
    SUPPORT desc,
    CONFIDENCE desc,
    ITEM_A desc
;