ChatGPT Prompts for SQL

I need a SQL query that joins multiple tables to produce a specific result set. The database is [PostgreSQL / MySQL / SQL Server].

**Tables involved:**
[List each table with its key columns, e.g., "orders (id, customer_id, total, created_at)", "customers (id, name, email)", "order_items (id, order_id, product_id, quantity, price)"]

**Desired output:**
[Describe the columns and rows you want, e.g., "Customer name, total number of orders, total spend, and their most recent order date — only for customers who placed at least 3 orders in the last 90 days"]

**Join conditions and filters:**
- Primary relationships: [e.g., "orders.customer_id = customers.id"]
- Filters: [e.g., "only completed orders", "exclude test accounts"]
- Sort order: [e.g., "by total spend descending"]

Please provide:
1. The complete query with proper JOIN types (INNER, LEFT, etc.) and explain why each type was chosen
2. A breakdown of how the query executes logically
3. Any edge cases this query might miss (e.g., customers with NULL values, duplicate rows)
4. Suggestions for making the query more efficient if the tables are large

Analyze this slow SQL query and help me optimize it. The database is [PostgreSQL / MySQL / SQL Server].

**Slow query:**
[Paste your SQL query]

**EXPLAIN / EXPLAIN ANALYZE output:**
[Paste the execution plan output]

**Table schemas and existing indexes:**
[Paste CREATE TABLE statements and any CREATE INDEX statements]

**Approximate row counts:**
[e.g., "users: 2M rows, orders: 15M rows, order_items: 80M rows"]

**Current execution time:** [e.g., "~4.2 seconds"]

Please:
1. Read the execution plan and identify the exact bottlenecks (sequential scans, hash joins on large tables, sort operations spilling to disk)
2. Rewrite the query with optimizations — explain each change
3. Recommend specific indexes with CREATE INDEX statements
4. Estimate the expected improvement for each optimization
5. Flag any potential trade-offs (e.g., write performance impact of new indexes, increased storage)

Design a database schema for the following application. Target database: [PostgreSQL / MySQL / SQL Server].

**Application description:**
[Describe the application, e.g., "A SaaS project management tool where organizations have multiple teams, teams have members with different roles, and members create tasks with subtasks, comments, attachments, and time tracking"]

**Key entities and relationships:**
[List main entities and how they relate, e.g., "An organization has many teams. A user can belong to multiple teams with different roles. A task belongs to one project and can have subtasks."]

**Expected scale:**
- Users: [e.g., "50K active users within 1 year"]
- Records per key table: [e.g., "~500K tasks, ~2M comments"]
- Read/write ratio: [e.g., "80% reads, 20% writes"]

**Requirements:**
- [e.g., "Soft deletes on all tables", "Audit trail for task status changes", "Multi-tenancy via organization_id"]

Please provide:
1. Complete CREATE TABLE statements with appropriate data types, constraints, and defaults
2. Foreign key relationships and ON DELETE behavior
3. Recommended indexes for the expected query patterns
4. An explanation of any denormalization choices and why they make sense
5. Migration-friendly naming conventions (snake_case, plural table names, etc.)

I need help with SQL window functions for the following use case. Database: [PostgreSQL / MySQL 8+ / SQL Server].

**What I need to calculate:**
[Describe the calculation, e.g., "For each employee, I need their salary, department average salary, their rank within the department, and a running total of salaries ordered by hire date within each department"]

**Source table(s) and columns:**
[List tables and relevant columns]

**Sample data (at least 5-8 rows):**
[Provide sample input data so the output can be verified]

**Expected output for the sample data:**
[Show what the result should look like]

Please:
1. Write the query using appropriate window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, AVG OVER, NTILE, etc.)
2. Explain each window function clause: PARTITION BY, ORDER BY, and frame specification (ROWS BETWEEN / RANGE BETWEEN)
3. Show how the window frame affects the calculation with a walkthrough on the sample data
4. Provide alternative approaches if the database version doesn't support certain window functions

Write a stored procedure for the following business logic. Database: [PostgreSQL / MySQL / SQL Server].

**Procedure name:** [e.g., "process_monthly_billing"]

**What it should do:**
[Describe the business logic step by step, e.g., "1. Find all active subscriptions due for billing. 2. Calculate the amount based on plan and any prorated days. 3. Create invoice records. 4. Update the subscription's next_billing_date. 5. Return a summary of processed invoices."]

**Input parameters:**
[List inputs, e.g., "billing_date DATE, dry_run BOOLEAN DEFAULT false"]

**Output:**
[What it should return, e.g., "Number of invoices created, total amount billed, list of failed accounts"]

**Error handling requirements:**
- [e.g., "If any single invoice fails, log the error and continue with the next subscription — don't roll back the entire batch"]
- [e.g., "Use SAVEPOINT for individual subscription processing"]

**Tables involved:**
[List tables and their key columns]

Please provide:
1. The complete procedure with proper error handling, transaction management, and logging
2. Comments explaining each major section
3. A test script that calls the procedure with sample data
4. Any caveats about running this in production (locking, timeout, batch size)

Write a SQL migration script to transform data from an old schema to a new schema. Database: [PostgreSQL / MySQL / SQL Server].

**Current (old) schema:**
[Paste the existing CREATE TABLE statements]

**Target (new) schema:**
[Paste the new CREATE TABLE statements or describe the desired structure]

**Data transformation rules:**
[Describe how data maps from old to new, e.g., "Split the 'full_name' column into 'first_name' and 'last_name'", "Convert status values from integers (0,1,2) to enum strings ('draft','active','archived')", "Merge the 'addresses' and 'user_profiles' tables into a single 'users' table"]

**Constraints:**
- Estimated row count: [e.g., "~5M rows in the main table"]
- Downtime tolerance: [e.g., "zero-downtime required" or "30-minute maintenance window"]
- Rollback requirements: [e.g., "must be reversible"]

Please provide:
1. The migration script with proper ordering to handle foreign key dependencies
2. Data validation queries to run before and after the migration (row counts, checksum comparisons)
3. A rollback script to undo the migration
4. Performance considerations (batch size, disabling indexes during migration, etc.)
5. Any data that might be lost or truncated, with warnings

Analyze my table schemas and query patterns, then recommend the optimal set of indexes. Database: [PostgreSQL / MySQL / SQL Server].

**Table schemas:**
[Paste CREATE TABLE statements including current indexes]

**Top queries by frequency (most common first):**
[List your 5-10 most frequent queries with approximate execution frequency, e.g.:
"1. (1000x/min) SELECT * FROM orders WHERE customer_id = ? AND status = 'active' ORDER BY created_at DESC LIMIT 20"
"2. (200x/min) SELECT customer_id, COUNT(*), SUM(total) FROM orders WHERE created_at > ? GROUP BY customer_id"
"3. (50x/min) SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.total > ? AND o.region = ?"]

**Current pain points:**
[e.g., "Query #1 takes 800ms, queries #2 and #3 cause high CPU during peak hours"]

**Write patterns:**
[e.g., "~500 inserts/min to orders, ~100 updates/min to order status"]

Please:
1. Recommend specific indexes (with CREATE INDEX statements) for each query
2. Identify any composite indexes that serve multiple queries
3. Flag any existing indexes that are redundant or never used
4. Estimate the write-performance impact of each new index
5. Prioritize: which indexes give the biggest improvement for the least write overhead?

Translate the following SQL query into plain English that a non-technical stakeholder could understand. Then translate it back into optimized SQL.

**SQL query to explain:**
[Paste your SQL query — can be as complex as you want, with CTEs, subqueries, window functions, etc.]

**Business context:**
[Briefly describe what area of the business this query relates to, e.g., "This is for the monthly revenue dashboard for the finance team"]

Please provide:
1. A plain English explanation of what this query does, written for someone who doesn't know SQL — avoid all technical jargon
2. A bullet-point breakdown of each major clause and what business question it answers
3. An optimized version of the same query (if improvements are possible) with comments explaining the business logic inline
4. Suggested column aliases that make the output self-explanatory to business users (e.g., "total_revenue_usd" instead of "sum_amt")

Refactor the following SQL query by converting nested subqueries into Common Table Expressions (CTEs) for improved readability. Database: [PostgreSQL / MySQL 8+ / SQL Server].

**Original query with nested subqueries:**
[Paste your deeply nested or hard-to-read query]

**What this query is supposed to produce:**
[Describe the expected output in plain English]

Please:
1. Rewrite the query using named CTEs (WITH clauses), giving each CTE a descriptive name that reflects its business purpose
2. Add a comment above each CTE explaining what intermediate result it produces
3. Verify that the refactored query produces identical results to the original
4. If any subqueries are correlated (reference outer query columns), explain whether converting them to CTEs changes the execution plan or semantics
5. Identify any CTEs that could be materialized or turned into temporary tables if the query is still slow after refactoring

Build a SQL query that generates a business report with multiple levels of aggregation. Database: [PostgreSQL / MySQL / SQL Server].

**Report requirements:**
[Describe what the report should show, e.g., "Monthly revenue broken down by product category and region, with subtotals per category, grand totals, month-over-month growth percentage, and a running 3-month average"]

**Source tables:**
[List tables and key columns, with approximate row counts]

**Filters:**
[e.g., "Last 12 months only", "Exclude refunded orders", "Only include verified customers"]

**Output format:**
[Describe the desired columns and row structure, e.g., "Columns: month, category, region, revenue, mom_growth_pct, running_3mo_avg. Rows should include subtotal rows per category with region = 'ALL'"]

Please provide:
1. The complete query using GROUP BY, ROLLUP/CUBE/GROUPING SETS as appropriate
2. Window functions for growth rates and running averages
3. Proper NULL handling and COALESCE for clean output
4. A version formatted for export (no NULL subtotal markers, formatted numbers)
5. Suggestions for turning this into a reusable view or parameterized query

Generate a comprehensive set of data validation queries for the following tables. Database: [PostgreSQL / MySQL / SQL Server].

**Tables to validate:**
[Paste CREATE TABLE statements or describe the schema]

**Business rules that should hold true:**
[List data integrity rules, e.g.:
"Every order must have at least one order_item"
"order.total must equal the sum of its order_items (quantity * price)"
"No customer should have duplicate email addresses (case-insensitive)"
"Every employee must belong to an active department"
"start_date must be before end_date on all subscriptions"]

**Known data quality issues to check for:**
[e.g., "We suspect some orders have negative totals", "There may be orphaned records from a failed migration"]

Please generate:
1. A query for each business rule that returns all violating rows
2. Orphan detection queries — records with foreign keys pointing to non-existent parents
3. Duplicate detection queries — columns or combinations that should be unique but might not be
4. NULL audit — columns that should never be NULL but might have NULLs
5. Range and format validation — dates in the future, negative amounts, invalid email formats
6. A summary query that counts violations per category so you can prioritize fixes

Analyze the following table design and recommend normalization improvements. Database: [PostgreSQL / MySQL / SQL Server].

**Current table(s):**
[Paste CREATE TABLE statements or describe the structure, e.g., a wide denormalized table with repeating groups or redundant data]

**Sample data (5-10 rows showing the problem):**
[Provide sample data that illustrates the redundancy or anomalies]

**Known issues:**
[e.g., "When we update a customer's address, we have to update it in 15 places", "Deleting an order also deletes the only record of the product price at that time"]

**Constraints:**
- Target normal form: [e.g., "3NF" or "BCNF" or "just fix the obvious problems"]
- Performance requirements: [e.g., "The reporting dashboard runs 200 queries/sec against this table and can't afford extra JOINs"]

Please:
1. Identify all normalization violations (repeating groups → 1NF, partial dependencies → 2NF, transitive dependencies → 3NF)
2. Show the step-by-step decomposition with new table designs
3. Provide the CREATE TABLE statements for the normalized schema
4. Write the migration queries to move data from the old structure to the new one
5. Identify cases where strategic denormalization is justified for performance, and explain the trade-off
6. Show before/after query examples for common operations to demonstrate the impact