Every data analyst hits the wall: a query that should take minutes turns into hours of debugging, or you stare at an empty editor wondering how to even start. You know the schema, you know the question, but the gap between them feels like a canyon. What if you could describe that gap in plain English and let an AI assistant generate, optimize, and even explain the SQL? That's not a futuristic fantasy—it's a workflow that's already changing how analysts work. I've compiled 12 battle-tested prompts that I use daily, from simple query generation to advanced ClickHouse optimizations. Each one comes with a real-world scenario and a concrete example, so you can copy, adapt, and start using them immediately.
1. The "Translate Business Question to SQL" Prompt
When to use: You have a business question but aren't sure which tables or joins are needed. This prompt forces the AI to think step-by-step and reveal its reasoning, making it easier to verify the logic.
The prompt:
You are a senior SQL analyst. Given the following database schema: [INSERT SCHEMA HERE]
Translate this business question into a SQL query: [INSERT QUESTION]
First, identify the relevant tables and columns. Then, explain the join conditions. Finally, write the SQL query. Output in this format:
1. Tables needed:
2. Join logic:
3. SQL query:
Real-world example:
Schema: users(id, name, signup_date), orders(id, user_id, amount, order_date), order_items(order_id, product_id, quantity), products(product_id, product_name, category).
Question: "What was the total revenue per product category in Q3 2026?"
AI output: Tables: orders, order_items, products. Join logic: orders.id = order_items.order_id, order_items.product_id = products.product_id. Query:
SELECT p.category, SUM(o.amount) AS total_revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2026-07-01' AND '2026-09-30'
GROUP BY p.category;
This prompt saves time on exploratory queries, especially when you're new to a database.
2. The "Explain This Query Like I'm 5" Prompt
When to use: You inherited a complex query with multiple CTEs and window functions, and you need to understand it before modifying it.
The prompt:
Explain the following SQL query in simple terms. Break down what each part does, step by step, as if explaining to a junior analyst. Include the purpose of each CTE, join, and window function.
[INSERT QUERY]
Real-world example:
Query:
WITH ranked AS (
SELECT user_id, order_date, amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 1;
AI explanation: "This finds the most recent order for each user. The CTE ranked assigns a row number to each order, restarting for each user, ordered by date descending. Then it filters to only rows where rn=1, i.e., the latest order."
This is invaluable for code reviews and learning new patterns.
3. The "SQL to Python/ClickHouse Translation" Prompt
When to use: You need to move logic between SQL dialects or from SQL to a data processing library like pandas or PySpark.
The prompt:
Translate the following SQL query to [target: e.g., ClickHouse SQL, PostgreSQL, pandas] code. Preserve the semantics exactly. If there is no direct equivalent, suggest the closest alternative.
[INSERT SQL QUERY]
Real-world example:
SQL:
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 50000;
To ClickHouse:
SELECT department, avg(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING avg(salary) > 50000;
(Note: In ClickHouse, avg is lowercase, but the syntax is nearly identical.)
To pandas:
filtered = employees[employees['hire_date'] > '2020-01-01']
result = filtered.groupby('department')['salary'].mean().reset_index()
result = result[result['salary'] > 50000]
result.columns = ['department', 'avg_salary']
This prompt is a lifesaver when migrating between systems.
4. The "Optimize My Query" Prompt
When to use: Your query runs slowly, and you need concrete suggestions for indexes, rewriting, or schema changes.
The prompt:
Here is a SQL query that runs slow. I am using [DBMS, e.g., PostgreSQL 15]. Please suggest optimizations: add indexes, rewrite joins, avoid subqueries, use window functions, etc. Provide the optimized query and explain why it's faster.
[INSERT QUERY AND EXPLAIN PLAN IF AVAILABLE]
Real-world example:
Query:
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');
Optimized suggestion: Use a JOIN instead of IN, and create an index on customers(country) and orders(customer_id).
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';
Explanation: A join allows the optimizer to choose a hash join or nested loop with indexes, while IN can be less efficient with large subquery results. Indexes reduce table scans.
Always include your DBMS version, as features like EXPLAIN ANALYZE vary.
5. The "Find Bugs in My Query" Prompt
When to use: Your query returns unexpected results (e.g., duplicates, NULLs, wrong aggregation), and you can't spot the issue.
The prompt:
I have a SQL query that returns incorrect results. The expected output is [describe]. The actual output is [describe]. Please analyze the query for logical errors, such as missing joins, incorrect aggregation levels, or NULL handling.
[INSERT QUERY]
Real-world example:
Query:
SELECT user_id, SUM(amount) AS total
FROM orders
LEFT JOIN users ON orders.user_id = users.id
WHERE users.active = 1
GROUP BY user_id;
Bug: The WHERE clause filters out inactive users, turning the LEFT JOIN into an INNER JOIN. Fix: move the condition to the ON clause.
SELECT user_id, SUM(amount) AS total
FROM orders
LEFT JOIN users ON orders.user_id = users.id AND users.active = 1
GROUP BY user_id;
This prompt is perfect for double-checking your logic before running expensive queries.
6. The "Generate Test Data" Prompt
When to use: You need to test a query or an application, but the database is empty or you don't want to touch production data.
The prompt:
Generate a SQL script that inserts [N] rows of realistic test data into the following table: [TABLE SCHEMA]. Ensure the data is consistent (e.g., foreign keys match, date ranges are logical). Include variety in values.
Real-world example:
Table: employees(id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))
AI output for 5 rows:
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice Johnson', 'Engineering', 95000.00),
(2, 'Bob Smith', 'Marketing', 72000.00),
(3, 'Carol White', 'Engineering', 87000.00),
(4, 'David Brown', 'Sales', 65000.00),
(5, 'Emma Davis', 'Engineering', 102000.00);
This is great for prototyping and learning.
7. The "Schema Generation from Description" Prompt
When to use: You're starting a new project and need a database schema based on a description of the entities and relationships.
The prompt:
Design a database schema for [describe the application, e.g., an e-commerce platform]. Include tables, primary keys, foreign keys, indexes, and the data types. Provide the SQL DDL for [MySQL/PostgreSQL/ClickHouse].
Real-world example:
Description: "A blog with users who can write posts and comment on posts. Each post has a category."
AI output (simplified):
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
category_id INTEGER REFERENCES categories(id),
title VARCHAR(200) NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INTEGER REFERENCES posts(id),
user_id INTEGER REFERENCES users(id),
content TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
This is a huge time-saver for prototyping.
8. The "Window Functions Explained" Prompt
When to use: You need to understand or write window functions like ROW_NUMBER, RANK, or LAG for analytics.
The prompt:
Explain the difference between [ROW_NUMBER, RANK, DENSE_RANK] in SQL. Provide an example query using each, and show the output for a sample table.
Real-world example:
Sample table: sales (salesperson, amount)
Query:
SELECT salesperson, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
RANK() OVER (ORDER BY amount DESC) AS rank,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM sales;
Output: If two people have the same amount, row_num will still be unique, rank will skip numbers after ties, dense_rank won't. This understanding is critical for accurate reporting.
9. The "Common Table Expressions (CTE) Simplification" Prompt
When to use: Your query is a mess of nested subqueries. You want to refactor it using CTEs for readability.
The prompt:
Rewrite the following SQL query using CTEs to improve readability. Break it into logical parts with meaningful names. Preserve the exact logic.
[INSERT QUERY]
Real-world example:
Original:
SELECT * FROM (
SELECT user_id, MAX(order_date) AS last_order
FROM orders GROUP BY user_id
) t
JOIN users ON t.user_id = users.id;
Rewritten:
WITH last_orders AS (
SELECT user_id, MAX(order_date) AS last_order
FROM orders
GROUP BY user_id
)
SELECT * FROM last_orders JOIN users ON last_orders.user_id = users.id;
CTEs make debugging easier because you can test each part separately.
10. The "Pivot Data Without PIVOT" Prompt
When to use: Your DBMS doesn't support PIVOT (like older MySQL), or you need a flexible solution.
The prompt:
I need to pivot the following data: [describe the table and desired output]. Write a SQL query using conditional aggregation (CASE WHEN) to achieve a pivot. Make it generic so it works in [MySQL/PostgreSQL/ClickHouse].
Real-world example:
Table: sales (year, quarter, revenue)
Desired: rows = year, columns = Q1..Q4
Query:
SELECT year,
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
FROM sales
GROUP BY year;
This approach is universal across SQL dialects.
11. The "ClickHouse-Specific Optimization" Prompt
When to use: You're working with ClickHouse and need to optimize queries for columnar storage, use materialized views, or apply specialized functions.
The prompt:
I have a ClickHouse query that is slow. Suggest optimizations: use appropriate ENGINE, add ORDER BY to the table, use PREWHERE, or use aggregate functions. Provide the optimized query.
[INSERT TABLE DDL and QUERY]
Real-world example:
Table: events (event_date Date, user_id UInt64, event_type String) ENGINE = MergeTree ORDER BY event_date
Query:
SELECT event_type, count() FROM events WHERE event_date >= '2026-01-01' GROUP BY event_type;
Optimization: Add PREWHERE event_type != '' to filter non-empty event types before reading other columns. Or change the ORDER BY to (event_date, event_type) if you often filter by event_type. ClickHouse docs (https://clickhouse.com/docs/en/optimize/) recommend using PREWHERE for filtering on columns that are not in the primary key.
12. The "Query Documentation Generator" Prompt
When to use: You need to document a complex query for your team or for future reference.
The prompt:
Write a documentation block for the following SQL query. Include: a summary of what the query does, the business context, parameters (if any), and an explanation of each major part. Format it as a comment block that could be placed at the top of the query file.
[INSERT QUERY]
Real-world example:
Output:
/*
* Purpose: Identify churned customers (no orders in last 90 days)
* Business context: Used for retention campaigns
* Parameters: none
* Explanation: First CTE gets last order date per customer, then filters
*/
WITH last_orders AS (
SELECT customer_id, MAX(order_date) AS last_date
FROM orders GROUP BY customer_id
)
SELECT * FROM customers c
LEFT JOIN last_orders lo ON c.id = lo.customer_id
WHERE lo.last_date < CURRENT_DATE - INTERVAL '90 days' OR lo.last_date IS NULL;
Good documentation saves hours when you revisit old code.
Putting It All Together
These prompts aren't magic—they're a way to leverage AI to handle the repetitive parts of SQL work, freeing you to focus on the logic and business questions. The key is to be specific: always provide schema, DBMS, and context. The more information you give, the better the output. Remember to verify the generated SQL, especially for performance-critical queries. Start with one or two prompts that address your biggest pain points, and soon you'll wonder how you ever worked without them.
And if you're looking to systematize this approach, consider exploring the AI-powered courses at asibiont.com, where you can practice these techniques in a structured environment. Happy querying!
Comments