When Postgres Beat Machine Learning

-- Materialized view: products frequently bought together
CREATE MATERIALIZED VIEW product_recommendations AS
SELECT 
    p1.product_id as source_product,
    p2.product_id as recommended_product,
    COUNT(DISTINCT o.order_id) as co_purchase_count,
    AVG(p2.rating) as avg_rating,
    SUM(p2.revenue) as total_revenue
FROM order_items p1
JOIN order_items p2 
    ON p1.order_id = p2.order_id 
    AND p1.product_id != p2.product_id
JOIN products p2 ON p2.product_id = p2.product_id
JOIN orders o ON o.order_id = p1.order_id
WHERE o.created_at > NOW() - INTERVAL '90 days'
GROUP BY p1.product_id, p2.product_id
HAVING COUNT(DISTINCT o.order_id) >= 3;

-- Create indexes for fast lookups
CREATE INDEX idx_recommendations_source 
    ON product_recommendations(source_product, co_purchase_count DESC);

-- The query that powers recommendations (12ms avg)
SELECT 
    recommended_product,
    co_purchase_count,
    avg_rating
FROM product_recommendations
WHERE source_product = $1
ORDER BY 
    co_purchase_count DESC,
    avg_rating DESC
LIMIT 10;