rag/sample_templates/12_self_join.sql

-- Template: Self-Join for Hierarchical or Sequential Data
-- Pattern: Join table to itself to find relationships within same table
-- Use Case: Manager-employee, sequential orders, referrals, product recommendations

-- Example 1: Find customers who ordered same product
SELECT DISTINCT
    c1.customer_id as customer1,
    c1.name as customer1_name,
    c2.customer_id as customer2,
    c2.name as customer2_name,
    oi1.product_id,
    p.name as product_name
FROM order_items oi1
JOIN order_items oi2 ON oi1.product_id = oi2.product_id 
    AND oi1.order_id < oi2.order_id  -- Avoid duplicates
JOIN orders o1 ON oi1.order_id = o1.order_id
JOIN orders o2 ON oi2.order_id = o2.order_id
JOIN customers c1 ON o1.customer_id = c1.customer_id
JOIN customers c2 ON o2.customer_id = c2.customer_id
JOIN products p ON oi1.product_id = p.product_id
WHERE c1.customer_id != c2.customer_id;

-- Example 2: Find sequential orders by same customer
SELECT 
    c.name,
    o1.order_id as first_order,
    o1.order_date as first_date,
    o2.order_id as next_order,
    o2.order_date as next_date,
    o2.order_date - o1.order_date as days_between
FROM orders o1
JOIN orders o2 ON o1.customer_id = o2.customer_id 
    AND o2.order_date > o1.order_date
JOIN customers c ON o1.customer_id = c.customer_id
ORDER BY c.name, o1.order_date;

-- Key Concepts:
-- - Join table to itself with different aliases
-- - Use inequality (< or !=) to avoid matching same row
-- - Useful for finding patterns, sequences, or relationships
-- - Can be combined with window functions for "next" or "previous" record