Bits of .NET

🔗 SQL Joins = Data Intelligence

Data is the new oil, but it’s messy. Mastering SQL joins allows you to extract meaningful insights from your data, no matter how complex the relationships.

📝 Analytical Query Patterns

-- Customer Lifetime Value Analysis
WITH customer_orders AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.email,
        COUNT(o.order_id) as order_count,
        SUM(o.total_amount) as total_spent,
        AVG(o.total_amount) as avg_order_value,
        MAX(o.order_date) as last_order_date,
        MIN(o.order_date) as first_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name, c.email
),
customer_segments AS (
    SELECT 
        *,
        CASE 
            WHEN total_spent > 1000 AND order_count > 10 THEN 'Premium'
            WHEN total_spent > 500 AND order_count > 5 THEN 'Gold'
            WHEN total_spent > 100 AND order_count > 2 THEN 'Silver'
            ELSE 'Bronze'
        END as segment
    FROM customer_orders
)
SELECT 
    segment,
    COUNT(*) as customer_count,
    AVG(total_spent) as avg_lifetime_value,
    AVG(order_count) as avg_orders,
    SUM(total_spent) as total_revenue
FROM customer_segments
GROUP BY segment
ORDER BY total_revenue DESC;

-- Product Affinity Analysis (Market Basket)
SELECT 
    p1.product_name AS product_a,
    p2.product_name AS product_b,
    COUNT(DISTINCT oi1.order_id) AS times_bought_together,
    COUNT(DISTINCT oi1.order_id) * 100.0 / (
        SELECT COUNT(DISTINCT order_id) 
        FROM order_items 
        WHERE product_id = p1.product_id
    ) AS affinity_percentage
FROM order_items oi1
JOIN order_items oi2 
    ON oi1.order_id = oi2.order_id 
    AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.product_id
JOIN products p2 ON oi2.product_id = p2.product_id
GROUP BY p1.product_name, p2.product_name
HAVING COUNT(DISTINCT oi1.order_id) >= 10
ORDER BY times_bought_together DESC
LIMIT 20;

🎯 Advanced Join Techniques

-- Self Join with Date Comparison (Find Repeat Customers)
SELECT 
    a.customer_id,
    a.order_date as first_order,
    b.order_date as second_order,
    DATEDIFF(b.order_date, a.order_date) as days_between
FROM orders a
JOIN orders b 
    ON a.customer_id = b.customer_id 
    AND a.order_date < b.order_date
    AND NOT EXISTS (
        SELECT 1 
        FROM orders c 
        WHERE c.customer_id = a.customer_id 
        AND c.order_date BETWEEN a.order_date AND b.order_date
        AND c.order_id NOT IN (a.order_id, b.order_id)
    )
WHERE DATEDIFF(b.order_date, a.order_date) <= 30;

-- Complex Multi-Join with Aggregates
SELECT 
    DATE_TRUNC('month', o.order_date) as month,
    p.category,
    COUNT(DISTINCT o.customer_id) as unique_customers,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as revenue,
    SUM(o.total_amount) / NULLIF(COUNT(o.order_id), 0) as avg_order_value,
    AVG(oi.discount_applied) as avg_discount,
    SUM(oi.quantity) as total_units_sold
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status != 'cancelled'
GROUP BY DATE_TRUNC('month', o.order_date), p.category
HAVING SUM(o.total_amount) > 1000
ORDER BY month DESC, revenue DESC;

-- Window Functions with Joins (Time-Series Analysis)
SELECT 
    o.customer_id,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (
        PARTITION BY o.customer_id 
        ORDER BY o.order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total,
    RANK() OVER (
        PARTITION BY DATE_TRUNC('month', o.order_date)
        ORDER BY o.total_amount DESC
    ) as monthly_order_rank
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

💡 Join Performance Optimization

• Create indexes on foreign key columns for faster JOIN operations
• Use INNER JOIN when you only need matching records
• Use LEFT JOIN when you need all records from the left table
• Consider materialized views for complex analytical queries
• Analyze execution plans to identify performance bottlenecks