SQL for Data Analytics · AI in an analysis
AI in an analysis
Sometimes an AI asks you questions before it runs a query. In this activity, it asks three. You answer them before you've seen the table, and then you see what was in it.
Try it
You do data analysis for a small coffee chain with four stores. You asked an AI to find the best-selling item last month, using the chain's sales table. You haven't looked at the table yet.
Answer the AI's three questions.
What was the best-selling item last month?
Let me take a look at the sales table first.
I've looked through it. Before I run anything, there are a few things that will change the answer, and I need your call on them:
What the data shows
A sample of 5 rows from the sales table
The full table would have a row for every sale last month.
| sale_date | store | item | price | quantity | note |
|---|
What the owner has told you about the shop
“Our oat latte is its own drink. It's $5.75, and a regular latte is $5.00. And Store 4 only opened on the 22nd.”
The AI wasn't there when the owner told you this. It only has the sales table.
The right calls
The best-selling item last month
In the coffee chain activity, you probably weren't sure of your answers. What did you need that you didn't have?
How I'd answer it
The data, and a few facts about the business. Some of it was in the sales table. The note column marks the refunds, and the rows show latte selling at Latte's price. The rest wasn't. Only the owner could tell you that Latte (oat) is its own drink and that Store 4 opened on the 22nd.
The AI's three questions in the coffee chain activity were good ones. Any analysis runs into questions like them, about rows that look odd, values that might be the same thing, and parts of the data that look incomplete.
"Use your best judgment" hands those decisions to the AI. The AI has the sales table. It wasn't there when the owner told you about the oat latte and Store 4. Its guesses were reasonable ones. Latte, latte, and Latte (oat) could easily be one drink, and a store with sales on only 9 days looks like missing data. With refunds subtracted, Latte and latte combined, and Store 4 included, Cold brew sold the most, 1,440 units. The AI's two wrong guesses made it Latte, with 1,670.
The most important thing when you work with AI on data is understanding the data yourself, because that's what lets you answer the AI's questions and check the decisions it makes on its own.
The widget below takes an analysis one step at a time, six steps in all. Click through them to see how you'd direct an AI in each one, and what stays your job. The examples use the coffee chain from the activity, where the owner has also asked which store is doing best.
Click through
Explore the data
The AI can give you queries to run, or run them itself, but you generally have to tell it what you want done. There are a lot of ways to explore a table. If you don't know what would be useful, you can't direct the AI well, so you need to understand the things you're asking it for.
Prompts that direct it
How many rows have a missing value in each column of the sales table?
List the different values in the item column, with how many rows each one has.
What are the earliest and latest dates in the sale_date column?
Show me ten rows of the sales table. What does one row stand for?
What stays yours: deciding what to look at, and understanding what comes back.
Break the request down
Come to your own understanding of the request before you let the AI guess at it, so it doesn't do your thinking for you. You may know something that matters, and if you think about it for a minute, you may find a way to frame the question that the AI wouldn't. If you let it come up with the questions and keep picking from its suggestions, you lose your say in what it builds, and you'll often get something generic. Make sure it's working from your understanding of the request, and think through any question you take from it so you know it's the right one.
What stays yours: deciding what the request is asking, and which questions answer it.
Write down what's ambiguous, and what you assumed
You need to understand the data to see what's ambiguous. The AI tends to lean toward resolving ambiguity, which makes sense, because its job is to answer the question. As we covered in Unit 01, part of the job is getting from a vague request down to a specific question the data can answer. That means making choices, like whether "best" means units sold or money taken in. So find the full range of what the request could mean, and choose how it gets narrowed yourself.
What stays yours: choosing how the question gets narrowed, and writing down what you chose.
Write the queries
The AI can write most of the queries, but you run them yourself and look at what comes back. The danger is usually a query that runs fine and doesn't quite do what you wanted, or one that misses something about the business. So check each result against what you expected, and know where every number comes from.
Prompts that direct it
Write a query that shows units sold per item last month, highest first. Subtract the refunds, and count Latte and latte as one item.
Write a query that shows how much money each item took in last month, highest first, with refunds subtracted.
Write a query that shows cold brew sold at each store last month, divided by the number of days that store was open. Store 4 opened on the 22nd.
What stays yours: running each query, and knowing where every number in the results comes from.
Present it
The AI can draft a write-up, but you're the one reporting the results to someone. You have to be able to talk about them and know what the write-up says. There are many ways to show the same data, and they can make it look very different and say different things about it. Choosing what to show is where the AI's judgment tends to be weakest. It'll show something that's true and works, but it has a hard time judging what's valuable to show, and what represents the data well for the person asking.
Both charts use cold brew sales from the coffee chain's sales table, the one the AI was working from in the activity at the start of this lesson. You only saw a few rows of it there. The numbers are in units, with refunds subtracted. Stores 1 to 3 were open all 30 days last month. Store 4 opened on the 22nd, so it was open for 9.
Cold brew sold per store
Cold brew sold per day open
What stays yours: choosing what to show, and knowing every number in the write-up well enough to explain it.
Name the limitations
The AI can list limitations, but it sees them differently than you do. The ones that matter most come from what you know about the data, especially how it was collected, and the AI usually doesn't know that.
What stays yours: knowing how the data was collected, and what that means for the results.
Say you hire a contractor to work on your house, and you have no experience with any of it. All you can ask for is something very high level. The contractor makes all the underlying decisions. You can't check the work, and you can't tell if it's bad. You also don't know whether it makes sense for the rest of the house, because each contractor specializes in their own area.
So most people hire a general contractor, someone who manages the other contractors.
In the role you're training for, whether that's data analyst or software developer, you're the general contractor. That's the job.
A general contractor can't check a subcontractor's work if they have no idea how it works. They might not be able to do every job themselves. But they need a clear mental model of how it all fits together, and the way to get one is to do some of the work. A general contractor usually knows how plumbing works. They're probably experienced in some areas, and that experience transfers to the others. Doing the work is how you know what good work looks like.
What matters less now is fixing 500 pipes by hand until you're a master pipe-fixer, because AI will do the pipe fixing. What matters is having done it a few times, so you know what can go wrong and what it looks like when it does. Still, the person who has done it 500 times is probably the better person to oversee the job.
So in this class, you write the SQL yourself, even though AI can write most of these queries. Writing it is how you learn how data is structured, how it moves, and how it fits together. Those skills get more concrete in the second half of the course, when you design databases and decide how data gets tracked. They're the base skills for the much more involved conversations you'll have with AI later.
These are just for you. They aren't graded or turned in.
An AI drafts the monthly report for a chain of shops. One line says "The Downtown store's sales dropped 40% in August." What would you check before that line goes in the report?
How I'd answer it
First, what the drop is measured against. It's probably July, but it could be August of last year, or the average of the other months, and each one can give a different percentage.
Then whether the store was open the whole month, in both months. If Downtown closed for a week in August for repairs, August's total covers fewer days than July's. Comparing sales per day open would show whether the store sold less on the days it was open, or was just open for fewer days.
The data might not tell you any of this. A week with no sales could be a closure or a gap in the data. Before the line goes in, ask someone who knows the business, like the store's manager.
An AI writes a query that joins Customers to Orders to show how many orders each Northwind customer has placed. What do you need to know about those two tables to check the query?
How I'd answer it
How they connect, and what one row is after the join. One customer can have many orders, so after the JOIN, one row is one order. A plain JOIN also drops the 17 customers who have never placed an order. If the request means every customer, the query needs a LEFT JOIN. With a LEFT JOIN, COUNT(*) gives those customers a 1, so the query has to count Orders.OrderID instead, which gives them a 0.