30 Claude Prompts That Write SQL

You are a senior analytics engineer who writes readable, layered SQL. <context> I have a multi-step calculation that would be an unreadable nest of subqueries. I want it expressed as chained CTEs, each a clear step. Return one correct, commented query. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - Tables and columns: [LIST] - The steps in plain English: [E.G. 1) FILTER PAID ORDERS 2) SUM REVENUE PER CUSTOMER 3) JOIN CUSTOMER INFO 4) KEEP TOP 100] - Final output columns and sort: [LIST] </inputs> <task> Write a WITH clause where each CTE is one named step from the list (e.g. paid_orders, revenue_per_customer, enriched), each building on the previous, ending in a final SELECT. Name every CTE for what it produces and add a one-line comment above each explaining its purpose. </task> <constraints> - One runnable statement; valid syntax only. - One logical step per CTE; no redundant recomputation; descriptive CTE names. - Final SELECT only references the last CTE(s); comment the data flow. </constraints> <format> Return the commented SQL in a code block, then a short list showing the pipeline of CTE names in order. </format>

You are a SQL expert who writes recursive queries. <context> I need a recursive CTE to walk a hierarchy (full org tree, category tree, bill of materials) or to generate a series (dates, numbers). Return one correct, commented query with a termination guard. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - Use case: [HIERARCHY / SERIES] - If hierarchy: table, id, parent_id, and label columns: [E.G. categories(id, parent_id, name)] - Starting point (anchor): [E.G. ROOT ROWS WHERE parent_id IS NULL / A SPECIFIC id] - If series: start, end, and step: [E.G. 2026-01-01 TO 2026-12-31 BY 1 MONTH] - Extra output (e.g. depth, path): [OPTIONAL] </inputs> <task> Write a WITH RECURSIVE query: an anchor member (the starting rows) UNION ALL a recursive member that joins back to the CTE. For a hierarchy, track a depth level and a breadcrumb path; for a series, increment until the end bound. Include a safeguard against infinite loops (depth cap or cycle detection). </task> <constraints> - One runnable statement; valid syntax only for the dialect (WITH RECURSIVE, or the SQL Server CTE form). - Comment the anchor vs recursive member clearly and the termination condition. - Include a depth/level column and guard against cycles. </constraints> <format> Return the commented SQL in a code block, then one line on adding cycle detection if the data might contain loops. </format>

You are a SQL specialist who knows when correlated subqueries are the cleanest tool. <context> I need a per-row value that depends on the current row (e.g. each customer's most recent order date, or a count of related rows). Return one correct, commented query and note when to switch to a JOIN for speed. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - Outer table and columns: [E.G. customers(id, name)] - Related table and link: [E.G. orders(customer_id, created_at, total)] - Per-row value(s) I need: [E.G. LATEST ORDER DATE, TOTAL ORDER COUNT, LAST ORDER TOTAL] - Filters/sort on the outer query: [OPTIONAL] </inputs> <task> Write an outer SELECT with a scalar correlated subquery in the select list for each per-row value (correlated on the outer row's key). Then, in a comment, show the equivalent LEFT JOIN + GROUP BY (or LATERAL/window) rewrite and note that on large tables the set-based version usually scales better. </task> <constraints> - One runnable statement plus the commented rewrite; valid syntax only. - Each subquery must return exactly one value per outer row; comment the correlation. - Explain the performance trade-off in a comment. </constraints> <format> Return the commented SQL in a code block, then one line on when the correlated form is fine vs when to refactor to a join. </format>

You are a SQL reviewer who refactors messy queries into clean, maintainable ones. <context> I have a hard-to-read query with subqueries nested inside subqueries. I want it refactored into readable CTEs with identical results. Return the rewritten, commented query. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - My current query: [PASTE THE FULL QUERY] - Anything I want preserved (column names, ordering, exact result): [NOTES] </inputs> <task> Refactor the query into a WITH clause where each formerly-nested subquery becomes a named CTE, flattening the nesting. Preserve the exact output columns, order, and semantics. Point out (as comments) any redundant or repeated subqueries you were able to compute once and reuse. </task> <constraints> - One runnable statement; valid syntax only; identical result set to the original. - Descriptive CTE names; comment what each replaced. - Do not change filters or joins that would alter results; flag anything ambiguous instead of guessing. </constraints> <format> Return the refactored, commented SQL in a code block, then a short before/after note on what became clearer and any dedup you did. </format>

You are a product analytics engineer who builds funnel and session queries from event data. <context> I have an events table and need either a step-by-step funnel (how many users reach each step) or sessions grouped by inactivity gaps. Return one correct, commented query built from CTEs. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - Events table and columns: [E.G. events(user_id, event_name, occurred_at)] - Goal: [FUNNEL / SESSIONIZATION] - If funnel: ordered steps: [E.G. 'view', 'add_to_cart', 'checkout', 'purchase'] - If sessions: inactivity gap that starts a new session: [E.G. 30 MINUTES] - Time window: [E.G. LAST 30 DAYS] </inputs> <task> For a FUNNEL: use CTEs to find the first timestamp of each step per user (respecting step order), then count users reaching each step and the step-to-step conversion rate. For SESSIONIZATION: order events per user, flag a new session when the gap since the prior event exceeds the threshold (LAG on occurred_at), and assign session ids with a cumulative sum of the flags. Comment the core logic either way. </task> <constraints> - One runnable statement; valid syntax only. - Funnel steps must be counted in order (a later step only counts if the earlier one happened first); comment that ordering. - Sessionization gap logic must use LAG + running sum; comment the threshold. </constraints> <format> Return the commented SQL in a code block, then one line on adjusting the funnel window or session gap. </format>

You are a database architect who designs normalized, well-constrained schemas. <context> I'm describing an app in plain English and need a normalized relational schema as runnable CREATE TABLE statements. Return commented DDL I can execute in order. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SQL SERVER] - What the app does and its main entities: [E.G. A BOOKING APP: users, venues, bookings, reviews] - Key relationships: [E.G. A USER HAS MANY BOOKINGS; A BOOKING BELONGS TO ONE VENUE] - Important fields per entity: [LIST] - Any known constraints/rules: [E.G. EMAIL UNIQUE, RATING 1-5] </inputs> <task> Design a normalized schema (3NF where sensible). Output CREATE TABLE statements in dependency order with primary keys, foreign keys (with ON DELETE behavior), NOT NULL, UNIQUE, CHECK constraints, sensible types, and created_at/updated_at timestamps. Add the indexes the obvious queries will need. Comment each table's purpose and each non-obvious constraint. </task> <constraints> - Runnable DDL in the stated dialect, ordered so foreign keys resolve; valid syntax only. - Use appropriate types (no everything-as-text); explicit FK actions; comment design choices. - Include indexes for foreign keys and common lookups. </constraints> <format> Return the commented DDL in a code block, then a short note on any denormalization trade-offs you deliberately avoided or would consider. </format>

You are a database migration expert focused on zero-downtime changes. <context> I need to add a column (or table) to a live, populated table without breaking production. Return a safe, ordered, commented migration plus a rollback. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SQL SERVER] - Table and change: [E.G. ADD status TEXT TO orders, DEFAULT 'pending'] - Should existing rows be backfilled?: [YES, WITH VALUE X / NO] - Must it end up NOT NULL?: [YES / NO] - New constraints/indexes needed: [E.G. INDEX ON status] - Approx row count (for locking concerns): [E.G. 5M ROWS] </inputs> <task> Write the migration as safe, ordered steps: add the column as nullable first, backfill in the same or a separate step, add the default, then add NOT NULL/constraints/indexes last (using CREATE INDEX CONCURRENTLY on Postgres for large tables). Explain in comments why the order avoids long locks. Include a matching rollback (DOWN) script. </task> <constraints> - Runnable, ordered DDL/DML; valid syntax only. - Avoid a blocking rewrite/long lock on a large table; comment the locking behavior of each step. - Provide an explicit rollback that undoes every step. </constraints> <format> Return the UP and DOWN scripts in separate labeled code blocks, then one line on running the backfill in batches if the table is huge. </format>

You are a database performance engineer who designs indexes. <context> A specific query is slow and I want the right indexes (not a shotgun of them). Return the CREATE INDEX statements with reasoning. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SQL SERVER] - The query (or query shape): [PASTE IT] - Relevant table sizes: [E.G. orders 20M ROWS] - Existing indexes: [LIST OR 'JUST PRIMARY KEYS'] - Read vs write balance: [READ-HEAVY / WRITE-HEAVY] </inputs> <task> Recommend the minimal set of indexes to speed up the query: identify the columns used in WHERE, JOIN, ORDER BY, and GROUP BY, propose composite indexes in the correct column order (equality columns first, then range/sort), and note where a covering index or partial index helps. Output the CREATE INDEX DDL and, in comments, explain each index and the write-cost trade-off. </task> <constraints> - Runnable CREATE INDEX statements; valid syntax only; CONCURRENTLY on Postgres for live tables. - Justify column order in composite indexes; avoid redundant/overlapping indexes. - Flag any index that duplicates an existing one. </constraints> <format> Return the commented DDL in a code block, then a short note on how to confirm the index is used (EXPLAIN) and the write overhead to expect. </format>

You are a database engineer who enforces data quality at the schema level. <context> Bad data is getting into a table and I want the database to reject it. Return ALTER TABLE statements adding constraints, with a check for existing violations first. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SQL SERVER] - Table and columns: [E.G. accounts(id, email, status, balance, plan)] - Rules to enforce: [E.G. email UNIQUE + FORMAT, status IN SET, balance >= 0, plan REFERENCES plans] - Can I fix or must I skip existing bad rows?: [FIX / REPORT ONLY] </inputs> <task> First write SELECTs that find rows currently violating each rule (so I can clean them before the constraint is added). Then write the ALTER TABLE ... ADD CONSTRAINT statements: CHECK constraints, UNIQUE, NOT NULL, and FOREIGN KEY as needed. Comment each constraint and why adding it will fail if violations remain. </task> <constraints> - Runnable validation SELECTs and ALTER statements; valid syntax only. - Order: detect violations, (optionally) clean, then add constraints. - Name each constraint explicitly so it can be dropped later; comment each. </constraints> <format> Return the detection SELECTs and the ALTER statements in separate labeled code blocks, then one line on validating constraints NOT VALID first on large tables (Postgres). </format>

You are a backend engineer who writes safe, re-runnable seed scripts. <context> I need to insert reference/lookup data (statuses, roles, plans, categories) in a script that can run multiple times without creating duplicates. Return one idempotent, commented script. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SQL SERVER] - Target table and columns: [E.G. plans(code, name, price_cents, is_active)] - The rows to seed: [LIST THE VALUES] - Natural key that must stay unique: [E.G. code] - On conflict, update or ignore?: [UPDATE THE ROW / LEAVE IT AS IS] </inputs> <task> Write an idempotent upsert: use INSERT ... ON CONFLICT (key) DO UPDATE/DO NOTHING on Postgres, INSERT ... ON DUPLICATE KEY UPDATE on MySQL, or MERGE on SQL Server. Ensure re-running the script leaves the table in the same correct state. Comment the conflict target and what happens on repeat runs. </task> <constraints> - One runnable script; valid syntax for the stated dialect only. - Truly idempotent (safe to run N times); relies on the natural key, not auto-increment ids. - Comment each row group and the conflict behavior. </constraints> <format> Return the commented script in a code block, then one line on wrapping it in a transaction so a partial failure rolls back cleanly. </format>

You are a SQL performance engineer who rewrites slow queries into fast ones. <context> I have a query that runs too slowly and I want a faster version that returns identical results. Return the optimized, commented query and a plain-English list of what you changed. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - The slow query: [PASTE IT] - Table sizes and existing indexes: [LIST] - How slow / how fast it needs to be: [E.G. 40s NOW, NEED UNDER 2s] - Anything that must not change (result shape, ordering): [NOTES] </inputs> <task> Analyze the query for common performance killers (functions on indexed columns in WHERE, SELECT *, correlated subqueries that could be joins, OR conditions that block index use, needless DISTINCT, implicit type casts) and rewrite it to be faster while returning the exact same rows. List every change and why it helps. </task> <constraints> - One runnable rewritten statement; valid syntax only; identical result set. - Don't change semantics; if a rewrite risks different results, flag it instead of applying it. - Comment each optimization inline. </constraints> <format> Return the optimized SQL in a code block, then a numbered list of the changes and any indexes I should add for the biggest win. </format>

You are a query-tuning expert who reads execution plans. <context> I have a query and its EXPLAIN (or EXPLAIN ANALYZE) output and want to know what's slow and how to fix it. Return the diagnosis plus a fixed query and/or index. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SQL SERVER] - The query: [PASTE IT] - The EXPLAIN / EXPLAIN ANALYZE output: [PASTE IT] - Table sizes and existing indexes: [LIST] </inputs> <task> Walk through the plan: identify the expensive nodes (sequential scans on big tables, nested loops over many rows, large sorts/spills to disk, bad row-count estimates), explain in plain English what each means, then give concrete fixes: the index to add, the query rewrite, or the statistics to refresh. Provide the corrected SQL/DDL. </task> <constraints> - Ground the analysis in the actual plan numbers provided; don't invent nodes not present. - Provide runnable, valid DDL/SQL for each fix. - Prioritize fixes by expected impact; comment each. </constraints> <format> Return a short plan-reading summary, then the fix SQL/DDL in a code block, then how to re-check the plan to confirm improvement. </format>

You are a backend performance engineer who eliminates N+1 query patterns. <context> My app runs one query per row in a loop (an N+1 problem). I want a single set-based SQL query that fetches everything at once. Return the consolidated, commented query. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - The loop / per-row query I run today: [PASTE THE INNER QUERY OR DESCRIBE IT] - The parent set I loop over: [E.G. A LIST OF order_ids / ALL ACTIVE users] - Related data I fetch per parent: [E.G. LINE ITEMS, LATEST STATUS, TOTALS] - How I want it shaped for the app: [ONE ROW PER PARENT / ONE ROW PER CHILD / AGGREGATED] </inputs> <task> Replace the per-row loop with one query using JOINs and/or aggregation (or json_agg/array_agg to nest children under each parent where the dialect supports it). Return the shape the app needs so it can consume the result in a single round trip. Comment how this collapses N+1 calls into one. </task> <constraints> - One runnable statement; valid syntax only. - Use IN (...) or a JOIN against the parent set instead of a per-row query; comment the change. - If nesting children, use the dialect's aggregate JSON/array function and note it. </constraints> <format> Return the commented SQL in a code block, then one line on how the app should map the single result set back to its objects. </format>

You are a backend engineer who builds fast pagination for large tables. <context> Deep pagination with LIMIT/OFFSET has gotten slow because high offsets scan and discard rows. I want efficient keyset (cursor) pagination. Return the query and how to page through it. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SNOWFLAKE / SQL SERVER] - Table and columns: [E.G. posts(id, created_at, title)] - Sort order for the list: [E.G. created_at DESC, id DESC] - Page size: [E.G. 20] - Columns to return: [LIST] - Filters that always apply: [OPTIONAL] </inputs> <task> Write a keyset-pagination query: order by a unique, stable key (add a tie-breaker like id so the order is total), and fetch the next page using a WHERE clause comparing against the last row's cursor values (e.g. (created_at, id) < (:last_created_at, :last_id)) with LIMIT page_size. Show the first-page query (no cursor) and the next-page query (with cursor params). </task> <constraints> - Two runnable statements (first page, next page); valid syntax only; parameterized cursor. - The ORDER BY must be a total order (include a unique tie-breaker); comment why OFFSET is avoided. - Explain what cursor values the client must send back for the next page. </constraints> <format> Return both queries in a code block, then a short note on how the client tracks and passes the cursor between pages. </format>

You are a database engineer who runs large data changes without locking production. <context> I need to UPDATE or DELETE millions of rows, but doing it in one statement locks the table and bloats the transaction log. I want a safe batched approach. Return the batched, commented script. </context> <inputs> - SQL dialect: [POSTGRES / MYSQL / SQL SERVER] - Operation: [UPDATE ... SET ... / DELETE] - Table and the WHERE that identifies target rows: [E.G. DELETE FROM events WHERE created_at < '2025-01-01'] - Approx rows affected: [E.G. 30M] - Batch size preference: [E.G. 10000] - Any ordering or key column to batch on: [E.G. id] </inputs> <task> Write a batched loop that changes rows in chunks of the given size (using a key range or LIMIT-based selection depending on the dialect), committing each batch so locks release and the transaction log stays small, until no rows remain. Include a verification COUNT before and after. Comment the loop, the commit points, and how to throttle between batches. </task> <constraints> - Runnable script in the stated dialect (procedural block or a documented repeatable statement); valid syntax only. - Each batch commits separately; the WHERE must reliably target only unprocessed rows so it terminates. - Comment how to pause/resume and how to verify progress. </constraints> <format> Return the commented batching script in a code block, then one line on running it during low-traffic hours and watching lock/replication lag. </format>