WITH
MEMBER_MASTER AS (
    SELECT
        DISTINCT MEMBER_CODE
    FROM
        PURCHASE_HISTORY
),
ITEM_MASTER AS (
    SELECT
        DISTINCT ITEM_CODE
    FROM
        PURCHASE_HISTORY
),
COMBINATION AS (
    SELECT
        ITEM_A.ITEM_CODE AS ITEM_A,
        ITEM_B.ITEM_CODE AS ITEM_B
    FROM
        ITEM_MASTER AS ITEM_A
    CROSS JOIN
        ITEM_MASTER AS ITEM_B
    WHERE
        ITEM_A.ITEM_CODE <> ITEM_B.ITEM_CODE
)
SELECT
    ITEM_A,
    ITEM_B,
    ROUND(CAST(COUNT(HISTORY_A.MEMBER_CODE) AS FLOAT) /
    (
        SELECT
            COUNT(*)
        FROM
            MEMBER_MASTER
    ) * 100, 5) AS SUPPORT,
    ROUND(CAST(COUNT(HISTORY_A.MEMBER_CODE) AS FLOAT) /
    (
        SELECT
            COUNT(*)
        FROM
            MEMBER_MASTER
        INNER JOIN
            PURCHASE_HISTORY ON
            PURCHASE_HISTORY.MEMBER_CODE = MEMBER_MASTER.MEMBER_CODE AND
            PURCHASE_HISTORY.ITEM_CODE = ITEM_A
    ) * 100, 5) AS CONFIDENCE,
    ROUND(CAST(COUNT(HISTORY_A.MEMBER_CODE) AS FLOAT) /
    (
        SELECT
            COUNT(*)
        FROM
            MEMBER_MASTER
        INNER JOIN
            PURCHASE_HISTORY ON
            PURCHASE_HISTORY.MEMBER_CODE = MEMBER_MASTER.MEMBER_CODE AND
            PURCHASE_HISTORY.ITEM_CODE = ITEM_A
    ) /
    (
        SELECT
            COUNT(*)
        FROM
            MEMBER_MASTER
        INNER JOIN
            PURCHASE_HISTORY ON
            PURCHASE_HISTORY.MEMBER_CODE = MEMBER_MASTER.MEMBER_CODE AND
            PURCHASE_HISTORY.ITEM_CODE = ITEM_B
    ) * 
    (
        SELECT
            COUNT(*)
        FROM
            MEMBER_MASTER
    ), 5) AS LIFT
FROM
    COMBINATION
INNER JOIN
    PURCHASE_HISTORY AS
    HISTORY_A ON
    HISTORY_A.ITEM_CODE = COMBINATION.ITEM_A
INNER JOIN
    PURCHASE_HISTORY AS
    HISTORY_B ON
    HISTORY_B.ITEM_CODE = COMBINATION.ITEM_B
WHERE
    HISTORY_A.MEMBER_CODE = HISTORY_B.MEMBER_CODE
GROUP BY
    ITEM_A, ITEM_B
ORDER BY
    LIFT DESC,
    SUPPORT DESC,
    CONFIDENCE DESC,
    ITEM_A DESC