with ic as (
select p1.ITEM_CODE as ITEM_A, p2.ITEM_CODE as ITEM_B 
from purchase_history p1 join purchase_history p2 on p1.SESSION_ID = p2.SESSION_ID where p1.ITEM_CODE != p2.ITEM_CODE
)
, nums as (
select
ITEM_CODE
, count(1) as inum
from purchase_history
group by ITEM_CODE
)
, members as (
select count(distinct(member_code)) as mnum
from purchase_history
)
select 
ic.ITEM_A
, ic.ITEM_B
, ROUND(100.0 *(select count(1) from ic as ic2 where ic.ITEM_A = ic2.ITEM_A and ic.ITEM_B = ic2.ITEM_B) / members.mnum, 5) as SUPPORT
, ROUND(100.0 *(select count(1) from ic as ic2 where ic.ITEM_A = ic2.ITEM_A and ic.ITEM_B = ic2.ITEM_B) / na.inum , 5) as CONFIDENCE
, ROUND((1.0 * (select count(1) from ic as ic2 where ic.ITEM_A = ic2.ITEM_A and ic.ITEM_B = ic2.ITEM_B) / na.inum) / (1.0 * nb.inum / members.mnum), 5) as LIFT
-- , ROUND((1.0 * (select count(1) from ic as ic2 where ic.ITEM_A = ic2.ITEM_A and ic.ITEM_B = ic2.ITEM_B) / na.inum) / (nb.inum / members.mnum), 5) as LIFT 
-- , ROUND(100.0 *(select count(1) from ic as ic2 where ic.ITEM_A = ic2.ITEM_A and ic.ITEM_B = ic2.ITEM_B) / (select count(1) from purchase_history as ph where ph.ITEM_CODE = ic.ITEM_A) , 5) as CONFIDENCE
from ic cross join members join nums as na on ic.ITEM_A = na.ITEM_CODE join nums as nb on ic.ITEM_B = nb.ITEM_CODE
group by ITEM_A, ITEM_B
order by LIFT desc, SUPPORT desc, CONFIDENCE desc, ITEM_A desc