SQL for Data Analytics · AI in an analysis

AI in an analysis

Questions you can't answer

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:

Answer all three to go on.
What went wrong

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.

AI in each step

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.

1 of 6
Why you're still learning SQL

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.

Pause and answer

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.