Write complex SQL queries involving multiple joins, subqueries, and data aggregation logic.

Architectural Logic: Complex SQL = joins + CTEs + window functions + aggregation. Structure for readability and optimizer-friendliness. Example: Revenue by segment and category with YoY growth, excluding returns. WITH base AS (SELECT c.segment, p.category, EXTRACT(YEAR FROM o.order_date) AS year, SUM(o.quantity o.unit_price) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id LEFT JOIN returns r ON o.order_id = r.order_id WHERE r.return_id IS NULL GROUP BY c.segment, p.category, year), yoy AS (SELECT segment, category, year, revenue, (revenue - LAG(revenue) OVER (PARTITION BY segment, category ORDER BY year)) / NULLIF(LAG(revenue) OVER (PARTITION BY segment, category ORDER BY year), 0) 100 AS yoy_pct FROM base) SELECT * FROM yoy. Best practices: CTEs for clarity; avoid correlated subqueries (use JOINs or window); validate with EXPLAIN; test with edge cases (no returns, single year).