WITH t1 AS (
SELECT DISTINCT a.item_code a, b.item_code b
            FROM PURCHASE_HISTORY a
               , PURCHASE_HISTORY b
            WHERE a.item_code != b.item_code
            )
   , t2 AS (SELECT t1.a, t1.b, COUNT(distinct pua.SESSION_ID) cnt
            FROM t1
                 JOIN PURCHASE_HISTORY pua ON pua.ITEM_CODE = t1.a
                 JOIN PURCHASE_HISTORY pub ON pub.ITEM_CODE = t1.b AND pua.SESSION_ID = pub.SESSION_ID group by t1.a,t1.b)
   , t3 AS (SELECT item_code, COUNT(*) cnt
            FROM PURCHASE_HISTORY pu
            GROUP BY pu.item_code)

   select
       a ITEM_A,
       b ITEM_B,
       round(x*100,5) SUPPORT,
       round(y*100,5) CONFIDENCE,
       round(y/z,5) LIFT
       from (
SELECT t1.a
     , t1.b
     , CAST(IFNULL(t2.cnt, 0) AS real) / (SELECT COUNT(DISTINCT session_id) FROM PURCHASE_HISTORY) x
     , CAST(IFNULL(t2.cnt, 0) AS real) / ac.cnt y
     , CAST(IFNULL(bc.cnt, 0) AS real) / (SELECT COUNT(DISTINCT session_id) FROM PURCHASE_HISTORY) z
FROM t1
     LEFT JOIN t2 ON t1.a = t2.a AND t1.b = t2.b
     LEFT JOIN t3 ac ON t1.a = ac.ITEM_CODE
     LEFT JOIN t3 bc ON t1.b = bc.ITEM_CODE )
order by 5 desc, 3 desc,4 desc,1 desc;