Bits of .NET

📝 Name Your Subqueries

Subqueries inside subqueries are unreadable. CTEs (WITH clause) give subqueries names. Your future self will thank you.

📝 Without CTE (Unreadable)

SELECT name, total_orders
FROM (
  SELECT u.name, COUNT(o.id) as total_orders
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY u.id, u.name
) order_stats
WHERE total_orders > 5
ORDER BY total_orders DESC;

✅ With CTE (Readable)

WITH order_stats AS (
  SELECT u.name, COUNT(o.id) as total_orders
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY u.id, u.name
)
SELECT name, total_orders
FROM order_stats
WHERE total_orders > 5
ORDER BY total_orders DESC;

✅ Multiple CTEs

WITH 
recent_users AS (
  SELECT * FROM users WHERE created_at > NOW() - INTERVAL '30 days'
),
active_orders AS (
  SELECT * FROM orders WHERE status = 'completed'
),
user_totals AS (
  SELECT u.id, u.name, SUM(o.total) as revenue
  FROM recent_users u
  JOIN active_orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
)
SELECT name, revenue
FROM user_totals
WHERE revenue > 1000
ORDER BY revenue DESC;