with combination_count as (
    select
        p1.item_code i1,
        p2.item_code i2,
        count(distinct p1.member_code) cc
    from purchase_history p1
    inner join purchase_history p2
    on p1.member_code = p2.member_code
        and p1.item_code != p2.item_code
    group by p1.item_code, p2.item_code
),
item_count as (
    select
        item_code,
        count(member_code) ic
    from purchase_history
    group by item_code
),
member_count as (
    select count(distinct member_code) mc
    from purchase_history
)
select
    i1 ITEM_A,
    i2 ITEM_B,
    round(100.0 * cc / (select mc from member_count), 5) SUPPORT,
    round(100.0 * cc / i1.ic, 5) CONFIDENCE,
    round(1.0 * cc * (select mc from member_count) / i1.ic / i2.ic, 5) LIFT
from combination_count c
inner join item_count i1
on c.i1 = i1.item_code
inner join item_count i2
on c.i2 = i2.item_code
order by
    LIFT desc,
    SUPPORT desc,
    CONFIDENCE desc,
    ITEM_A desc