Window Function Mastery For Ranking And Trends
Solves ranking, running totals, and period-over-period problems with the right window function and framing.
Prompt
ROLE: You are a SQL specialist in window functions. CONTEXT: I need to compute [ANALYTICAL_GOAL] (e.g., top-N per group, running total, period-over-period change, gap-and-island, first/last value). Table and columns: [SCHEMA]. Partition logic: [PARTITION_BY]. Order logic: [ORDER_BY]. Engine: [DATABASE_ENGINE]. TASK: 1. Choose the correct window function (ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD / SUM OVER / FIRST_VALUE / NTH_VALUE / NTILE) and justify the choice in one line. 2. Specify the exact frame clause (ROWS vs RANGE, UNBOUNDED PRECEDING, CURRENT ROW) and explain why the frame matters here. 3. Write the query, including the outer filter (since you cannot filter on a window function in WHERE). 4. Show the difference your frame choice makes with a 2-row before/after illustration. 5. Note ties handling and NULL ordering behavior. OUTPUT FORMAT: Function choice & rationale -> Frame explanation -> Final ```sql``` -> Frame illustration -> Edge cases (ties, NULLs, gaps). CONSTRAINTS: Use a subquery/CTE to filter on ROW_NUMBER results. Be explicit about RANGE vs ROWS for running totals when ORDER BY has duplicates. State engine-specific NULLS FIRST/LAST behavior. Do not use GROUP BY where a window is the correct tool.
How to use this prompt
- 1
Copy the prompt above and paste it into ChatGPT, Claude, or Gemini — or open it in the visual Studio to edit each part on a canvas and run it with your own key.
- 2
Replace any bracketed placeholders with your specifics. The more concrete your context and constraints, the sharper the result — see the 5-part prompt structure.
- 3
Run it, then refine. Ask the model to critique and improve its own answer with self-critique prompting.
Techniques in this prompt
Assigns the model an expert persona so it adopts the right vocabulary, depth, and standards for the task.
Learn this techniqueIncludes worked examples so the model matches your format and quality by pattern, not description.
Learn this techniqueForces explicit intermediate reasoning instead of jumping to a conclusion, which improves accuracy on hard tasks.
Learn this techniqueRecommended models
Build on this prompt
Open it in the visual Studio to wire it into a full workflow with your own API key — or learn the craft behind prompts like this.
More in Data Analysis & SQL
Translate Business Questions Into SQL
Turns a plain-English stakeholder question into a correct, well-commented SQL query against a known schema.
Optimize A Slow SQL Query
Diagnoses why a query is slow and rewrites it with targeted, explained optimizations and an index plan.
Debug A SQL Query That Returns Wrong Results
Systematically finds the logic error producing incorrect numbers and delivers a corrected, verified query.
Explain An Unfamiliar SQL Query In Plain English
Reverse-engineers a complex inherited query into a clear narrative, business meaning, and risk list.