-- TEMPLATE: Self-Join (two aliases for same table)

-- Pattern A: Hierarchy (employee → manager)
SELECT
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id   -- join condition defines relationship
WHERE e.salary > m.salary;                          -- filter condition

-- Pattern B: Finding pairs (avoid duplicates with < operator)
SELECT
    t1.id AS id1,
    t2.id AS id2,
    t1.col AS col1,
    t2.col AS col2
FROM table t1
JOIN table t2
    ON t1.shared_key = t2.shared_key    -- what links them
    AND t1.id < t2.id                   -- prevent (A,B) AND (B,A), prevent self-pairs
WHERE ABS(t1.value - t2.value) <= 5;   -- similarity condition

SELECT
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id    -- link employee to their manager
WHERE e.salary > m.salary              -- filter: only employees earning more
ORDER BY e.salary DESC;

-- ⚠️ TRAP: Don't forget to handle NULLs!
--   Top-level managers have manager_id = NULL → JOIN filters them out (correct!)
--   Use INNER JOIN → rows without a manager are excluded automatically

WITH private_rsvp AS (
    -- Step 1: Only private events
    SELECT user_id, event_id
    FROM event_rsvp
    WHERE event_type = 'private'
),

shared_events AS (
    -- Step 2: Self-join to find users who attended same events
    SELECT
        r1.user_id AS user_1,
        r2.user_id AS user_2,
        COUNT(r1.event_id) AS shared_events
    FROM private_rsvp r1
    JOIN private_rsvp r2
        ON r1.event_id = r2.event_id      -- same event
        AND r1.user_id < r2.user_id        -- avoid (A,B) and (B,A), avoid self-pairs
    GROUP BY r1.user_id, r2.user_id
    HAVING COUNT(r1.event_id) >= 2         -- attended 2+ same events
),

existing_friends AS (
    -- Step 3: Normalize friendships table (ensure both directions covered)
    SELECT user_id_1 AS u1, user_id_2 AS u2 FROM friendships
    UNION
    SELECT user_id_2, user_id_1 FROM friendships
)

-- Step 4: Exclude pairs who are already friends
SELECT s.user_1, s.user_2, s.shared_events
FROM shared_events s
LEFT JOIN existing_friends f
    ON s.user_1 = f.u1 AND s.user_2 = f.u2
WHERE f.u1 IS NULL;   -- not already friends

-- TEMPLATE A: Simple combination generation
SELECT
    a.col1,
    b.col2
FROM table_a a
CROSS JOIN table_b b;   -- generates every row of a paired with every row of b

-- TEMPLATE B: Self-cross-join for pairs (e.g., tournament matchups)
SELECT
    t1.team_name AS home_team,
    t2.team_name AS away_team
FROM teams t1
CROSS JOIN teams t2
WHERE t1.team_id < t2.team_id;   -- prevents (A vs B) AND (B vs A), prevents self-matches

-- TEMPLATE C: Fill gaps — generate full grid then LEFT JOIN to actuals
WITH all_dates AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-12-31'::date,
        '1 day'::interval
    )::date AS date_val
    -- Hive/Spark: Use recursive CTE or pre-built date table
),
all_products AS (SELECT DISTINCT product_id FROM products)

SELECT
    d.date_val,
    p.product_id,
    COALESCE(s.revenue, 0) AS revenue   -- 0 for days with no sales
FROM all_dates d
CROSS JOIN all_products p
LEFT JOIN daily_sales s
    ON d.date_val = s.sale_date
    AND p.product_id = s.product_id;

SELECT
    t1.topping_name AS topping_1,
    t2.topping_name AS topping_2,
    t3.topping_name AS topping_3,
    t1.ingredient_cost + t2.ingredient_cost + t3.ingredient_cost AS total_cost
FROM toppings t1
JOIN toppings t2 ON t1.topping_name < t2.topping_name   -- ensures t1 < t2 < t3 alphabetically
JOIN toppings t3 ON t2.topping_name < t3.topping_name   -- avoids all duplicate combos
ORDER BY total_cost, topping_1, topping_2, topping_3;

-- ⚠️ Using < on the name column (lexicographic order) ensures:
--   (Basil, Mushroom, Peppers) appears but NOT (Mushroom, Basil, Peppers)
--   This gives exactly C(n,3) = n!/3!(n-3)! unique combinations

-- TEMPLATE: Market Basket — find co-occurring item pairs

WITH item_pairs AS (
    SELECT
        o1.product_id AS product_1,
        o2.product_id AS product_2,
        COUNT(DISTINCT o1.order_id) AS co_purchase_count
    FROM order_items o1
    JOIN order_items o2
        ON o1.order_id = o2.order_id        -- same order
        AND o1.product_id < o2.product_id    -- avoid (A,B) AND (B,A), avoid (A,A)
    GROUP BY o1.product_id, o2.product_id
    ORDER BY co_purchase_count DESC
)

SELECT
    p1.product_name AS product_1,
    p2.product_name AS product_2,
    ip.co_purchase_count
FROM item_pairs ip
JOIN products p1 ON ip.product_1 = p1.product_id
JOIN products p2 ON ip.product_2 = p2.product_id
ORDER BY co_purchase_count DESC
LIMIT 5;   -- top 5 pairs

WITH product_pairs AS (
    SELECT
        t1.product_id AS product_1_id,
        t2.product_id AS product_2_id,
        COUNT(DISTINCT t1.order_id) AS times_bought_together
    FROM transactions t1
    JOIN transactions t2
        ON t1.order_id = t2.order_id        -- same order
        AND t1.product_id < t2.product_id    -- canonical pair, no duplicates
    GROUP BY t1.product_id, t2.product_id
)

SELECT
    p1.product_name AS product_1,
    p2.product_name AS product_2,
    pp.times_bought_together
FROM product_pairs pp
JOIN products p1 ON pp.product_1_id = p1.product_id
JOIN products p2 ON pp.product_2_id = p2.product_id
ORDER BY pp.times_bought_together DESC
LIMIT 5;

WITH total_orders AS (
    SELECT COUNT(DISTINCT order_id) AS total FROM orders
),

product_freq AS (
    -- How often does each product appear in ANY order?
    SELECT product_id, COUNT(DISTINCT order_id) AS freq
    FROM orders
    GROUP BY product_id
),

pair_freq AS (
    -- How often do pairs appear in the SAME order?
    SELECT
        o1.product_id AS prod_1,
        o2.product_id AS prod_2,
        COUNT(DISTINCT o1.order_id) AS pair_count
    FROM orders o1
    JOIN orders o2
        ON o1.order_id = o2.order_id
        AND o1.product_id < o2.product_id
    GROUP BY o1.product_id, o2.product_id
    HAVING COUNT(DISTINCT o1.order_id) > 100
)

SELECT
    pf.prod_1,
    pf.prod_2,
    pf.pair_count,
    -- LIFT = (pair_count / total) / ((freq_A / total) × (freq_B / total))
    --      = pair_count × total / (freq_A × freq_B)
    ROUND(
        pf.pair_count * t.total * 1.0 / (f1.freq * f2.freq),
    3) AS lift
FROM pair_freq pf
JOIN product_freq f1 ON pf.prod_1 = f1.product_id
JOIN product_freq f2 ON pf.prod_2 = f2.product_id
CROSS JOIN total_orders t
ORDER BY lift DESC;

-- Lift interpretation:
-- > 1.0: products bought together MORE than expected → strong association
-- = 1.0: products bought together exactly as expected → no association
-- < 1.0: products bought together LESS than expected → negative association