Verified against ChatGPT · 2026-08-09
Get a SQL query for an analytics question you can actually explain and defend, not just paste and run
Converts a plain-English analytics question into a SQL query written for your actual schema, plus a plain-English explanation of every join and filter, so you're never running a query you couldn't defend if someone asked how the number was computed.
The prompt
Ready to copy — highlighted parts are example details you can swap.
You are a senior data analyst writing SQL for an analytics question. Do not just produce a query — produce one I can defend when someone asks how the number was computed. QUESTION What percentage of customers who made a first purchase in Q1 made a second purchase within 60 days? RELEVANT TABLES AND COLUMNS orders(order_id, customer_id, order_date, status, amount); customers(customer_id, signup_date, region) SQL DIALECT PostgreSQL 15, running in Redshift-compatible mode EDGE CASES TO HANDLE About 5% of orders have status = 'refunded' and roughly 200 customer_id values are duplicated across two legacy migration batches. Rules: - Write the query using only the tables and columns given — if answering the question properly requires a table or column you weren't given, say exactly what's missing instead of inventing a plausible-sounding table name. - Handle NULLs explicitly wherever they'd silently change the answer (a NULL in a join key dropping rows, a NULL in an aggregated column being ignored by SUM/AVG) rather than leaving default SQL NULL behavior to work in your favor by accident. - If the question is ambiguous about a boundary condition (inclusive/exclusive date range, how to treat duplicate rows, whether cancelled/refunded records count), state the assumption you made explicitly rather than silently picking one interpretation. - Use CTEs to make the logic legible in stages rather than one dense nested query, unless the dialect or performance context given makes that impractical. OUTPUT FORMAT 1. The SQL query, fully formatted for the given dialect. 2. A plain-English walkthrough of what each CTE or major clause does and why, written so a non-SQL-fluent stakeholder could follow the logic. 3. Every assumption you made about an ambiguous boundary condition, listed explicitly. 4. One sentence on what could make this query slow at scale and the simplest fix, if the table sizes given suggest that's a real risk — otherwise say performance isn't a concern here.
Customize
Optional — swap in your own details for the highlighted parts above.
Why this works
Requiring a plain-English walkthrough alongside the query forces the model to actually justify each join and filter rather than produce syntactically valid SQL that silently encodes a wrong assumption — GPT-5.1, like most models, can write a query that runs and returns a number without ever surfacing that it treated a NULL join key in a way that dropped 8% of rows, and the walkthrough requirement makes that behavior visible instead of hidden inside working syntax. Explicitly requiring NULL handling and boundary-condition assumptions to be stated addresses the most common way an analytics query is technically correct but practically wrong: SQL's default NULL semantics (a NULL never equals anything, including another NULL, and is silently excluded from most aggregates) are a frequent, invisible source of undercounting, and a model not told to check for this will happily generate a query that runs cleanly while quietly excluding real rows. The instruction to say what's missing rather than invent a plausible table name matters because a model given a partial schema and no permission to say "I don't have enough" will fabricate a column name that fits the pattern of the ones it was given — which looks completely legitimate in a code block and fails only when someone actually runs it against the real database. The CTE-staging requirement is a readability choice with a real payoff: a dense single query is much harder for a reviewer to audit for the exact assumption that made the query wrong, while a staged CTE query makes each transformation checkable against the plain-English explanation next to it.
What you get back
WITH first_purchases AS (SELECT customer_id, MIN(order_date) AS first_order_date FROM orders WHERE status != 'refunded' GROUP BY customer_id), repeat_purchases AS (SELECT o.customer_id FROM orders o JOIN first_purchases fp ON o.customer_id = fp.customer_id WHERE o.order_date > fp.first_order_date AND o.order_date <= fp.first_order_date + INTERVAL '60 days' AND o.status != 'refunded') SELECT ROUND(100.0 * COUNT(DISTINCT rp.customer_id) / COUNT(DISTINCT fp.customer_id), 1) AS repeat_purchase_pct FROM first_purchases fp LEFT JOIN repeat_purchases rp ON fp.customer_id = rp.customer_id; Assumption stated: refunded orders were excluded from both the first-purchase and repeat-purchase counts, and the 60-day window was treated as inclusive of day 60.
Verified against
ChatGPT GPT-5.1 · 2026-08-09
Changelog
- 2026-08-09 — Initial publish, verified against ChatGPT GPT-5.1.
Need this built into your business?
If a prompt isn't enough — what Scult builds, built and maintained for you — that's Scult's day job.
EXPLORE WHAT SCULT BUILDS
