Feeling stuck waiting forever for your team to sort out your data reports or SQL queries?
Have you ever tried to handle it yourself, only to find your SQL skills aren't quite up to speed? Maybe you spent a whole hour grappling with just one query.
But what if I told you there was a data expert at your service 24/7, ready with answers in less than a minute?
By using ChatGPT the right way, you can own an AI-driven Data Expert set to rewrite the rules of the game.
But to transform ChatGPT into a world-class Data Analyst, there are some guidelines to follow.
Keep reading to discover the best method to put AI to work, giving your SQL queries a power boost.
First, let me share with you a real-life example:
Last Sunday, Charles, lempire's COO was preparing a Monthly Performance Report for the team when he decided to include an analysis tracking the growth of Free Trial users and new customers per product.
Charles jumped into BigQuery and found raw data that seemed promising. As he began crafting his own SQL query, he realized this task could potentially take an hour, given that he hadn't practiced SQL in a while.
That's when he chose to call upon AI, more specifically, ChatGPT, to save time and energy.
Charles knew that for ChatGPT to produce worthwhile results, he had to create a precise and clear prompt.
I’ll show you how he did it, so you can replicate this to your advantage.
Here's the first prompt:
How do you craft a prompt like this one?
1. Role: Start by telling ChatGPT the role and job expected to fulfill. Make sure to mention if you're using BigQuery, so the AI creates a query perfectly suited for BigQuery's unique SQL structure.
2. Context: Give as much background info as you can to help the AI understand what you need:
a) Table name: I shared the exact name of the table to eliminate the need for manual replacement.
b) Table explanation: Describe the table's layout and make clear what each part of the schema means. Since ChatGPT might not understand what each column signifies, it's essential to explain it as if you're teaching a new team member.
c) Table schema: Provide the table schema so that ChatGPT can reference the correct column names.
d) Filtering values: Mention the possible values to filter (like for 'FreeTrial') to assist the AI in crafting a more accurate query.
3. Task: Give ChatGPT a clear job, describing exactly what you want with as much detail as possible.
4. Format: Ask the AI to use a specific style for the query.
To simplify it, you can copy this prompt here 👇
You just need to fill it in according to your needs!
{{copy1}}
These are the type of results you can get from using this prompt:
{{copy2}}
After you get the SQL query generated by ChatGPT, you'll be pleased to find that it works perfectly, meeting all of the requirements you made on the prompt.
Do you see an error window popping up? No need to worry!
Just copy your error, and ChatGPT will fix it for you. It'll even say sorry for the hiccup! 😅
Amazing, right?
ChatGPT's capabilities extend beyond simple queries. You can use it for more complex queries, like "Joins".
You can use ChatGPT for such tasks multiple times. As long as you take the time to explain the background and meaning of the schemas, it will manage to accomplish the job.
If you or your team find it hard to use ChatGPT for SQL tasks, it’s often because your prompts are missing key elements. Making sure your prompts are clear and detailed is key to getting the best results from ChatGPT.
Key Takeaways
To wrap up, ChatGPT is an invaluable resource in generating the SQL query you need. By investing just 5 minutes in crafting clear and specific prompts, you can get a ready-to-use SQL query that works without a hitch.
✅ Use ChatGPT as your data expert to get fast SQL query answers.
✅ Explain your table layout and schema to ChatGPT, just like teaching a new team member.
✅ Give ChatGPT a clear job, telling it what you want in detail.
✅ If errors pop up, tell them to ChatGPT to get them fixed.
This shows how AI, especially ChatGPT, can change the way we analyze data and make queries faster, saving you both time and energy.
If you liked this article 👉 sharing is caring
If you want to give feedback or simply send a love message 👉 you can e-mail me at guillaume@growth-elite.com
If you like this ChatGPT hack, make sure to subscribe to our Growth Elite newsletter for more 👇
Peace, Love & AI ✌️
You are a seasoned Data Analyst with an in-depth understanding of SQL and a skilled user of BigQuery, a popular data warehouse software. Your task is to aid me in formulating a SQL query that is both optimized and concise, and can be used directly within Tableau, a data analytics platform.
For this task, we will be working with the table {INSERT YOUR TABLE NAME HERE}. Each row in this table represents {DESCRIBE WHAT EACH ROW IN THE TABLE REPRESENTS}. This table contains the following variables:
1. {VARIABLE 1 NAME}, {VARIABLE 1 TYPE}, {BRIEF DESCRIPTION OF VARIABLE 1}
2. {VARIABLE 2 NAME}, {VARIABLE 2 TYPE}, {BRIEF DESCRIPTION OF VARIABLE 2}
3. {VARIABLE 3 NAME}, {VARIABLE 3 TYPE}, {BRIEF DESCRIPTION OF VARIABLE 3}
4. {VARIABLE 4 NAME}, {VARIABLE 4 TYPE}, {BRIEF DESCRIPTION OF VARIABLE 4}
For example, if we are working with a table called orders, we could have:
1. OrderID, integer, This is the unique identifier for each order placed in our platform
2. CustomerID, integer, This identifies the unique customer who placed the order
3. ProductType, varchar, This is the category of each product ordered
4. OrderDate, datetime, The date and time when the order was placed
Variable {VARIABLE 1 NAME} can take the following values:
* Value 1, meaning {EXPLAIN THE MEANING OF VALUE 1}
* Value 2, meaning {EXPLAIN THE MEANING OF VALUE 2}
* Value 3, meaning {EXPLAIN THE MEANING OF VALUE 3}
For example Variable ProductType can take the following values:
* Type1, meaning Shoes
* Type2, meaning Bags
* Type3, meaning Accessories
In terms of the desired SQL query, here is an example of what the query needs to achieve (semantically):
* {EXPLAIN WHAT THE QUERY SHOULD DO}
For example The SQL query should retrieve all orders placed by a specific customer (CustomerID = 123) in the last 30 days. The result should include details about each order such as the OrderID, the ProductType, and the date of the order (OrderDate).
Finally, outline the required format for the query's outcome.
1. {ADD YOUR DESIRED OUTCOME FORMAT HERE}
2. {ADD YOUR DESIRED OUTCOME FORMAT HERE}
3. {ADD YOUR DESIRED OUTCOME FORMAT HERE}
For example, your desired outcome format may include the following points:
1. The result should be sorted by OrderDate in descending order, so the most recent orders appear at the top.
2. Only the OrderID, ProductType, and OrderDate should be displayed in the result.
3. Dates should be on the following format: DD-MM-YYYY
This SQL query should meet the following requirements:
1. It should be as optimized as possible for efficient execution.
2. It should avoid unnecessary complexity and remain concise.
3. It should include comments for each major step to ensure readability and ease of understanding.
If necessary, incorporate JOIN operations (such as INNER JOIN, LEFT JOIN), as well as FILTERS or WHERE clauses, based on the data analysis requirements. For instance, you might need to join the Orders table with the Customers table to get more information about the customer who placed the order.
- - This query calculates the number of new free trials and new customers per calendar month by product.
WITH
- - Extract year and month from event_date and create a date representing the last day of the month
preprocessed_data AS (
SELECT
customer_id,
DATE_TRUNC(event_date, MONTH) + INTERVAL "1" MONTH - INTERVAL "1" DAY AS calendar_month,
event,
product_name
FROM
`datawarehouse-prod-381508.prod_analytics.user_journey_lempire`
),
- - Aggregate new free trial events per calendar month by product
new_trials AS (
SELECT
calendar_month,
product_name,
COUNT(*) AS number_of_new_trials
FROM
preprocessed_data
WHERE
event = 'Freetrial'
GROUP BY
calendar_month,
product_name
),
- - Aggregate new customer events per calendar month by product
new_customers AS (
SELECT
calendar_month,
product_name,
COUNT(*) AS number_of_new_customers
FROM
preprocessed_data
WHERE
event = 'New'
GROUP BY
calendar_month,
product_name
)
- - Combine new free trial and new customer counts into the final result
SELECT
COALESCE(nt.calendar_month, nc.calendar_month) AS calendar_month,
COALESCE(nt.product_name, nc.product_name) AS product_name,
IFNULL(nt.number_of_new_trials, 0) AS number_of_new_trials,
IFNULL(nc.number_of_new_customers, 0) AS number_of_new_customers
FROM
new_trials nt
FULL OUTER JOIN
new_customers nc
ON
nt.calendar_month = nc.calendar_month
AND nt.product_name = nc.product_name
ORDER BY
calendar_month,
product_name
;
.png)
