with TMP1 as (
select
    P1.SESSION_ID
    ,P1.MEMBER_CODE
    ,P1.ITEM_CODE as ITEM_A
    ,P2.ITEM_CODE as ITEM_B
    ,P1.EX_TIMESTAMP
from PURCHASE_HISTORY P1
    cross join
        PURCHASE_HISTORY P2
        on P1.SESSION_ID=P2.SESSION_ID
            and P1.ITEM_CODE<>P2.ITEM_CODE
)
,ALL_CUST as (
select 
    1.0*count(distinct MEMBER_CODE) as ALL_CNT
from PURCHASE_HISTORY
)
,SUP as (
select
    ITEM_A
    ,ITEM_B
    ,
    100.0*count(distinct MEMBER_CODE)
        /(select ALL_CNT from ALL_CUST)
        as SUP_VAL
from 
    TMP1
group by 1,2
)
,ITEM_A_CUST as (
select 
    ITEM_CODE as ITEM_A
    ,1.0*count(distinct MEMBER_CODE) as CUST_CNT
from PURCHASE_HISTORY
group by 1
)
,CONF as (
select
    T.ITEM_A
    ,ITEM_B
    ,
    100.0*count(distinct MEMBER_CODE)
        /CUST_CNT
        as CONF_VAL
from 
    TMP1 T
    left outer join 
        ITEM_A_CUST I 
        on T.ITEM_A=I.ITEM_A
group by 1,2
)
,LIFT as (
select
    T.ITEM_A
    ,T.ITEM_B
    ,I.CUST_CNT
    ,IB.CUST_CNT
    ,count(distinct MEMBER_CODE)
    ,
        (1.0*count(distinct MEMBER_CODE)/I.CUST_CNT)
        /(IB.CUST_CNT/(select ALL_CNT from ALL_CUST))
        as LIFT_VAL
from 
    TMP1 T
    left outer join 
        ITEM_A_CUST I 
        on T.ITEM_A=I.ITEM_A
    left outer join 
        ITEM_A_CUST IB
        on T.ITEM_B=IB.ITEM_A
group by 1,2,3,4
)
select
    S.ITEM_A
    ,S.ITEM_B
    ,1.0*round(S.SUP_VAL*100000)/100000 as SUPPORT
    ,1.0*round(C.CONF_VAL*100000)/100000 as CONFIDENCE
    ,1.0*round(L.LIFT_VAL*100000)/100000 as LIFT
from
    SUP S
    left outer join 
        CONF C
        on S.ITEM_A=C.ITEM_A and S.ITEM_B=C.ITEM_B
    left outer join
        LIFT L
        on L.ITEM_A=S.ITEM_A and L.ITEM_B=S.ITEM_B
order by 
cast (LIFT_VAL*100000 as int) desc
,cast (SUP_VAL*100000 as int) desc
,cast (CONF_VAL*100000 as int) desc
,S.ITEM_A
-- LIFT_VAL desc, SUP_VAL desc, CONF_VAL desc, S.ITEM_A desc