---
name: sql-window-functions-helper
description: "The right window function query for a running total, rank, previous-row comparison, or moving average over your table, with the PARTITION BY, ORDER BY, and frame choices explained so you can adapt it. Use when a query needs a running total, rank, previous-row comparison, or moving average per group, built with the correct window function and frame. Do not use for plain aggregates with GROUP BY, or tuning an existing query's performance. Related topics: coding, database, sql."
---

# SQL Window Functions Helper

The right window function query for a running total, rank, previous-row comparison, or moving average over your table, with the PARTITION BY, ORDER BY, and frame choices explained so you can adapt it.

## When to use

Use this skill when a query needs a running total, rank, previous-row comparison, or moving average per group, built with the correct window function and frame. Do not use it for plain aggregates with GROUP BY, or tuning an existing query's performance.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Pick the window function the operation calls for and state in one line why it beats the alternatives (tie handling, defaults).
3. Write the frame clause explicitly, using ROWS for running totals and moving averages so equal order values do not merge.
4. Build the query with the given partition and order columns, selecting the source columns beside the computed one.
5. Note the dialect caveats that apply to the chosen database.
6. Add the variation that filters on the window result through an outer query or QUALIFY.

## Inputs

- **Table name** (free text, e.g. "employee_salaries")
- **Operation** (choose one: Running total, Rank/Denserank, Lead/Lag comparison, Moving average)
- **Partition column** (free text, e.g. "department_id")
- **Order column** (free text, e.g. "salary")
- **Value column** (free text, e.g. "the column to sum or average or compare")
- **Database** (choose one: PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite, BigQuery)

## Output

A sql code block with the query, a two-line explanation of the function and frame, and a second code block showing how to filter on the computed column.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### SQL Window Function Query

Write a window function query.

- Table name: {{Table name}}
- Operation: {{Operation}}
- Partition column: {{Partition column}}
- Order column: {{Order column}}
- Value column: {{Value column}}
- Database: {{Database}}

Choose the function the operation calls for and explain the choice in a line: SUM with a frame for a running total, RANK versus DENSE_RANK versus ROW_NUMBER by how ties should behave, LAG and LEAD with an explicit default for the first and last row, AVG with a ROWS BETWEEN frame for a moving average. State the frame clause explicitly even when it is the default, because the default RANGE frame includes peer rows with equal order values and surprises people on running totals. Write the query with the partition and order columns given, select the source columns next to the computed one so the result is checkable, and order the final output the same way as the window. Note the dialect differences that matter (MySQL before 8 has no window functions, SQLite needs 3.25 or later, BigQuery frames in rows). Then show one variation: how to filter on the window result with an outer query or QUALIFY, since WHERE cannot reference it.

Return the query in a sql code block, a two-line explanation of the function and frame chosen, and the filter variation in a second code block.

## Pitfalls

- Leaving the default RANGE frame on a running total, which sums all rows that tie on the order column at once.
- Filtering on a window column in WHERE, which fails because window functions are evaluated after WHERE.
- Using ROW_NUMBER for a leaderboard, which breaks ties arbitrarily where RANK would show the tie.
