WITH SUB1 AS (SELECT MEMBER_CODE, ITEM_CODE,
COUNT(1) OVER(PARTITION BY ITEM_CODE) AS ITEM_CNT,
MEMBER_CNT
FROM PURCHASE_HISTORY PH
CROSS JOIN (SELECT
COUNT(DISTINCT MEMBER_CODE) AS MEMBER_CNT
FROM PURCHASE_HISTORY)),
SUB2 AS (SELECT S1.ITEM_CODE AS ITEM_A,
S2.ITEM_CODE AS ITEM_B,
S1.ITEM_CNT AS A_CNT,
S2.ITEM_CNT AS B_CNT,
COUNT(*) AS AB_CNT,
S1.MEMBER_CNT AS M_CNT
FROM SUB1 S1 INNER JOIN SUB1 S2
ON S1.MEMBER_CODE = S2.MEMBER_CODE
WHERE S1.ITEM_CODE <> S2.ITEM_CODE
GROUP BY ITEM_A, ITEM_B, A_CNT, B_CNT, M_CNT)
SELECT ITEM_A, ITEM_B,
ROUND(100.0 * AB_CNT / M_CNT, 5) AS SUPPORT,
ROUND(100.0 * AB_CNT / A_CNT, 5) AS CONFIDENCE,
ROUND(1.0 * AB_CNT / A_CNT / (1.0 * B_CNT / M_CNT), 5) AS LIFT
FROM SUB2
ORDER BY LIFT DESC,
SUPPORT DESC,
CONFIDENCE DESC,
ITEM_A DESC