WITH T0 AS(
    SELECT
        COUNT(DISTINCT MEMBER_CODE) AS AMT
    FROM
        PURCHASE_HISTORY
), T1 AS(
    SELECT
        ITEM_CODE,
        COUNT(DISTINCT MEMBER_CODE) AS AMT
    FROM
        PURCHASE_HISTORY
    GROUP BY
        ITEM_CODE
), T2 AS(
    SELECT
        T_A.ITEM_CODE AS ITEM_A,
        T_B.ITEM_CODE AS ITEM_B,
        COUNT(DISTINCT T_A.MEMBER_CODE) AS AMT
    FROM
        PURCHASE_HISTORY AS T_A
    CROSS JOIN
        PURCHASE_HISTORY AS T_B
    WHERE
        ITEM_A != ITEM_B
        AND T_A.MEMBER_CODE = T_B.MEMBER_CODE
    GROUP BY
        ITEM_A,
        ITEM_B
), T3 AS(
    SELECT
        T1_A.ITEM_CODE AS ITEM_A,
        T1_B.ITEM_CODE AS ITEM_B,
        T1_A.AMT AS AMT_A,
        T1_B.AMT AS AMT_B
    FROM
        T1 AS T1_A
    CROSS JOIN
        T1 AS T1_B
), T4 AS(
    SELECT
        T3.ITEM_A,
        T3.ITEM_B,
        T0.AMT AS AMT,
        T3.AMT_A AS AMT_A,
        T3.AMT_B AS AMT_B,
        CASE
            WHEN T2.AMT IS NULL THEN 0
            ELSE T2.AMT
        END AS AMT_AB
    FROM
        T3
    CROSS JOIN
        T0
    INNER JOIN
        T2 ON
            T2.ITEM_A = T3.ITEM_A
            AND T2.ITEM_B = T3.ITEM_B
), T5 AS(
    SELECT
        ITEM_A,
        ITEM_B,
        ROUND(
            100.0 * AMT_AB / AMT, 6
        ) AS SUPPORT,
        ROUND(
            100.0 * AMT_AB / AMT_A, 6
        ) AS CONFIDENSE,
        ROUND(
            (1.0 * AMT_AB / AMT_A) / (1.0 * AMT_B / AMT), 6
        ) AS LIFT
    FROM
        T4
    ORDER BY
        LIFT DESC,
        SUPPORT DESC,
        CONFIDENSE DESC,
        ITEM_A ASC
)
SELECT
    *
    FROM
        T5