Build Advanced SQL Window Functions

This skill covers SQL window functions - analytical query patterns, performance optimization, and cross-platform compatibility for ranking, aggregation, offset operations, and complex business calculations across PostgreSQL, SQL Server, Oracle, MySQL, BigQuery, Snowflake, and other databases. Essential syntax: FUNCTION() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...), where the partition clause defines calculation groups, the order clause determines row sequence, and the frame clause specifies which rows are included - always accounting for NULL handling and edge cases. Function categories: ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), aggregate (SUM/AVG/COUNT/MIN/MAX with frames), offset (LAG, LEAD, FIRST_VALUE, LAST_VALUE), and statistical (PERCENT_RANK, CUME_DIST, PERCENTILE_CONT).

Ranking and sequential analysis is shown via multi-level ranking with ties handling:

-- Multi-level ranking with ties handling
SELECT 
    product_id,
    category,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) as row_num,
    RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) as rank_with_gaps,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) as dense_rank,
    NTILE(4) OVER (PARTITION BY category ORDER BY sales_amount DESC) as quartile
FROM sales_data;

and a top-N-per-group pattern combining ROW_NUMBER with PERCENT_RANK in a CTE to filter to the top 5 and top 10th percentile per region. Running calculations cover running totals (SUM() OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)), a 7-day moving average (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), a forward-looking 3-day sum, and a year-over-year comparison using LAG(revenue, 12) with a null-safe percentage-change calculation via NULLIF.

Advanced frame specifications cover range-based time windows (RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW for a rolling 30-day average) and first/last-value extraction across an entire partition (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Complex business calculations are demonstrated via a customer-lifecycle-analysis query combining order sequence, previous-order-date lag, and a running lifetime-value sum to classify each order as First Order, Reactivated (a gap over 365 days), or Repeat.

Performance optimization covers indexing strategy (composite indexes on PARTITION BY/ORDER BY columns, covering indexes, monitoring execution plans for sort operations) and reusing a single windowed CTE for multiple derived columns rather than repeating the window clause. Cross-platform considerations note PostgreSQL's full standard compliance, SQL Server's OFFSET/FETCH and WITHIN GROUP, BigQuery and Snowflake's QUALIFY clause for filtering window results, and MySQL 8.0+'s more limited frame support - with worked examples of BigQuery's QUALIFY syntax and SQL Server's WITHIN GROUP for ordered string aggregation.

Best practices: partition appropriately to avoid oversized window operations, specify explicit frame clauses with aggregates, handle NULLs with COALESCE/ISNULL, test edge cases like empty partitions and single rows, use CTEs for readability, profile and adjust partitioning strategy, leverage database-specific optimizations like QUALIFY, and document business logic behind complex frame specifications.

Use it when writing or optimizing SQL window-function queries - ranking, running totals, moving averages, year-over-year comparisons, or database-specific patterns like QUALIFY. It is not a general SQL-tuning guide beyond window functions, and it assumes familiarity with basic SQL joins and aggregation.

Given an analytical requirement - for example, top 5 products per category, or a 7-day moving average - it produces the window-function SQL for the target database, including the correct partition/order/frame clause and any database-specific syntax like QUALIFY or WITHIN GROUP.

Covers PostgreSQL, SQL Server, Oracle, MySQL 8.0+, BigQuery, and Snowflake, noting each platform's specific window-function support and syntax differences.

Data analysts and engineers writing analytical SQL queries that need ranking, running calculations, or complex windowed business logic.