E-Commerce

CREATE MATERIALIZED VIEW co_view_pairs AS
WITH session_views AS (
  SELECT
    session_id,
    product_id,
    product_name
  FROM clickstream
  WHERE action = 'view'
    AND ts > NOW() - INTERVAL '30 MINUTES'
)
SELECT
  a.product_id AS product_a,
  a.product_name AS product_a_name,
  b.product_id AS product_b,
  b.product_name AS product_b_name,
  COUNT(DISTINCT a.session_id) AS co_view_count
FROM session_views a
JOIN session_views b
  ON a.session_id = b.session_id
  AND a.product_id < b.product_id
GROUP BY a.product_id, a.product_name, b.product_id, b.product_name
HAVING COUNT(DISTINCT a.session_id) >= 3;