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,
    A_count,
    B_count,
    1.0 * A_B_count/ all_count * 100 as SUPPORT,
    1.0 * A_B_count/ A_count * 100 as CONFIDENCE,
    (1.0 * A_B_count/ A_count) / (1.0*B_count/ all_count ) as LIFT
from
    temp
order by
    LIFT,
    SUPPORT,
    CONFIDENCE,
    ITEM_A

;