Window Function Builder

The window-function-builder skill constructs SQL window functions to perform complex aggregations and calculations across ordered result sets. It enables AI agents to generate queries that calculate running totals, moving averages, and rank-based metrics without collapsing rows into a single summary line.

When to use it

• Generating cumulative sums or running balances for financial ledgers.
• Calculating rolling averages over time-series data like stock prices or sensor readings.
• Ranking records within specific partitions, such as finding the top 3 salespeople per region.
• Creating lagged or lead values to compare current metrics against previous periods.

Key capabilities

• Constructs OVER clauses to define window frames and partitioning logic.
• Supports standard window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE().
• Handles aggregate functions within windows, such as SUM() OVER, AVG() OVER, and COUNT() OVER.
• Allows specification of frame boundaries using ROWS BETWEEN or RANGE BETWEEN clauses.

Example prompts

• "Write a SQL query to calculate the 7-day moving average of daily sales for each product category."
• "Generate a query that assigns a unique row number to every transaction within each customer's order history."
• "Create a statement that ranks employees by salary within their respective departments using dense ranking."

Tips & gotchas

Ensure your SQL dialect supports the specific window functions you request, as syntax can vary between PostgreSQL, MySQL, and SQL Server. When defining partitions, be explicit about the grouping columns to avoid unintended global calculations across the entire dataset.