SELECT 
    cl.first_name, cl.last_name, cl.email,
    MAX(os.date_created) as last_order,
    COUNT(os.order_id) as total_orders,
    SUM(os.total_sales) as total_spent,
    DATEDIFF(NOW(), MAX(os.date_created)) as days_since_last_order
FROM wp_wc_customer_lookup cl
JOIN wp_wc_order_stats os ON cl.customer_id = os.customer_id
WHERE os.status = wc-completed
GROUP BY cl.customer_id
HAVING days_since_last_order > 90
ORDER BY total_spent DESC;

SELECT 
    p1.post_title as product_a,
    p2.post_title as product_b,
    COUNT(*) as times_bought_together
FROM wp_wc_order_product_lookup ol1
JOIN wp_wc_order_product_lookup ol2 
    ON ol1.order_id = ol2.order_id 
    AND ol1.product_id < ol2.product_id
JOIN wp_posts p1 ON ol1.product_id = p1.ID
JOIN wp_posts p2 ON ol2.product_id = p2.ID
GROUP BY ol1.product_id, ol2.product_id
HAVING times_bought_together >= 3
ORDER BY times_bought_together DESC
LIMIT 20;

SELECT 
    DAYNAME(date_created) as day_of_week,
    COUNT(*) as orders,
    SUM(total_sales) as revenue,
    AVG(total_sales) as avg_order
FROM wp_wc_order_stats
WHERE status IN (wc-completed, wc-processing)
  AND date_created >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY DAYOFWEEK(date_created), DAYNAME(date_created)
ORDER BY DAYOFWEEK(date_created);

SELECT 
    DATE_FORMAT(first_order, %Y-%m) as cohort_month,
    COUNT(DISTINCT customer_id) as cohort_size,
    COUNT(DISTINCT CASE 
        WHEN repeat_order_date IS NOT NULL THEN customer_id 
    END) as returned,
    ROUND(COUNT(DISTINCT CASE 
        WHEN repeat_order_date IS NOT NULL THEN customer_id 
    END) * 100.0 / COUNT(DISTINCT customer_id), 1) as retention_rate
FROM (
    SELECT 
        os.customer_id,
        MIN(os.date_created) as first_order,
        (SELECT MIN(os2.date_created) 
         FROM wp_wc_order_stats os2 
         WHERE os2.customer_id = os.customer_id 
           AND os2.date_created > MIN(os.date_created)
           AND os2.status = wc-completed) as repeat_order_date
    FROM wp_wc_order_stats os
    WHERE os.status = wc-completed AND os.customer_id > 0
    GROUP BY os.customer_id
) cohort_data
GROUP BY cohort_month
ORDER BY cohort_month DESC;