SQL Window Functions

Creates advanced SQL queries using window functions for analytics, reporting, and data transformation. Covers ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), offset functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE), aggregate windows (running totals, moving averages), frame specifications (ROWS vs RANGE), PARTITION BY strategies, and combining multiple window functions in complex analytical queries.

Usage

Describe the analytical calculation you need to perform, the table structure, and your database engine (PostgreSQL, MySQL 8+, SQL Server, BigQuery). Provide sample data if possible and describe the expected output. The skill generates optimized SQL with window functions and explains the frame specification logic.

Examples

• "Calculate a 7-day moving average of daily revenue and compare each day to the same day last week"
• "Rank customers by total purchase amount within each region, showing top 3 per region"
• "Compute running total of inventory changes and flag when stock drops below reorder point"
• "Find the time gap between consecutive user actions and flag sessions with gaps over 30 minutes"

Guidelines

• Use ROW_NUMBER for unique row numbering, RANK for ties-aware ranking, DENSE_RANK for gap-free ranking
• Specify window frames explicitly (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) instead of relying on defaults
• Use ROWS for fixed-offset windows (last 7 rows) and RANGE for value-based windows (last 7 days)
• Define named windows with WINDOW clause when multiple columns use the same PARTITION BY and ORDER BY
• LAG(column, 1) looks back one row, LEAD(column, 1) looks ahead — both require ORDER BY in the window
• Use FILTER (WHERE ...) clause with aggregate window functions in PostgreSQL for conditional aggregation
• Combine window functions with CTEs for multi-step analytics instead of deeply nested subqueries
• Be aware of NULL handling in window frames: IGNORE NULLS option available in FIRST_VALUE/LAST_VALUE