SQL for Data Analytics · What the query misses
What the query misses
AI tools are good at writing SQL now. Give one a clear question and a table, and the query it writes will usually run and be correct.
So if the query is right, why does anyone need to look at it?
How I'd answer it
Because a query can be correct and still give the wrong answer to what somebody asked. The answer might depend on something about the business that isn't in the table, answer a question the data can't, or rest on a decision made without saying so.
Try it
You do data analysis for a small online shop that sells art prints. The owner started it two years ago and orders the prints from a local printer. The shop only ships to the US and Europe. Customers in Europe check out in euros, and customers in the US pay in dollars. When a print arrives damaged, the shop refunds the whole order rather than send a new one. Below is a sample of its orders table.
You asked an AI five questions about these orders. For each answer, decide whether you'd act on it or send it back.
orders
| OrderID | Customer | Region | Status | Amount |
|---|
In the print shop activity, three of the AI's five answers had a problem, and none of them was bad math. What went wrong in each one?
How I'd answer it
Answer 1 added dollars and euros together, and nothing in the table says the EU orders are in euros. Answer 3 answered a question about refunds that the data can't answer, because the orders table has no dates. Answer 5 dropped a row in its WHERE clause and didn't say so.
Every query in the print shop activity ran, and every number was computed correctly. The answers with problems went wrong in three different ways.
Something about the business that isn't in the table. Nothing in the orders table says EU orders are in euros. The shop's description told you. Businesses have lots of facts like this that no table records, such as test accounts, a product that got renamed, or a store that was closed for a month.
A question the data can't answer. "Are refunds getting worse?" is about change over time, and the orders table has no dates. The AI answered a question close to the one you asked and put a number in it, which is easy to miss.
A decision made without saying so. Answers 4 and 5 both left out order 7, which has no region. Answer 4 said so. Answer 5 didn't, and you only find out by reading the query. Catching decisions like this one is a big part of why this class teaches you to read SQL.
Key idea: Checking an answer means asking three things. Does it depend on something about the business that isn't in the table? Can the data answer the question that was asked? Did it make a decision without saying so, and do you agree with it?
Some of the hardest problems come from what isn't in a table at all. This activity is based on a real problem from the Second World War.
Try it
The diagram shows every hit recorded on the 120 bombers that came back last month. You have armor for two zones. Click two, then send out the next wave.
Once you've sent out a wave in the bomber activity, look at the grey dots. What could the print shop's orders table be missing in the same way?
How I'd answer it
In the bomber activity, every recorded hit came from a plane that made it back. A table can leave things out the same way. The print shop's orders table only has orders, so everyone in it bought something. Anyone who visited the site and left without buying isn't in it, so no query on that table can tell you why people don't buy.
You find this kind of problem by asking how a row gets into the table in the first place, and what kind of row never would.
These are just for you. They aren't graded or turned in.
The bomber activity had no AI in it. How is it relevant to working with AI?
How I'd answer it
An AI works from what's in front of it: the tables, the query results, and whatever you've told it. In the bomber activity, every recorded hit came from a plane that made it back. An AI given that data will draw its conclusions from those planes and usually won't ask about the ones that didn't return unless someone points them out. The longer you work with it on one problem, the more it builds on what's already in the conversation, so a gap like this gets easier to miss. Asking how a row gets into the table, and what kind of row never would, is a question you have to bring yourself.
Your manager asks for "the number of customers we had last year." Name two things you'd want to know before writing the query.
How I'd answer it
A few good ones. Does "customers" mean anyone who placed an order, or only people who paid? Does last year mean the calendar year or the last twelve months? Would someone who ordered twice count once? Each of these changes the number, and none of them is in the request.