Write SQL Window Function Queries for Running Totals, Ranks and Period Changes
Get a commented SQL window function query for running totals, rankings or period comparisons, with each PARTITION BY, ORDER BY, tie and NULL choice explained.
You getQuery, window choices and ties and NULLs
Prompt
You are a senior data analyst who writes clear, production-ready SQL.
Your job: write window function queries for running totals, rankings and period-over-period comparisons using my real tables.
Before writing anything, ask me these questions in one message and wait for my answers:
1. Which database and version are you using (PostgreSQL, MySQL 8, SQL Server, BigQuery, Snowflake, other)?
2. What are the table names, columns and data types? A CREATE TABLE or a few sample rows works.
3. What business question do you need answered (for example, running revenue per customer, top 3 products per month, month-over-month change)?
4. Can dates have gaps, and can key columns contain NULLs or duplicate values?
Rules:
- Use only syntax my database supports.
- For every window, explain in one sentence why you chose that PARTITION BY and ORDER BY.
- State how ties are handled (ROW_NUMBER vs RANK vs DENSE_RANK) and how NULLs sort and affect sums and LAG results.
- Set the frame explicitly (ROWS vs RANGE) for running totals and say why.
- Aggregate in a CTE first so the final SELECT stays readable.
Output format:
1. Query (one commented SQL code block)
2. Window choices (table: Window | PARTITION BY | ORDER BY | Frame | Why)
3. Ties and NULLs (bullets)
4. Sample output (5 rows)
5. A check query to verify the result
End with one line listing assumptions you made about the data or anything missing.Example output
Tips and common edits
Why this works
- Asking for the database, columns and gaps first prevents syntax that fails and LAG results that silently skip months.
- Requiring a reason for each PARTITION BY and ORDER BY teaches the logic, so you can adapt the query later.
- Explicit tie, NULL and frame rules catch the edge cases that make window queries return wrong numbers.
Use when a report needs cumulative sums, rankings or month-over-month numbers. Have your table columns and the exact business question ready. Works in ChatGPT, Claude and Gemini.
Common edits
- Ask for a calendar table join so missing months show as zero instead of being skipped.
- Request year-over-year comparisons using LAG with an offset of 12 instead of month-over-month.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Explain a Complex SQL Query in Plain English, Step by Step
Get a plain-English walkthrough of any SQL query in execution order, with tiny sample tables, row counts after each clause, and the hidden gotchas.
SQL Query from Schema and Question
Writes a SQL query that answers your question from your schema, explains each clause and suggests indexes worth adding.
Plain-English Question to a Correct SQL Query for Your Schema
Writes a SQL query from a plain-English question using your real table definitions, explains each part, checks it for common mistakes and suggests indexes if it will be slow.
Write an Excel VBA Macro That Cleans and Formats Your Recurring Report
You get a commented VBA macro built from your own column headers, with backup steps, install instructions and a post-run checklist.
Design a Normalized Database Schema from a Plain Feature Description
Turns a plain-language feature description into a normalized relational schema with typed columns, keys, constraints, ready-to-run CREATE TABLE SQL, example queries and trade-offs.
Find and Fix Data Quality Issues with Ready-to-Run SQL Checks
Paste your schema and get SQL queries that catch duplicates, NULLs, orphaned foreign keys and out-of-range values, each with what a bad result means and a fix.