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
    ,printf("%.5f", 
    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
    ,printf("%.5f", 
    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)
    ,printf("%.5f", 
        (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
    ,S.SUP_VAL as SUPPORT
    ,C.CONF_VAL as CONFIDENCE
    ,L.LIFT_VAL 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 LIFT_VAL desc, SUP_VAL desc, CONF_VAL desc, S.ITEM_A