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

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

At a glanceEast region running total by monthFrom the sample output: running_total for East, 2024
  • 2024-01: 12400
  • 2024-02: 27500
  • 2024-03: 41400

Example for: PostgreSQL 15, an orders table (order_id, region, order_date, amount numeric, amount can be NULL for cancelled orders), question: monthly revenue per region with running total, rank and month-over-month change.

1. Query
WITH monthly AS (
  -- One row per region per month; NULL amounts count as 0
  SELECT region,
         date_trunc('month', order_date)::date AS month,
         SUM(COALESCE(amount, 0)) AS revenue
  FROM orders
  GROUP BY region, date_trunc('month', order_date)
)
SELECT region,
       month,
       revenue,
       SUM(revenue) OVER (PARTITION BY region ORDER BY month
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       DENSE_RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS region_rank,
       revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS mom_change
FROM monthly
ORDER BY region, month;
2. Window choices
WindowPARTITION BYORDER BYFrameWhy
running_totalregionmonthROWS unbounded to currentEach region accumulates on its own timeline; ROWS avoids surprises if months ever repeat
region_rankmonthrevenue DESCn/aCompares regions within the same month
mom_changeregionmonthn/aLAG needs the previous month of the same region
3. Ties and NULLs
  • DENSE_RANK gives two regions with equal revenue the same rank and does not skip the next number (1, 1, 2).
  • NULL amounts become 0 in the CTE, so sums never return NULL.
  • The first month per region has no prior row, so mom_change is NULL by design.
  • Missing months are not filled: LAG compares to the last month with orders, not the calendar month before.
4. Sample output
regionmonthrevenuerunning_totalregion_rankmom_change
East2024-01-0112400124002NULL
East2024-02-01151002750012700
East2024-03-0113900414002-1200
West2024-01-0114800148001NULL
West2024-02-0115100299001300
5. Check query
SELECT region, SUM(COALESCE(amount, 0)) AS total
FROM orders GROUP BY region;
-- Should match the last running_total per region

Assumptions: order_date has no time zone issues, and gaps between months should not be filled with zero rows.

Written by AI from this prompt. Your results will vary.

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 in Coding