WITH t AS(
    SELECT DISTINCT
        ph1.item_code AS ITEM_A
        , ph2.item_code AS ITEM_B
        , ph1.member_code
    FROM
        PURCHASE_HISTORY AS ph1
    LEFT JOIN
        PURCHASE_HISTORY AS ph2
    ON
        ph1.session_id = ph2.session_id
        AND ph1.item_code != ph2.item_code
), ab_num AS(
    SELECT ITEM_A, ITEM_B, COUNT(DISTINCT member_code) AS num FROM  t GROUP BY 1, 2
), a_num AS(
    SELECT ITEM_A, COUNT(DISTINCT member_code) AS num FROM  t GROUP BY 1
), all_num AS(
    SELECT COUNT(DISTINCT member_code) AS num FROM t
), preprocess AS(
SELECT DISTINCT
    t.ITEM_A
    , t.ITEM_B
    , 1.0*ab_num.num AS ab_num
    , 1.0*a.num AS a_num
    , 1.0*b.num AS b_num
    , 1.0*all_num.num AS all_num
FROM
     t
LEFT JOIN 
    ab_num
ON
    t.ITEM_A = ab_num.ITEM_A
    AND t.item_b = ab_num.item_b
LEFT JOIN
    a_num AS a
ON
    t.ITEM_A = a.ITEM_A
LEFT JOIN
    a_num AS b
ON
    t.ITEM_b = b.ITEM_A
CROSS JOIN
    all_num
)
SELECT
    ITEM_A
    , ITEM_B
    , round(100*ab_num / all_num, 5) AS SUPPORT
    , round(100*ab_num / a_num, 5) AS  CONFIDENCE
    , round((ab_num / a_num) / (b_num/ all_num), 5) AS LIFT
FROM
    preprocess
WHERE
    ITEM_B IS NOT NULL 
ORDER BY
    LIFT DESC
    , SUPPORT DESC
    , CONFIDENCE DESC
    , ITEM_A DESC