Association Rule Lift Matrix with Directional Pair Evaluation

WITH total_orders AS
  (SELECT count(DISTINCT basket_id) AS total_baskets
   FROM market_baskets),
     item_frequencies AS
  (SELECT item_name,
          count(DISTINCT basket_id) AS item_baskets
   FROM market_baskets
   GROUP BY item_name),
     pair_occurrences AS
  (SELECT a.item_name AS item_a,
          b.item_name AS item_b,
          count(DISTINCT a.basket_id) AS pair_baskets
   FROM market_baskets a
   JOIN market_baskets b ON a.basket_id = b.basket_id
   AND a.item_name != b.item_name
   GROUP BY a.item_name,
            b.item_name)
SELECT p.item_a,
       p.item_b,
       round(p.pair_baskets * 1.0 / t.total_baskets, 4) AS support,
       round(p.pair_baskets * 1.0 / fa.item_baskets, 4) AS confidence,
       round((p.pair_baskets * 1.0 * t.total_baskets) / (fa.item_baskets * fb.item_baskets), 4) AS lift
FROM pair_occurrences p
JOIN item_frequencies fa ON p.item_a = fa.item_name
JOIN item_frequencies fb ON p.item_b = fb.item_name
CROSS JOIN total_orders t
WHERE p.pair_baskets >= 2
ORDER BY lift DESC,
         p.item_a ASC,
         p.item_b ASC;

CREATE TABLE market_baskets (basket_id INT, item_name VARCHAR);


INSERT INTO market_baskets
VALUES (1, 'Bread'),
       (1, 'Butter'),
       (1, 'Milk'),
       (2, 'Bread'),
       (2, 'Butter'),
       (3, 'Bread'),
       (3, 'Milk'),
       (4, 'Beer'),
       (4, 'Diapers');