Build Complex SQL Queries for BI and Analytics

Complex SQL Query Builder Agent designs, optimizes, and debugs complex SQL for business intelligence: window-function analytics such as running totals, ranking, and period-over-period comparison, multi-dimensional aggregation with segment benchmarking, join and subquery optimization using EXISTS over IN, incremental batch processing over date ranges, cohort retention analysis, and data-quality validation queries.

Use it when writing a business-intelligence query that goes beyond a simple SELECT - computing running totals and rankings with window functions, segmenting customers against percentile benchmarks, replacing a slow IN subquery with EXISTS, processing a large date range in monthly batches, building a cohort retention table, or validating raw data before aggregating it. It is not a database-design or schema-migration tool - it assumes tables and indexes already exist, though it does recommend specific indexes to support a given query pattern.

Input is the business question and the relevant tables and columns. Output is SQL, primarily PostgreSQL-flavored CTEs: a running-total and monthly-rank window-function query with period-over-period growth-rate calculation; a multi-CTE customer-metrics query joining orders, computing recent vs. lifetime revenue, and classifying customers into value tiers via PERCENTILE_CONT benchmarks and lifecycle stages such as At Risk, Active, or Dormant; an EXISTS-based product query replacing an IN subquery for performance, with matching index recommendations; an incremental batch-processing pattern using generate_series to split a date range into monthly batches with a cumulative running total; a full cohort-retention analysis computing each cohort's month-over-month active customers and retention rate; and a data-validation CTE that flags non-positive values, future dates, and missing customer IDs before running the main aggregation only on valid rows.

Targets SQL databases with CTE and window-function support, primarily PostgreSQL syntax such as generate_series and PERCENTILE_CONT, with notes on database-specific optimizations - LATERAL joins in PostgreSQL, APPLY in SQL Server, optimizer hints in MySQL - and recommends EXPLAIN ANALYZE for execution-plan review:

-- Optimized approach using EXISTS instead of IN for large datasets
SELECT p.product_id, p.product_name, p.category
FROM products p
WHERE EXISTS (
    SELECT 1 
    FROM order_items oi 
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id 
    AND o.order_date >= '2024-01-01'
    AND o.status = 'completed'
)
AND p.status = 'active';

Analysts and data engineers writing BI queries who need both correctness and performance - self-documenting CTEs with clear aliases, EXISTS over IN on large tables, supporting indexes, LIMIT-bounded development queries, and documented business logic - and who validate data quality before aggregating rather than trusting raw input.