Build Complex SQL Queries for BI and Analytics

This skill is an expert in designing complex SQL for business intelligence, data analysis, and advanced database operations - efficient, maintainable queries handling intricate business logic, multiple joins, analytical functions, and performance optimization across database systems. Core design principles: clarity and readability (self-documenting SQL, meaningful aliases, CTEs to decompose complex logic), performance first (execution plans, indexing, EXISTS vs IN, join order, temp tables), scalability (filtering strategy, avoiding unnecessary sorts, partitioning), and maintainability (parameterization, no hardcoded values, consistent naming).

Use it when a query needs advanced analytical logic - running totals, cohort retention, multi-dimensional aggregation - built for correctness and performance from the start, not just a working SELECT statement.

WITH sales_analysis AS (
    SELECT
        region,
        sales_date,
        revenue,
        SUM(revenue) OVER (
            PARTITION BY region
            ORDER BY sales_date
            ROWS UNBOUNDED PRECEDING
        ) AS running_total,
        RANK() OVER (
            PARTITION BY region, DATE_TRUNC('month', sales_date)
            ORDER BY revenue DESC
        ) AS monthly_rank
    FROM sales_data
    WHERE sales_date >= '2024-01-01'
)
SELECT * FROM sales_analysis;

Beyond window functions, it covers multi-level customer aggregation with percentile-based value tiering (PERCENTILE_CONT for P75/P25 average-order-value benchmarks) and lifecycle staging (At Risk / Active / Dormant by recency), optimized EXISTS-based filtering over large joined datasets with matching index recommendations, an incremental batch-processing pattern using generate_series to chunk large date ranges with a running cumulative total, a full customer cohort retention analysis (first-purchase month, activity by period number, retention rate per cohort), and a data-quality pattern that classifies and quarantines invalid rows (non-positive values, future dates, missing keys) before the main analysis runs only against validated records.

Performance guidance names database-specific features to reach for: LATERAL joins in PostgreSQL, APPLY operators in SQL Server, and optimizer hints in MySQL, alongside general practices like EXPLAIN ANALYZE, cursors or paginated retrieval for large result sets, and LIMIT clauses during development.

Data analysts and engineers writing BI or reporting SQL who need advanced, production-grade patterns - window functions, cohort analysis, incremental batch processing, and built-in data validation - rather than a basic multi-table JOIN. Maintenance guidance treats performance as an ongoing concern, not a one-time optimization pass: queries should be tested against realistic data volumes, complex business logic documented inline, and performance benchmarks maintained for critical reports so regressions are caught early rather than discovered in production as data volumes grow over time.