Metric Definition SQL — Product Analytics

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 product event data: activation, retention, and feature adoption.

TASK: Define a business metric in SQL precisely enough to be the single source of truth.

DELIVERABLE: Produce the metric spec: plain-language definition with edge cases decided, the canonical SQL, the disaggregation dimensions, and the reconciliation query against any existing version.

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 metric spec.