SQL Query Companion: Framework for Managers
The 'SQL Query Companion: Framework for Managers' prompt acts as an expert SQL Business Intelligence Lead, translating non-technical business requirements and database schemas into efficient, production-ready SQL queries, complete with explanations, assumptions, and potential pitfalls for managers.
What is the SQL Query Companion: Framework for Managers prompt?
Copy the prompt below into ChatGPT, Gemini, Claude or any capable LLM, replace the bracketed variables with your own values, and run it.
ROLE: You are an expert SQL Business Intelligence Lead and Technical Liaison. You excel at translating complex business questions into efficient, readable, and accurate SQL queries. Your specialty is helping non-technical managers bridge the gap between their business objectives and the database layer without requiring them to write the code themselves. GOAL: Your task is to analyze the provided business requirements and schema information to generate production-ready SQL queries. You must ensure the logic matches the business constraints, handle common data pitfalls (like null values or duplicates), and provide a clear explanation that a manager can use to understand the output. CONTEXT: Business Goal: [BUSINESS OBJECTIVE] Database Schema/Tables: [TABLE SCHEMAS] Key Metrics Needed: [KPI DEFINITIONS] Filtering Requirements: [CONSTRAINTS OR DATE RANGES] INSTRUCTIONS: 1. Review the [TABLE SCHEMAS] provided to identify the necessary joins, primary keys, and foreign key relationships. 2. Based on the [BUSINESS OBJECTIVE], determine the required grain of the data (e.g., daily, per customer, per region). 3. Draft a SQL query that incorporates the [KPI DEFINITIONS] and applies all [CONSTRAINTS OR DATE RANGES]. 4. Apply best practices: use meaningful table aliases, include comments for complex logic, utilize Common Table Expressions (CTEs) for readability, and ensure data types are handled correctly (especially dates). 5. Perform a "logic check" to ensure the query accounts for edge cases like zero-value denominators (to avoid division by zero) or empty result sets. 6. Provide a brief narrative summary of what the query calculates in plain English. OUTPUT FORMAT: - SQL QUERY: Provide the complete, formatted code block. Use standard SQL unless a specific dialect (PostgreSQL, Snowflake, BigQuery) is requested in the objective. - LOGIC EXPLANATION: A bulleted list explaining the 'why' behind specific joins or filters. - DATA ASSUMPTIONS: List any assumptions made about the data quality or structure based on the provided schemas. - POTENTIAL PITFALLS: Highlight any risks (e.g., "If a customer has two accounts, they will be counted twice"). QUALITY BAR: The final query must be copy-paste ready. It should prioritize accuracy and performance. Avoid using SELECT *; explicitly name all columns required for the [BUSINESS OBJECTIVE]. Ensure the logic is robust enough to handle messy real-world data environments.
What variables does the SQL Query Companion: Framework for Managers prompt use?
| Variable | What to put | Example |
|---|---|---|
| [BUSINESS OBJECTIVE] | The high-level business problem or question that needs to be answered by the data. | Identify top 10 most profitable customers in Q3. |
| [TABLE SCHEMAS] | The structure of the relevant database tables, including table names, column names, and their data types. | Customers (id, name, email), Orders (order_id, customer_id, order_date, total_amount), LineItems (item_id, order_id, product_id, quantity, unit_price) |
| [KPI DEFINITIONS] | Precise definitions of the key metrics to be calculated, including how they should be derived. | Profit = (sum of unit_price * quantity) - (sum of unit_cost * quantity); Customer Lifetime Value = average total_amount per customer year. |
| [CONSTRAINTS OR DATE RANGES] | Any specific filters, date ranges, or conditions that should be applied to the data. | Only orders placed in the last 90 days; exclude customers marked as 'test_account'; |
How do I use the SQL Query Companion: Framework for Managers prompt?
- 1Step 1: Clearly articulate your [BUSINESS OBJECTIVE] and the specific questions you need answered by the data.
- 2Step 2: Provide the exact [TABLE SCHEMAS] for all relevant tables, including column names and data types, to give the AI proper context.
- 3Step 3: Define all [KPI DEFINITIONS] precisely, explaining how each metric should be calculated.
- 4Step 4: Specify any [CONSTRAINTS OR DATE RANGES] such as date filters, specific user groups, or exclusion criteria.
- 5Step 5: Copy-paste the generated SQL query and its explanations to your team or directly execute it in your database environment.
When should you use the SQL Query Companion: Framework for Managers prompt?
Ad-Hoc Reporting Requests
Quickly generate SQL for one-time or infrequent reports based on urgent business questions without needing to involve data engineers immediately.
Validating Business Metrics
Obtain queries to calculate and verify key performance indicators (KPIs) against raw data, ensuring consistency and accuracy across reports.
Exploring New Data Segments
Formulate SQL to investigate specific customer segments, product performance, or market trends based on defined business criteria and available database schemas.
Pre-Analysis Data Extraction
Prepare structured data extracts for further analysis in tools like Excel or Tableau, ensuring the data aligns with specific business objectives and filters.
Understanding Data Logic
Receive not just the query but also a clear explanation of its logic, enabling managers to understand how their business questions are translated to data operations.
What does the SQL Query Companion: Framework for Managers prompt output look like?
SQL QUERY: WITH TopCustomers AS ( SELECT o.customer_id, SUM(li.quantity * li.unit_price) AS total_revenue, SUM(li.quantity * li.unit_price * 0.7) AS estimated_profit -- Assuming 30% COGS FROM Orders o JOIN LineItems li ON o.order_id = li.order_id WHERE o.order_date BETWEEN '2023-07-01' AND '2023-09-30' -- Q3 2023 GROUP BY o.customer_id ) SELECT c.name AS customer_name, tc.estimated_profit FROM TopCustomers tc JOIN Customers c ON tc.customer_id = c.id ORDER BY tc.estimated_profit DESC LIMIT 10; LOGIC EXPLANATION: * The CTE 'TopCustomers' calculates the total revenue and an estimated profit for each customer within Q3 2023. * It joins 'Orders' and 'LineItems' tables on 'order_id' to get detailed purchase information. * A WHERE clause filters orders to only include those placed between July 1, 2023, and September 30, 2023 (Q3). * The final SELECT statement joins the 'TopCustomers' CTE with the 'Customers' table to retrieve customer names. * Results are ordered by 'estimated_profit' in descending order and limited to the top 10 to identify the most profitable customers. DATA ASSUMPTIONS: * Unit cost is uniformly 70% of unit price for profit calculation. * 'Customers.id' is the primary key for the 'Customers' table. * 'Orders.customer_id' is a foreign key referencing 'Customers.id'. * 'Orders.order_id' is the primary key for 'Orders'. * 'LineItems.order_id' is a foreign key referencing 'Orders.order_id'. POTENTIAL PITFALLS: * The profit calculation uses an assumed Cost of Goods Sold (COGS) percentage. If actual COGS varies by product, this estimate might be inaccurate. * Customers with no orders in Q3 will not appear in the results, even if they were profitable in other quarters.
Which AI model works best with the SQL Query Companion: Framework for Managers prompt?
Excellent at complex logical reasoning, understanding implicit relationships from schemas, and generating well-commented, production-quality code. Strong for data type handling and edge case considerations.
Good for detailed schema interpretation and generating SQL. Handles context and constraints well, including applying specific filtering requirements and incorporating CTEs effectively.
Strong in maintaining clarity in explanations and narrative summaries, which is crucial for the 'LOGIC EXPLANATION' and 'DATA ASSUMPTIONS' sections for managers. Also adept at following structured output formats rigorously.
What are the pros and cons of the SQL Query Companion: Framework for Managers prompt?
Pros
- Generates production-ready SQL queries for non-technical users.
- Includes detailed explanations for easier understanding by managers.
- Identifies potential data pitfalls and assumptions.
- Promotes best practices for SQL readability and maintainability.
- Acts as a bridge between business objectives and technical database operations.
- Ensures data accuracy by addressing common data issues.
Cons
- Relies heavily on the accuracy and completeness of provided schema information.
- Assumed COGS or other business rules might need manual verification if not explicitly stated.
- May require iterative refinement if initial business objective is vague.
How can you get better results from the SQL Query Companion: Framework for Managers prompt?
- Provide the most detailed and accurate database schema possible for better results.
- Clearly define all KPIs, including how they are calculated, to avoid assumptions.
- Specify the desired SQL dialect (e.g., PostgreSQL, Snowflake) if not standard SQL.
- Include examples of desired output values if the calculation is nuanced.
- Review 'DATA ASSUMPTIONS' and 'POTENTIAL PITFALLS' carefully to ensure alignment.
Frequently asked questions about the SQL Query Companion: Framework for Managers prompt
What is the SQL Query Companion: Framework for Managers prompt?
This prompt serves as an AI SQL consultant, helping managers translate their business needs into accurate, production-ready SQL queries. It produces the query along with a clear explanation, data assumptions, and potential pitfalls.
Who is this prompt for?
It is designed for non-technical managers, business analysts, or anyone who needs to extract data or perform analysis from a database but may not have deep SQL coding expertise. It bridges the gap between business objectives and database logic.
Can this prompt handle complex business questions?
Yes, by detailing your business objective and providing comprehensive schema information, the prompt is equipped to tackle a wide range of complex data extraction and analytical queries. The more precise your input, the better the output.
What kind of SQL dialect does it use?
By default, it uses standard SQL. However, you can specify a particular dialect like PostgreSQL, Snowflake, or BigQuery in your 'BUSINESS OBJECTIVE' or 'CONSTRAINTS' section if your database requires it.
What if my schema has sensitive information?
When using AI models, it's generally recommended to sanitize or generalize sensitive information in your schema details before inputting them. Focus on structure and data types rather than actual sensitive values.
How accurate are the 'DATA ASSUMPTIONS' and 'POTENTIAL PITFALLS'?
These sections are generated based on logical deductions from your provided input. They are designed to highlight common issues and implicit understandings. Always review them to ensure they align with your actual data and business context.
Why is it important to provide detailed KPI definitions?
Detailed KPI definitions ensure the AI calculates metrics exactly as your business requires. Ambiguous definitions can lead to misinterpretations and inaccurate data outcomes, impacting decision-making.