Real-World Scenario: E-commerce Analytics

SELECT c.customer_name,
       COUNT(o.id) AS total_orders,
       SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spent DESC
LIMIT 5;

Classic customer value query. Add WHERE o.status = 'completed' if you only want completed orders. This drives loyalty programs and VIP customer identification.

SELECT MONTH(created_at) AS month,
       COUNT(*) AS new_customers
FROM customers
WHERE YEAR(created_at) = YEAR(CURDATE())
GROUP BY MONTH(created_at)
ORDER BY new_customers DESC
LIMIT 1;

Acquisition trend analysis. The LIMIT 1 gives just the peak month. Remove LIMIT to see all months — useful for seasonal pattern analysis.

SELECT
  COUNT(DISTINCT CASE WHEN order_count > 1 THEN customer_id END) AS repeat_customers,
  COUNT(DISTINCT customer_id) AS total_customers,
  ROUND(
    100.0 * COUNT(DISTINCT CASE WHEN order_count > 1 THEN customer_id END)
    / COUNT(DISTINCT customer_id), 2
  ) AS repeat_rate_pct
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) order_counts;

Repeat purchase rate = repeat buyers / total buyers * 100. Key e-commerce metric. The subquery counts orders per customer, outer query calculates the rate.

SELECT
  a.product_id AS product_a,
  b.product_id AS product_b,
  COUNT(*) AS times_together
FROM order_items a
JOIN order_items b
  ON a.order_id = b.order_id
  AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY times_together DESC
LIMIT 10;

Market basket analysis using a SELF JOIN on order_items. a.product_id < b.product_id ensures each pair is counted once (not twice). Drives "frequently bought together" recommendations.

SELECT order_date,
       COUNT(*) AS daily_orders,
       AVG(COUNT(*)) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7d_avg
FROM orders
GROUP BY order_date
ORDER BY order_date;

Nested window function — AVG over COUNT. First GROUP BY aggregates to daily level, then window function calculates rolling average. The 7-day rolling avg smooths daily volatility in KPI dashboards.