SQL Query Generator Prompts: 25+ Tested Database & Performance Playbooks (2026)

System Directive: You are a Principal Database Architect and SQL Optimization Specialist. Generate a robust, production-grade SQL query for the objective described below.

Target Database Dialect: [SPECIFY: PostgreSQL 16 / MySQL 8.0 / Snowflake / BigQuery / Microsoft SQL Server]

Schema & Tables:
"""
[PASTE TABLE DDLs OR COLUMN LISTS WITH DATA TYPES: e.g., users, orders, order_items, product_catalog]
"""

Business & Analytical Objective:
"""
[DESCRIBE OBJECTIVE: e.g., Calculate rolling 90-day customer spend, rank customers within each geographic region using DENSE_RANK(), flag accounts with month-over-month spend contraction >20%, and display only the top 10 ranked customers per region]
"""

Technical Query Constraints:
1. CTE Structure: Organize the logic into sequential, readable Common Table Expressions (`WITH customer_daily_spend AS (...)`). Avoid deeply nested subqueries in `FROM` clauses.
2. Window Functions: Utilize window functions (`SUM() OVER(...)`, `LAG() OVER(...)`, `DENSE_RANK() OVER(...)`) with explicit `PARTITION BY` and `ORDER BY` clauses.
3. Edge Case Handling: Guard against division by zero using `NULLIF()`. Handle null values gracefully with `COALESCE()`.
4. Formatting: Adhere to standard SQL uppercase keywords, clear 2-space indentation, and explicit table aliases on every projected column.

Act as a Senior Database Engineer. Formulate a recursive SQL query (`WITH RECURSIVE`) to traverse a hierarchical parent-child tree structure.

Database Dialect: [e.g., PostgreSQL / Oracle / SQL Server]

Table Schema:
- Table Name: [e.g., organizational_units / category_nodes]
- Primary Key: `id`
- Parent Foreign Key: `parent_id` (NULL indicates root node)
- Additional Columns: [e.g., `name`, `department_head_id`, `budget_allocated`]

Traversal Requirements:
1. Base Member (Anchor): Select the designated root node or starting entity: `WHERE id = [STARTING_NODE_ID]`.
2. Recursive Member: Join the parent node to its immediate children, tracking:
   - `tree_depth`: Integer tracking generation/level starting at 0.
   - `lineage_path`: Text breadcrumb path showing ancestors (e.g., "/Executive/Engineering/Infrastructure").
   - `is_leaf`: Boolean flag identifying nodes with zero sub-children.
3. Infinite Loop Safeguard: Implement cycle detection (e.g., Postgres `CYCLE id SET is_cycle USING path`) or an explicit depth guard `WHERE tree_depth < 15` to prevent infinite recursion on circular graphs.
4. Aggregation Rollup: Calculate the cumulative sum of `budget_allocated` for each branch rollup.

Act as a Business Intelligence Data Engineer. I need to pivot the transactional event rows below into an aggregated columnar cross-tab report.

Target Dialect: [SPECIFY: PostgreSQL / MySQL / Snowflake]

Source Table Details:
"""
[PASTE TABLE STRUCTURE: e.g., monthly_sales containing columns: sales_rep_id, month_year (YYYY-MM), product_category, gross_revenue]
"""

Pivot Specifications:
1. Column Transformation: Pivot the monthly periods (e.g., 'Jan-2026', 'Feb-2026', 'Mar-2026', 'Q1-Total') into distinct horizontal columns per sales rep.
2. Dialect Approach:
   - If PostgreSQL: Provide both standard conditional aggregation (`SUM(CASE WHEN ... THEN ... END)`) and the native `tablefunc` `crosstab()` approach.
   - If Snowflake / SQL Server: Use the native `PIVOT` operator.
3. Grand Totals & Percentages: Append a row-level grand total and calculate the percentage contribution of each rep to total organization gross revenue.

System Directive: You are a Principal PostgreSQL Performance Tuning Expert and Storage Engine Specialist. Audit the slow query and raw execution plan output below.

Original SQL Query:
"""
[PASTE SLOW SQL QUERY HERE]
"""

Execution Plan Output (EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)):
"""
[PASTE RAW EXPLAIN ANALYZE TREE]
"""

Diagnostic Requirements:
1. Bottleneck Node Breakdown:
   - Locate the exact operational nodes consuming >80% of total execution time.
   - Flag expensive Sequential Scans (`Seq Scan`) on large tables.
   - Identify Memory Spills: Check for `Sort Method: external merge Disk` or Hash Join batches spilling to temporary files due to constrained `work_mem`.
   - Estimate Divergence: Compare Estimated Rows vs. Actual Rows to spot stale table statistics (`ANALYZE` required).
2. Root-Cause Diagnosis: Explain specifically why the query planner chose this plan over index scans.
3. Rewritten Optimized SQL: Provide a refactored SQL query that eliminates redundant subqueries, reorganizes join trees, and optimizes predicate placement.
4. Indexing Prescription: Provide the exact `CREATE INDEX CONCURRENTLY` statements required to support the refactored query.

Act as a Database Reliability Engineer (DBRE). I have a high-frequency transactional table processing 2,000 queries per second with increasing read latency.

Target Table Schema & Row Count:
- Table: [e.g., customer_invoices, 45 million rows]
- Columns: `id`, `tenant_id`, `status`, `created_at`, `total_amount_usd`, `billing_email`, `pdf_url`

Critical High-Traffic Query:
"""
SELECT id, total_amount_usd, billing_email 
FROM customer_invoices 
WHERE tenant_id = 452 
  AND status = 'UNPAID' 
  AND created_at >= '2026-01-01' 
ORDER BY created_at DESC 
LIMIT 50;
"""

Design the Optimal Indexing Strategy:
1. The ESR (Equality, Sort, Range) Principle: Explain the exact ordering of columns in the composite index (`tenant_id`, `status`, `created_at`).
2. Covering Index Optimization: Explain how adding `INCLUDE (total_amount_usd, billing_email)` enables an Index-Only Scan (`Index Only Scan using ...`), bypassing table heap lookups entirely.
3. Partial / Filtered Index Option: Would a partial index (`WHERE status = 'UNPAID'`) provide significant space savings if unpaid invoices represent <5% of total table records?
4. Exact DDL Command: Output the complete `CREATE INDEX CONCURRENTLY` statement with tablespace options.

Act as a Senior Backend Systems Engineer. Our application is suffering from catastrophic N+1 query execution caused by our ORM [SPECIFY: Prisma / Hibernate / Django ORM / ActiveRecord].

ORM Model Context & Loop:
"""
[PASTE ORM QUERY SNIPPET OR PSEUDOCODE SHOWING NESTED FETCHING IN A LOOP]
"""

Execution Protocol:
1. N+1 Vulnerability Analysis: Document how many discrete round-trips to the database occur when processing 1,000 parent records.
2. Consolidated SQL Query: Replace the loop with a single, high-performance SQL query utilizing `LEFT JOIN` and `JSON_AGG()` / `json_build_object()` to return the entire nested object graph in a single round-trip.
3. ORM Refactor: Provide the native ORM configuration (e.g., `include`, `select_related`, or `prefetch_related`) that forces the ORM to generate this optimized pattern natively.

System Directive: You are a Principal Data Architect. Design an institutional-grade, normalized relational database schema (3rd Normal Form) for the business domain described below.

Domain & Business Entities:
"""
[DESCRIBE APPLICATION REQUIREMENTS: e.g., Multi-tenant B2B SaaS platform with organizations, users, role-based access control (RBAC), subscription tiers, invoices, and API key management]
"""

Target RDBMS: [e.g., PostgreSQL 16]

Schema Deliverables:
1. Entity Relationship Mapping: Define all entities, cardinalities (1:1, 1:N, N:M via join tables), and primary key types (UUIDv7 vs BIGINT IDENTITY).
2. Production DDL Scripts: Complete, executable SQL `CREATE TABLE` scripts with:
   - Primary Keys and Foreign Key Constraints with appropriate `ON DELETE RESTRICT` or `ON DELETE CASCADE` rules.
   - Domain Check Constraints (`CHECK (status IN ('active', 'suspended', 'pending'))`, `CHECK (price_cents >= 0)`).
   - Audit Columns: `created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`, `updated_at`, `deleted_at` (soft deletes).
3. Concurrency Protection: Implement Optimistic Locking using an integer `version` column.

Act as a Database Operations Lead (DBRE). I need to perform a high-risk schema migration on an active production PostgreSQL table containing 80 million rows with 5,000 write ops/sec.

Migration Requirement:
- Operation: [e.g., Adding a NOT NULL column with a default value / Changing a column type from INT to BIGINT / Adding a Foreign Key constraint]
- Target Table: [e.g., payment_transactions]

Zero-Downtime Execution Protocol:
1. Lock Hazards Analysis: Explain what table lock level (`ACCESS EXCLUSIVE`) is required and how it blocks concurrent `SELECT`, `INSERT`, and `UPDATE` statements.
2. Multi-Step Migration Blueprint:
   - Step 1: Set `lock_timeout = '2s'` to prevent query queues from backing up.
   - Step 2: Add column as nullable without default.
   - Step 3: Backfill data in batches using a chunked loop (`WHERE id BETWEEN ... AND ...`) to avoid transaction log bloat.
   - Step 4: Add check constraint `NOT VALID`.
   - Step 5: Validate constraint concurrently (`VALIDATE CONSTRAINT`).
3. Complete Migration SQL Script: Provide the exact rollback-ready migration code.

Act as a Cloud Database Architect. Our application logs 50 million audit trail events monthly into a monolithic table, causing query performance degradation.

Implementation Goals:
1. Declarative Range Partitioning: Design a partitioned table in PostgreSQL partitioned by month: `PARTITION BY RANGE (created_at)`.
2. Partition Maintenance Automation: Provide a PL/pgSQL function or `pg_partman` setup script that automatically pre-creates partitions 3 months in advance and detaches partitions older than 12 months.
3. Partition Pruning Verification: Demonstrate how `SET enable_partition_pruning = on;` enables the query planner to scan only the active month's child table.
4. Archival Protocol: Provide DDL to detach historical partitions (`ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY`) and export to cold storage without locking.

System Directive: You are a Database Security Architect and Compliance Engineer. Create an automated, tamper-evident audit logging trigger system in PostgreSQL.

Requirements:
1. Audit Table Schema (`audit_event_logs`):
   - `id BIGSERIAL PRIMARY KEY`
   - `table_name VARCHAR(64)`
   - `operation VARCHAR(10)` (INSERT, UPDATE, DELETE)
   - `record_id VARCHAR(64)`
   - `old_data JSONB` (NULL on INSERT)
   - `new_data JSONB` (NULL on DELETE)
   - `changed_fields JSONB` (Diff capturing only modified keys on UPDATE)
   - `executed_by VARCHAR(128)` (Current DB user / session application user)
   - `timestamp_utc TIMESTAMPTZ DEFAULT clock_timestamp()`
2. Trigger Function (PL/pgSQL):
   - A single generic, reusable trigger function that dynamically inspects `TG_OP`, `TG_TABLE_NAME`, `OLD`, and `NEW`.
   - Calculate exact JSON delta on UPDATE: `to_jsonb(NEW) - to_jsonb(OLD)`.
3. Immutability Guard: Add security rules preventing `UPDATE` or `DELETE` statements on the `audit_event_logs` table (append-only ledger).

Act as a Senior Database Administrator. Build an analytical Materialized View with non-blocking concurrent refresh capabilities.

Underlying Data Tables:
"""
[DESCRIBE TABLES & HEAVY AGGREGATIONS: e.g., orders, order_items, payments joined to compute daily merchant payouts]
"""

Specifications:
1. Materialized View Definition: Create `materialized_merchant_daily_summary` pre-calculating merchant gross revenue, platform fee deductions, and refund volume.
2. Unique Index Requirement: Create a unique composite index on the materialized view (`CREATE UNIQUE INDEX ... ON ... (merchant_id, summary_date)`)—a mandatory prerequisite for concurrent refresh.
3. Concurrent Refresh Command: Formulate `REFRESH MATERIALIZED VIEW CONCURRENTLY ...` to allow uninterrupted read access while refreshing.
4. Automated Cron Routine: Write an automated maintenance script (`pg_cron` or shell script) that executes the refresh every night at 02:00 UTC with execution error alerting.

Act as a Financial Transaction Systems Engineer and Concurrency Specialist. We are building a double-entry ledger wallet system experiencing concurrent debit race conditions and database deadlocks.

Database Engine: PostgreSQL 16
Isolation Level: Read Committed / Repeatable Read

Core Operational Problem:
Multiple concurrent workers process payment debits for the same wallet simultaneously, resulting in negative balances and deadlock exceptions (`deadlock detected`).

Implement Concurrency Hardening:
1. Row-Level Pessimistic Locking: Write an atomic transfer transaction utilizing `SELECT ... FOR UPDATE` that locks the source and destination wallets in deterministic alphanumeric order (preventing circular deadlocks).
2. Non-Blocking Worker Job Queue: Demonstrate how to construct a high-throughput job queue worker query utilizing `SELECT ... FOR UPDATE SKIP LOCKED` so concurrent worker pods never contend for the same tasks.
3. Balance Check Constraint: Enforce a database-level safety net: `CHECK (balance_cents >= 0)`.

System Directive: You are a Database Migration Specialist. Transpile the following PostgreSQL query into fully compliant, optimized MySQL 8.0 syntax.

Source PostgreSQL Query:
"""
[PASTE COMPLEX POSTGRESQL QUERY]
"""

Transpilation Specifications:
1. Syntax & Operator Conversions:
   - Convert PostgreSQL JSONB operators (`->>`, `@>`) to MySQL `JSON_EXTRACT()` or `->>` path notation.
   - Convert `DISTINCT ON (...)` constructs to equivalent window function subqueries using `ROW_NUMBER() OVER(...)`.
   - Convert `ILIKE` case-insensitive matching to MySQL collation-aware `LIKE` or `LOWER()`.
   - Convert `string_agg()` to `GROUP_CONCAT()`.
   - Adjust `BOOLEAN` literals (TRUE/FALSE) to MySQL `TINYINT(1)` (1/0).
2. Performance Verification: Highlight any behavioral divergence between PostgreSQL MVCC and MySQL InnoDB locking mechanics in the converted query.

Act as a Polyglot Persistence Architect and NoSQL Engineer. Translate the relational SQL query below into an idiomatic MongoDB Aggregation Pipeline.

Source SQL Query:
"""
[PASTE SQL QUERY CONTAINING JOINS, GROUP BY, HAVING, AND SORT]
"""

Target MongoDB Schema Architecture:
- Primary Collection: [e.g., `orders`]
- Foreign Collection to Join: [e.g., `customers`]

Pipeline Requirements:
1. Stage-by-Stage Translation:
   - Translate `WHERE` filters to `$match` (placed as early as possible to utilize indexes).
   - Translate `JOIN` to `$lookup` with explicit `localField` and `foreignField`.
   - Translate joined array flattening to `$unwind`.
   - Translate `GROUP BY` and aggregations (`COUNT`, `SUM`, `AVG`) to `$group` using accumulator operators (`$sum`, `$avg`).
   - Translate `HAVING` filters to a secondary `$match`.
   - Translate `ORDER BY` and `LIMIT` to `$sort` and `$limit`.
2. Python / Mongoose Syntax: Provide the pipeline formatted both as raw JSON and as an executable Python `pymongo` or Node.js Mongoose script.

System Directive: You are a Principal Data Warehouse Architect and ClickHouse / Snowflake Expert. We are migrating our event logging telemetry from a PostgreSQL transactional table to an analytical columnar warehouse.

Source PostgreSQL DDL:
"""
[PASTE TRANSACTIONAL TABLE DDL]
"""

OLAP Architecture Blueprint:
1. Engine & Table Type:
   - If ClickHouse: Define table using the `ReplacingMergeTree` or `SummingMergeTree` engine.
   - If Snowflake: Define table clustering keys and transient vs. permanent storage options.
2. Sorting & Partitioning Keys: Choose primary sorting keys (`ORDER BY (tenant_id, event_date, event_type)`) optimized for sparse primary index lookups.
3. Column Encoding & Compression: Specify column compression codecs (e.g., DoubleDelta for timestamps, ZSTD for text payloads, T64 for integers) to minimize storage footprint and maximize I/O throughput.
4. ETL Ingestion SQL: Provide the bulk data ingestion SQL query to stream data from Postgres into the analytical warehouse.