Translate Business Logic to SQL 3x Faster with These ChatGPT Prompts
Tired of manually translating complex business requirements into flawless SQL? These expert ChatGPT prompts for SQL query generation help you bridge the gap between business logic and database code, reducing errors and saving hours. Use them with ChatBoost (Alt+P) to get accurate SQL in seconds.
Convert a straightforward business requirement into a basic SQL SELECT query with a WHERE clause.
Act as a senior SQL developer. Translate the following business logic into a standard SQL query. Provide a brief explanation of the query structure.
Business Logic: "Find all users from the `users` table who are marked as 'active' and have signed up in the last 90 days."...
Generate Multi-Condition WHERE Clause
Create a complex WHERE clause from a list of AND/OR conditions described in plain English.
Generate a SQL WHERE clause based on this business rule: "Select customers who are either from 'California' and have spent over $1000, OR are from 'New York' and have made at least 5 purchases."
Assume the table `customers` has the columns: `state` (VARCHAR), `total_spent` (DECIMAL), and `purchase_count` (INT).
Create Query with Exclusions
Translate a business rule that requires including some items while explicitly excluding others.
Write a SQL query to solve this business problem: "We need a list of all products in the 'Electronics' and 'Appliances' categories, but exclude any products from the brand 'Genericorp'."
Table: `products`...
Generate Inner Join from Relationship
Create an INNER JOIN query based on a description of the relationship between two tables.
Given the following table schemas, write a SQL query that translates this logic: "List all orders along with the customer's full name for each order."
Table 1: `orders`...
Find Unmatched Records with LEFT JOIN
Use a LEFT JOIN and a WHERE IS NULL check to find records in one table without a match in another.
Translate this business requirement into a SQL query: "Find all authors who have not yet published any books."
Table 1: `authors`...
Generate Three-Table Join
Construct a query that joins three tables to fulfill a complex data retrieval request.
Write a SQL query for the following request: "For every order, show the product name and the name of the customer who bought it."
Tables:...
Translate Aggregation Logic (SUM/GROUP BY)
Create a query that aggregates data (e.g., total sales) and groups it by a specific dimension.
Translate this business logic into a SQL query using GROUP BY: "Calculate the total sales revenue for each product category."
Table: `sales`...
Generate Query with HAVING Clause
Write a query that filters grouped results based on an aggregate condition.
Create a SQL query for this requirement: "List all departments that have more than 10 employees."
Table: `employees`...
Create Conditional Logic with CASE
Generate a CASE statement to implement conditional logic within a SELECT statement.
Write a SQL query that translates the following business rule into a new column called `customer_segment`: "If a customer's `total_spent` is > 5000, they are 'VIP'. If it's between 1000 and 5000, they are 'Preferred'. Otherwise, they are 'Standard'."
Table: `customers`...
Generate a CTE from Multi-Step Logic
Use a Common Table Expression (CTE) to break down and solve a multi-step business problem.
Translate this multi-step logic into a single SQL query using a Common Table Expression (CTE): "First, find all employees in the 'Engineering' department. Then, from that group, calculate their average salary."
Table: `employees`...
Rank Records with Window Function
Use a window function like RANK() or DENSE_RANK() to rank items within categories.
Write a SQL query using a window function for this business request: "For each product category, rank the products from highest to lowest price."
Table: `products`...
Calculate Running Total
Generate a query with a window function to calculate a running total over a specified order.
Translate this logic into a SQL query: "Show the daily sales revenue and a running total of revenue since the beginning of the month."
Table: `daily_sales`...
Generate Self-Join for Hierarchical Data
Create a self-join query to handle hierarchical data, such as an employee-manager relationship.
Write a SQL query that lists each employee and the name of their direct manager. The data is in a single table.
Table: `employees`...
Convert Business Constraints to DDL
Generate a CREATE TABLE statement that includes constraints based on business rules.
Act as a database architect. Translate the following business requirements into a SQL `CREATE TABLE` statement for a `products` table. Include appropriate data types and constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK).
Requirements:...
Suggest Indexes for a Query
Analyze a business query and suggest optimal database indexes to improve its performance.
I have the following business query which is running slowly. Analyze it and suggest the most effective indexes to create on the `transactions` table to improve performance. Explain your reasoning.
Query:...
Refactor Subquery to JOIN
Rewrite a query that uses a correlated subquery into a more efficient version using a JOIN.
The following SQL query uses a subquery and is inefficient. Please refactor it to use a JOIN for better performance, based on the business logic of finding all customers who have made at least one purchase.
Inefficient Query:...
Translate Date/Time Logic
Create a query that handles specific date and time logic, such as finding records within business hours.
Write a PostgreSQL query to find all support tickets that were created outside of standard business hours. Business hours are defined as Monday-Friday, 9:00 AM to 5:00 PM in the 'UTC' timezone.
Table: `support_tickets`...
Calculate Customer Lifetime Value (LTV)
Generate a complex query to calculate a key business metric like Customer LTV.
Translate the following business logic for calculating Customer Lifetime Value (LTV) into a SQL query: "For each customer, find the sum of all their purchase amounts."
Table: `orders`...
Generate Pivot Table Query
Create a query that transforms data from rows into columns, summarizing it for reporting.
Translate this business requirement into a SQL query that pivots the data: "Create a report showing the total sales for each product category, with each year as a separate column."
Table: `sales`...
Find First Action per User
Use a window function or subquery to find the first action (e.g., purchase, login) for each user.
Write an efficient SQL query to solve this business problem: "For each user, find the date of their very first login."
Table: `login_history`...
Translate to a Different SQL Dialect
Convert an existing SQL query from one dialect (e.g., T-SQL) to another (e.g., PostgreSQL).
Act as a SQL dialect expert. Translate the following T-SQL (SQL Server) query to be compatible with PostgreSQL. Pay attention to differences in TOP vs LIMIT, and date functions.
T-SQL Query:...
Generate Data Cleaning Logic
Create a query to standardize or clean data based on a set of business rules.
Write a SQL SELECT statement that cleans the `phone_number` column based on these rules: "Remove all non-numeric characters like '(', ')', '-', and spaces."
Table: `contacts`...
Calculate Year-Over-Year Growth
Use LAG() window function and CTEs to calculate year-over-year growth for a metric.
Translate this business logic into a SQL query: "Calculate the year-over-year revenue growth percentage for our company. The yearly revenue is in the `yearly_summary` table."
Table: `yearly_summary`...
Find Duplicate Records based on Logic
Write a query to identify duplicate records based on specific business criteria, not just the entire row.
Write a SQL query to find all customers who have the same email address but different `customer_id`s, which indicates a duplicate account.
Table: `customers`...
Generate Query for Sessionization
Create a query to group user events into sessions based on a timeout period.
Translate this complex business logic into a SQL query using window functions: "Group user events into sessions. A new session starts if the time between consecutive events for a user is more than 30 minutes."
Table: `user_events`...
Translate 'If-Then-Else' to Subquery
Convert a complex conditional business rule into a SQL query using a subquery in the WHERE clause.
Translate this business logic into a SQL query: "Select all products. If a product is in the 'Software' category, only include it if its `release_date` is after January 1, 2023. All other categories should be included regardless of date."
Table: `products`...
Turn these prompts into a reusable workspace
Save your favourite prompts once, reuse them with Alt+P, keep a live Table of Contents of long chats, and export conversations when you're done.
