Window Function Analysis — Sales Ops

Variables to fill in: {{question}}{{schema}}{{dialect}}

You are a staff analytics engineer who writes SQL that is correct first, fast second, and readable always. I need your help in the context of CRM pipeline data: stages, conversion rates, and rep performance.

TASK: Solve a sequencing/ranking problem with window functions.

DELIVERABLE: Produce the query using appropriate window functions with frame clauses explained, a worked mini-example showing the window math, and the common off-by-one traps checked.

PROCESS:
1. Review the inputs below. If anything critical is missing or ambiguous, ask me up to three clarifying questions before producing the deliverable.
2. Restate what the question means in terms of the actual tables and grain from the inputs before writing SQL.
3. Build incrementally with CTEs named after business concepts; each step should be checkable alone.
4. State the assumptions the query makes (dedup rules, timezone, null handling) right in the comments.
5. Produce the deliverable in a clean, skimmable format I can use directly.

QUALITY BAR:
- Handles the classic traps: fan-out joins, nulls in aggregates, timezone drift, late-arriving data.
- Readable by the next analyst without a walkthrough.
- Includes the validation query that proves the result is sane.

INPUTS:
- The business question the query must answer: {{question}}
- Relevant tables with their columns, grain, and quirks you know about: {{schema}}
- SQL dialect/warehouse: BigQuery, Snowflake, Postgres, etc.: {{dialect}}

Run this prompt on a real model without leaving the page. Every run is saved to your history for this prompt.

How to use

1. 1Fill in the {{question}}, {{schema}}, {{dialect}} variables with your real details — specifics in, specifics out.
2. 2Paste the prompt into ChatGPT, Claude, Gemini, or any capable model.
3. 3Answer the clarifying questions it asks; that step is what makes the output fit your situation.
4. 4Iterate on the deliverable: ask for alternatives, tighter versions, or a different angle on any section.

Pro tips

• If the output feels generic, add more concrete detail to the inputs — names, numbers, and constraints sharpen everything.
• Works well in a thread: keep the conversation going to refine the query using appropriate window functions with frame clauses explained, a worked mini-example showing the window math, and the common off-by-one traps checked.