with tbl1 as (
    select
        session_id,
        item_code,
        row_number() over(partition by session_id order by item_code) as rank
    from purchase_history
),
tbl2 as (
    select
        session_id,
        item_code as item1
    from tbl1
    where rank=1
),
tbl3 as (
    select 
        session_id,
        item_code as item2
    from tbl1
    where rank=2
),
tbl4 as (
    select 
        tbl2.session_id,
        item1,
        item2
    from tbl2
    inner join tbl3
    on tbl2.session_id=tbl3.session_id
),
tbl5 as (
    select 
        count(session_id) as groupcount,
        item1,
        item2
    from tbl4
    group by item1,item2
),
tmp1 as (
    select
        count(distinct session_id) as custotal
    from purchase_history
),
tmp2 as (
    select
        session_id,
        item_code,
        row_number() over(partition by session_id order by ex_timestamp) as rank
    from purchase_history
),
tmp3 as (
    select
        count(session_id) as cus_subtotal,
        item_code
    from purchase_history
    group by item_code
),
tmp4 as (
    select 
        session_id,
        item_code as ITEM_A 
    from tmp2
    where rank=1
),
tmp5 as (
    select 
        session_id,
        item_code as ITEM_B
    from tmp2
    where rank=2
),
tmp6 as (
    select 
        tmp4.session_id,
        ITEM_A,
        ITEM_B
    from tmp4
    inner join tmp5
    on tmp4.session_id=tmp5.session_id
),
tmp7 as (
    select 
        tmp6.session_id,
        ITEM_A,
        ITEM_B,
        item1,
        item2
    from tmp6
    inner join tbl4
    on tmp6.session_id=tbl4.session_id
),
tmp8 as (
    select
        session_id,
        item_a,
        item_b,
        tmp7.item1,
        tmp7.item2,
        groupcount,
        cus_subtotal as cus_subtotal_a
    from tmp7
    inner join tbl5
    on tmp7.item1=tbl5.item1 and tmp7.item2=tbl5.item2
    inner join tmp3
    on tmp7.item_a=tmp3.item_code
),
tmp9 as (
    select 
        *,
        cus_subtotal as cus_subtotal_b
    from tmp8
    inner join tmp3
    on tmp8.item_b=tmp3.item_code
)
select
    item_a as ITEM_A,
    item_b as ITEM_B,
    round(cast(groupcount as real)/cast(custotal as real)*100,5) as SUPPORT,
    round(cast(groupcount as real)/cast(cus_subtotal_a as real)*100,5) as CONFIDENCE,
    round((cast(groupcount as real)/cast(cus_subtotal_a as real))/(cast(cus_subtotal_b as real)/cast(custotal as real)),5) as LIFT
from tmp9
inner join tmp1
group by item_a,item_b
order by lift desc,support desc,confidence desc,item_a desc;