ChatGPT for SQL: How to Generate Queries Faster with AI

Today's business landscape positions data as a critical asset, crucial for making informed decisions in any business. The evolution of artificial intelligence (AI) has significantly simplified the process of generating SQL queries, a key task for data professionals.

This article explains how utilizing ChatGPT for BigQuery is transforming your SQL query development, data organization, modeling data, and retrieval methods. 

By integrating AI tools and methods into day-to-day data workflow, users gain a brand new approach to managing and querying large datasets in data warehouses, paving the way for innovative data management strategies.

If you prefer to watch the video instead of reading the article - we got you covered as well:

Applying AI to the process of generating SQL queries marks a major milestone in managing data workflows.

Tools like ChatGPT drastically cut down the complexity and save a lot of time traditionally required for drafting and fine-tuning SQL code. This shift allows a broader spectrum of professionals - from data analysts and engineers to business intelligence teams, product managers, and marketers - to dedicate more effort to data analysis rather than learn the details of database queries or wait for analytics resources available.

The biggest advantage of this new AI approach is making data accessible for everyone no matter their expertise or coding experience.

AI-enhanced SQL query builder tools enable individuals without a technical background to craft complex queries with ease, ensuring insights are accessible without the bottleneck of waiting for analytic specialists. This feature is invaluable in dynamic business settings where swift, informed decisions are key to maintaining a competitive edge.

Comparing ChatGPT with traditional human-driven SQL writing highlights a blend of strengths and challenges.

Humans are great at applying critical thinking, specific industry insights, as well as a light fleur of creativity though their manual query crafting can be time-consuming and error-prone.

ChatGPT by OpenAI as well as most of the other LLM Models, contrastingly, offer speed and a relatively good level of accuracy, delivering queries faster, 2x, 10x, 100x faster.

Nonetheless, AI tools need precise instructions and may not fully capture the nuanced requirements of complex business scenarios.

The landscape of data analysis is evolving in 2024 with the integration of AI tools like ChatGPT, offering new strengths and encountering different limitations when compared to human data analysts.

Now let’s critically analyze the pros and cons of ChatGPT and human data analysts to get a better understanding of the future of SQL writing and data analysis:

The intersection of AI and human expertise in SQL query generation is indeed a promising development in the field of Data Analysis. Here are some of the key strengths of ChatGPT:

✅ Speed: ChatGPT can generate complex SQL queries quickly, enabling rapid data retrieval and analysis.

✅ Consistency: AI tools do not suffer from fatigue, ensuring consistent performance over extended periods.

✅ Error Reduction: ChatGPT minimizes syntax errors due to its programming, leading to more accurate query generation.

✅ Comments: For ChatGPT it’s very easy to comment the code. Whether you need to enclose a detailed description of the whole query, or you need to comment on every line - an AI tool would handle this quickly and effectively.

While ChatGPT is a powerful tool with broad applications, it's not without its limitations.

Its capabilities are constrained by the data it was trained on.

Additionally, while it can generate and suggest edits to SQL queries, for example, it can't directly interact with databases (unless it’s a FREE SQL Copilot for BigQuery) or verify the success of a query against actual data (well, the SQL Generator mentioned above could do this as well).

Its understanding is purely based on the information provided in the prompt, so clear, detailed prompts are essential for generating accurate responses.

⚠️ Lack of Context: While proficient in syntax, ChatGPT may lack the deeper understanding of business contexts and objectives that human analysts possess.

⚠️ Creativity: ChatGPT generates queries based on existing data and patterns, potentially missing out on innovative approaches a human might take.

⚠️ AI Hallucinations: Like all other generative AI-based tools, it may generate outputs that are incorrect and unrelated to the request.

Human data analysts play an indispensable role in the landscape of data analysis, bringing to the table a unique set of strengths that complement the capabilities of automated tools and AI like ChatGPT.

✅ Creative Problem Solving: Humans excel in thinking outside the box, and developing new strategies for data analysis and interpretation.

✅ Adaptability: Human analysts can quickly pivot their approach based on subtle shifts in business goals or data nuances.

✅ Strong Context Knowledge: Analysts bring a comprehensive understanding of business contexts, enabling them to tailor queries more effectively.

Human data analysts offer unique insights and creativity that are invaluable. However, they also have their limitations.

Many of these limitations come from their human qualities, like getting tired, having varying levels of performance, and taking a lot of time to analyze data.

⚠️ Time: Humans typically require more time to write and test SQL queries compared to AI.

⚠️ Consistency: Susceptibility to fatigue and error can lead to inconsistencies in query generation and analysis.

For experienced data analysts, ChatGPT can be a real game-changer, saving a lot of time.

Rather than building every query by hand, they can use ChatGPT to kick things off, especially for the routine or common ones.

By automating routine query generation, ChatGPT enables analysts to dedicate more time to complex and strategic analysis tasks.

It's also great for sparking new ideas or when diving into new kinds of queries.

ChatGPT can take care of the basic tasks, freeing up these pros to focus on deeper analysis, fine-tuning, and making strategic calls.

For those new to data analysis, ChatGPT is an incredibly useful tool for learning. Beginners can check their SQL queries with ChatGPT for accuracy or explore different ways to structure them. When faced with new types of requests, ChatGPT can help them figure out possible query designs.

By comparing their methods with ChatGPT's suggestions, newcomers can sharpen their skills, learn best practices, and build confidence in their SQL writing. Although ChatGPT can't fully replace the depth of knowledge and contextual insight of human analysts, it's a great support. It helps streamline tasks for seasoned analysts and provides guidance for those just starting out, making it an essential tool in the world of SQL.

Incorporating ChatGPT into your SQL query creation process can markedly improve both the speed and precision of your work, regardless of whether you're a beginner looking to grasp SQL basics or a seasoned professional seeking to refine your workflow.

We are going to be using SQL Copilot for BigQuery by OWOX.

Why? Because it has a direct BigQuery to GPT integration via Oauth, so you can fetch the table schema before generating your SQL code.

It’s available for all GPT Users. No matter whether you’re on the free or paid version - you can hire it to fasten the query generation process.

So let’s start with generating a new SQL Query.

Before diving into query writing, let’s share the table schemas with ChatGPT. Because, understanding the structure, relationships, and types of data you're working with helps generate more accurate queries.

Let’s determine the tables that contain the information you'd require to generate the SQL query.

Provide your BigQuery project ID, dataset ID, and table ID in the following format: project.dataset.table

For example, if your project ID is "myproject", your dataset ID is "sales_data", and your table ID is "transactions", provide the following information: Myproject.sales_data.transactions

We’ll list all of the tables in our demo dataset, and allow access to the table schemas:

If you need to add more tables to ChatGPT so we use them in the query - you can connect as many tables as you need.

Note: If you don’t want to connect BigQuery to ChatGPT - you can just share the table schema by uploading a screenshot of it, or just simply by using text, for example: "I have a BigQuery dataset with 'sales' (sale_id, product_name, sale_amount, sale_date) and 'customers' (customer_id, name, email). I need a query to analyze monthly sales per product."

We would go even further and ask this AI SQL Copilot to visualize the database relationships, find JOIN keys, and visualize the database structure for us.

And here is the database relationship map that ChatGPT created for us. In our case, as this dataset is pretty straightforward, all of the primary and foreign keys have been discovered correctly. But if you want to share additional context at this step - it’s exactly the right time.

Let’s say we want to analyze the product sales performance.The ideal way to use GPT here would be"I want to analyze the Sales Performance by Product.Which products are performing well and contributing the most to revenue?

Please write a SQL query to show me what are the total sales and quantities sold for each product?”

Here is the response for the same prompt:

Here is an explanation of the query:

1. SELECT: Specifies the columns to be included in the result set.
   p.product_id: The ID of the product.
   p.name AS product_name: The name of the product.
   SUM(rol.qty) AS total_quantity_sold: The total quantity of the product sold.
   SUM(rol.price * rol.qty) AS total_sales: The total sales amount for the product.
2. FROM: Specifies the table from which to retrieve the data:
   retail_orders_line (aliased as rol): Contains order line details.
3. JOIN: Combines rows from products (aliased as p) with retail_orders_line based on the product_id.
4. GROUP BY: Groups the results by product_id and product_name.
5. ORDER BY: Orders the results by total_sales in descending order to show the top-performing products first.

You can use this query to analyze which products are performing well and contributing the most to revenue.

This SQL Copilot also asks us whether we want to proceed with validating this query, or whether we have any modifications or additional criteria in mind before doing so. Our query looks actionable, so let’s validate it.

To get this data directly into Google Sheets and have it automatically updated, you can use this BigQuery extension.

We’ll run our query right from the Google Sheets.

Now we apply filters to adjust the data retrieval without editing SQL.

For example, we’d like to see only those products, which were sold over 60 times.

We’ll add a filter to the total_quantity_sold column, select Greater than, specify 60, and click on ‘Run’. Here is what the result looks like:

Below are strategies to maximize the benefits of using ChatGPT for your SQL-related activities:

For example, You're a data analyst working with a retail company's sales database in BigQuery.

Your task is to analyze sales trends over time, requiring a detailed understanding of both the sales and customer data structures to produce accurate monthly sales reports by product.

The ideal way to use GPT here would be to share access to the tables, where the products, transactions, and customer details are stored, as well as the other tables that you might need to use in the report.

Focus on simplifying your SQL problem.

Be specific about constraints, desired outputs, filters, ordering, and aggregations. This clarity helps in generating more accurate queries.

Suppose, You're preparing for a monthly sales meeting. To present the latest trends, you need a quick way to pull data on the most recent month's product sales, focusing on identifying the best-selling products.

You can try the following way to write a prompt for a similar scenario - "Create a SQL query to list all products sold in the last month, including total sales amount, sorted by the sale amount in descending order."

Being explicit about your needs and the structure of your desired output ensures that the generated queries meet your expectations.

Imagine that, at the end of the year, your marketing team is planning a campaign targeted at your top customers. Your task is to identify these customers based on their spending in 2024, requiring a precise and concise query that sorts customers by their total spending.

You may want to write like - "I need a BigQuery query that identifies the top 20 customers by total spend in 2024, including customer ID, name, and total spend."

Don’t expect the first query to be perfect. Use the process as a way to fine-tune your request based on the initial output, making necessary adjustments for optimization.

Suppose, after running your initial query to identify top customers, you realize that the promotional sales might distort the true spending picture. The marketing team asks you to adjust the query to exclude these sales for a more accurate analysis of customer spending behavior.

So you can write the following on top of your existing query - "Based on the previous query, how can I adjust it to exclude sales from promotional items listed in the 'promotions' table?"

Learn these essential BigQuery functions to boost your data analysis skills. These key functions will streamline your workflows and deliver more accurate results.

• String Functions:  It helps in manipulating and formatting text data, allowing for operations like substring extraction and pattern matching.
• Aggregate Functions: These functions perform calculations on a set of values and return a single result, such as SUM, COUNT, AVG, MAX, and MIN.
• Conditional Expressions: Allows for the execution of different actions based on specified conditions, such as CASE and IF statements.
• Navigation Functions: It enables you to access data from subsequent or preceding rows without performing self-joins, such as LEAD and LAG.
• Date Functions:  Date functions handle operations on date data types, allowing for calculations and formatting of dates.
• Numbering Functions:  Provides unique numbers to rows within a partition of a result set, such as ROW_NUMBER, RANK, and DENSE_RANK.
• Conversion Functions: Conversion functions are used to convert data from one type to another, ensuring compatibility and correct data formatting.

1. Efficiency and Speed: Utilizing ChatGPT for SQL query generation significantly accelerates the process, reducing the time traditionally required for drafting and fine-tuning SQL code. This allows data professionals to focus more on data analysis rather than learning and writing complex queries.
2. Accessibility for All Skill Levels: AI-enhanced SQL query builders enable individuals without a technical background to create complex queries with ease. This democratizes access to data insights, eliminating the bottleneck of waiting for specialized analytics resources.
3. Consistency and Error Reduction: ChatGPT provides consistent performance without fatigue, minimizing syntax errors and ensuring accurate query generation. This reliability is crucial in maintaining data integrity and quality.
4. Enhanced Collaboration: By integrating AI tools like ChatGPT, teams comprising data analysts, engineers, product managers, and marketers can collaborate more effectively. The AI facilitates rapid prototyping and iteration of queries, fostering a more agile and responsive data-driven environment.
5. Support for Both Novices and Experts: ChatGPT serves as a valuable learning tool for beginners, offering guidance and validation for their SQL queries. For experienced analysts, it acts as a time-saving assistant, automating routine query generation and allowing them to focus on more strategic and complex analysis tasks.

To further enhance your SQL query generation and streamline your reporting workflow, consider using the BigQuery extension for Google Sheets.

This tool allows you to integrate and manage your queries directly within Google Sheets, providing a seamless and efficient data analysis experience.