- Lab
-
Libraries: If you want this lab, consider one of these libraries.
- AI
Claude for Querying Data
Turn natural language questions into accurate, optimized SQL by using Claude. In this guided, hands-on lab you will practice prompt engineering, multi-step analytical queries (filters, joins, aggregations, window functions), cross-dialect generation, performance analysis, query documentation, and risk mitigation, so you can work faster and more confidently as a data analyst or engineer.
Lab Info
Table of Contents
-
Challenge
Introduction
Welcome to the Claude for Querying Data lab. You're a sales analyst at a retail chain with 12 stores across the United States. Your team answers questions about revenue every week, and you want Claude to help you write, check, speed up and document the SQL behind those answers.
In this lab, you'll use the Claude API from a Jupyter notebook to:
- Turn business questions into correct SQL by giving Claude your schema and business rules
- Write the same query for PostgreSQL, SQLite, MySQL and Oracle, and translate a legacy Oracle query
- Use CTEs, window functions and recursive queries, and rewrite a slow query
- Read execution plans with Claude, add the indexes it suggests and measure the difference
- Document and parameterize a query, block unsafe SQL and catch a query that's plausible but wrong
The sales database
The database covers 2024 and 2025 and holds six tables:
| Table | Rows | What it holds | |---|---|---| |
stores| 12 | Each store's name, city, state and region (East, Central, South or West) | |categories| 45 | A three-level product hierarchy: departments, categories and subcategories | |products| 184 | Each product's name, category and list price | |customers| 20,000 | Names, locations and loyalty tiers (some customers have no tier) | |orders| 100,000 | Order date, store, channel (online or in store) and status; guest orders have no customer | |order_items| 300,000 | Each line of an order: product, quantity, unit price before any discount, and the discount |You'll run queries in two engines: PostgreSQL 16 and SQLite 3.51. The lab has no MySQL or Oracle servers, so you'll check SQL for those dialects without running it.
Before you start
The lab notebook is
ClaudeForQueryingData.ipynb. Open it in Jupyter and check that the kernel shown in the top-right corner is Python 3 (Claude for Querying Data). To run a cell, select it and press Shift+Enter. Run the cells in order, from top to bottom.You'll write only SQL and plain-English prompt text. The notebook provides all the Python, and each TODO is a SQL query, part of a query or a few lines of instructions for Claude. You'll read some of the Python to see how the notebook talks to Claude, but you won't need to change it.
If you get stuck,
ClaudeForQueryingData-Solutions.ipynbholds the completed SQL and prompts for every task.Keep two things in mind as you work:
- Compare results, not SQL text. Claude can write the same query in several valid ways, so its SQL may not match the examples in this lab, and neither may yours. Each task's Observed Completion shows the expected output. The result tables should match even when the SQL doesn't.
- You have 20,000 tokens for this lab. Each call to Claude uses tokens, and
budget()shows how many you've used. Re-running a cell you haven't changed returns the saved answer and uses no tokens. A cell that uses a saved answer prints[saved response: no tokens used]first. Three cells that you only run show answers saved when the lab was built, and they're marked in the notebook. The solutions notebook's answers are saved too, so running it uses almost no tokens. Any change to a request makes a live call.
-
Challenge
Step 2: Set up Claude and the sales database
In this step, you'll connect to Claude through the lab's proxy, see how the notebook reads Claude's reply, and write your first query against the sales data.
The lab's helper functions
The lab's kernel loads a set of helper functions for you, so the notebook needs no imports. You'll meet them as you go:
lab_client(api_key)returns ananthropicclient. It saves every response, so re-running an unchanged cell is free, and it counts the tokens you use.budget()prints the tokens your last call used, your total so far and how many are left.compact_schema()describes the tables in a few short lines you can send to Claude.run_sql(sql)runs a query and shows the result as a table. It connects to PostgreSQL as a user that can only read data; from Step 3 onward it runs SQL that Claude wrote.
Choosing the model
The notebook names the model once, in
MODEL:MODEL = "claude-haiku-4-5-20251001"Every call in the lab uses
MODEL, so trying another model means changing that one line.Note: If you've taken Claude API Fundamentals, you may expect to set
temperature. This lab uses version 2 of theanthropicPython SDK, which follows API version 2026-10-21. That version removed thetemperature,top_pandtop_ksettings, so Claude runs with its defaults. Clear instructions are what make its answers consistent, and you'll write those in Step 3.Reading Claude's reply
A response from Claude holds a list of content blocks in
response.content. Today, Claude Haiku 4.5 returns a single text block. Newer models can return other kinds of blocks first, such as athinkingblock. The notebook'sget_text(response)function joins the text of every block whose type istext, so it keeps working whichever model you choose.
Task 2.1: Connect to Claude
In the Task 2.1 cell of the lab notebook:
- Replace
<TODO_ENTER_API_KEY_HERE>with the API key shown on the lab page. - Read
MODELandget_text(), then run the cell. - Run the next cell to send a short test message to Claude.
Observed Completion
You should see the following output below the test cell. The test message always makes a live call, so a wrong key shows up here. Your session total grows each time you run it.ready Last call: 18 tokens (14 in, 4 out) | Session: 18 of 20,000 | Left: 19,982
Exploring the data
Before you ask Claude anything, look at the data yourself.
compact_schema()lists each table's columns, keys and allowed values in one line per table. It's the same description you'll send to Claude in Step 3, kept short to save tokens.The schema shows that every order has a
status:completed,returnedorcancelled. Only completed orders count toward revenue, so it's worth knowing how many orders have each status.In
order_items, the schema describesunit_priceas the price charged. That's the price before the discount indiscount_pct, which is applied separately when you calculate revenue.
Task 2.2: Explore the sales data
In the lab notebook:
- Run the cell below Task 2.1's test cell to print the compact schema.
- Run the next cell to see the first five orders.
- In the Task 2.2 cell, replace the TODO comment in
ORDERS_BY_STATUS_SQLwith a query that counts the orders for each status. Returnstatusandorder_count, with the most orders first. - Run the cell.
Hint
Group the `orders` table by `status`, count the rows with `COUNT(*) AS order_count`, and sort with `ORDER BY order_count DESC`.Solution
SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status ORDER BY order_count DESCObserved Completion
You should see the schema:stores(store_id PK, store_name, city, state, region 'East'|'Central'|'South'|'West', opened_date) categories(category_id PK, category_name, parent_category_id FK categories, level 1=department|2=category|3=subcategory) products(product_id PK, product_name, category_id FK categories, list_price) customers(customer_id PK, first_name, last_name, city, state, country, loyalty_tier 'Bronze'|'Silver'|'Gold'|'Platinum'|NULL, signup_date) orders(order_id PK, customer_id FK customers NULL for guest orders, store_id FK stores, order_date, channel 'online'|'in_store', status 'completed'|'returned'|'cancelled') order_items(order_item_id PK, order_id FK orders, product_id FK products, quantity, unit_price (price charged), discount_pct (0.10 = 10% off))Then the first five orders. Order 4 is a guest order, so its
customer_idis<NA>:order_id customer_id store_id order_date channel status 0 1 19402 10 2024-01-01 in_store completed 1 2 6289 7 2024-01-01 online returned 2 3 7577 10 2024-01-01 online completed 3 4 <NA> 10 2024-01-01 online cancelled 4 5 13983 11 2024-01-01 online completedAnd the order counts. About 9% of orders were returned or cancelled, so a revenue query that forgets the status filter overstates revenue:
status order_count 0 completed 90930 1 returned 6057 2 cancelled 3013 -
Challenge
Step 3: Translate business questions into SQL
In this step, you'll see what goes wrong when Claude writes SQL without knowing your database, then give it the schema and business rules it needs, and check its answer against a query you trust.
Why Claude needs your schema
Claude knows SQL, but it doesn't know your tables. Asked for "revenue by region", it has to guess where revenue and regions live, and a confident guess can still name tables or columns that don't exist. Even when the names happen to match, Claude can't know your business rules, such as which orders count toward revenue.
Task 3.1: See why the schema matters
The Task 3.1 cell asks Claude for "our revenue by region last quarter" with no schema, then runs the SQL it returns.
- Run the Task 3.1 cell.
- Read the SQL and the error below it.
Note: This cell shows a saved response, so it uses no tokens. If you change the prompt, the cell makes a live call, and Claude may guess differently.
Observed Completion
You should see the following output. Claude invented a `sales` table with `region` and `revenue` columns, and no such table exists:SELECT region, SUM(revenue) as total_revenue FROM sales WHERE date_part('quarter', order_date) = date_part('quarter', CURRENT_DATE - INTERVAL '3 months') AND date_part('year', order_date) = date_part('year', CURRENT_DATE - INTERVAL '3 months') GROUP BY region ORDER BY total_revenue DESC; The query failed: relation "sales" does not exist LINE 4: FROM sales ^
Grounding Claude in your data
A good system prompt for SQL gives Claude four things. XML tags keep each one separate, so Claude can tell the schema from the rules:
- A role and a dialect: "You are a senior data analyst who writes PostgreSQL SQL for a retail chain."
- The schema, inside
<schema>tags. - The business rules, inside
<business_rules>tags: how revenue is calculated, which orders count, and today's date. Fixing "today" at 2025-12-31 means "last quarter" always means the same thing. - Output rules, inside
<output_rules>tags. They tell Claude to use exactly the column names, rounding and sort order the question asks for, and show it the exact reply format. A question that pins down its output has one correct shape, so you can compare the result with a trusted one.
The notebook's
build_system_prompt()function puts these four parts together, andgenerate_sql(question)sends the prompt and a question to Claude. Claude replies with the query inside<sql></sql>tags and its assumptions inside<assumptions></assumptions>tags, andextract_tag()pulls out what's between the tags. Read the assumptions: they show where Claude had to guess.The business rules are the part only you can supply. Claude can't tell from the schema that revenue is net of discounts or that returned orders don't count, so you'll write those two rules in SQL.
Tags are the simplest reply format. They need nothing beyond the prompt and work with any model, but Claude can ignore them, so the notebook has a fallback that you'll see in Step 5. Step 4 introduces structured output, which enforces the shape of a reply.
Rules that go into a prompt deserve the same testing as any other SQL. If a rule is wrong, Claude may follow it, or it may quietly work around it, and neither is what you want. The notebook therefore runs your two rules against the database and compares the total with the finance team's figure before Claude sees them.
Task 3.2: Ground Claude in the schema
In the Task 3.2 cell of the lab notebook:
- Set
REVENUE_SQLto the SQL expression for revenue: the sum, overorder_items, of quantity times unit price times (1 minus the discount fraction).unit_priceis the price before the discount, so the discount is applied once in this expression. Write the columns with their full table names, such asorder_items.quantity, because Claude reads the rule without your aliases. - Set
COMPLETED_SQLto the condition that keeps only completed orders, again with the full table name. - Run the cell, then run the next cell. It tests your two rules on the database, then builds
BUSINESS_RULESfrom them and prints the result. If the test stops with a message, correct your rules and run both cells again. - Run the cell after that. It asks Claude for the top five product categories by revenue in the West region in Q3 2025, with each one's share of West revenue.
Note: The saved answer for this question was recorded with the rules from the solutions notebook. If your rules are worded differently, the question makes a live call, and Claude's SQL may differ from the example below.
Hint
`discount_pct` is a fraction, so `0.10` means 10% off and the price after the discount is `unit_price * (1 - discount_pct)`. The `status` column holds lowercase text.Solution
REVENUE_SQL = "SUM(order_items.quantity * order_items.unit_price * (1 - order_items.discount_pct))" COMPLETED_SQL = "orders.status = 'completed'"Observed Completion
You should see the result of the test, then the business rules with your SQL in them:Your business rules match the finance team's total for all completed orders: 37,817,399.58 - Today is 2025-12-31. The data covers 2024-01-01 to 2025-12-31. Quarters are calendar quarters. - Revenue is SUM(order_items.quantity * order_items.unit_price * (1 - order_items.discount_pct)). - Only orders where orders.status = 'completed' count toward revenue. - A store's region is stores.region. Guest orders have a NULL customer_id. - A product's category is the category its products.category_id points to.Then Claude's assumptions, SQL and result, similar to the following. The assumptions and SQL may be worded differently, but
Problemsshould be an empty list and the result table should match.Problemslists anything thesqlglotparser found wrong with Claude's SQL, so an empty list means the query parsed cleanly:Assumptions: Q3 2025 is July 1 – September 30, 2025. Categories at any level (department/category/subcategory) are included as-is without hierarchical filtering. WITH west_q3_2025 AS ( SELECT oi.product_id, oi.quantity, oi.unit_price, oi.discount_pct FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN stores s ON o.store_id = s.store_id WHERE s.region = 'West' AND o.status = 'completed' AND o.order_date >= '2025-07-01' AND o.order_date < '2025-10-01' ), ... ORDER BY cr.revenue DESC LIMIT 5; Problems: [] category_name revenue revenue_share_pct 0 Budget Phones 92433.77 6.6 1 Smartwatches 91759.71 6.6 2 Gaming Laptops 82017.39 5.9 3 Flagship Phones 73104.2 5.2 4 Ultrabooks 72710.31 5.2
Checking Claude's answer
SQL that runs isn't necessarily right. Before you trust Claude's query, check it two ways:
- Against a result you trust. The finance team keeps its own query for this question,
TRUSTED_WEST_Q3_SQL, but its filter is missing. Once you complete it, thecompare_results(a, b)helper compares the two result tables value by value, ignoring column names, and prints the first difference it finds. The notebook passescheck_order=True, because the order matters for a top five. - Against the business rules. A query can return the right numbers today for the wrong reason. The notebook's
filters_completed_orders()function uses thesqlglotlibrary to parse Claude's SQL and confirm that it keeps only completed orders, whatever the data looks like.
Task 3.3: Validate the answer
In the Task 3.3 cell of the lab notebook:
- In
TRUSTED_WEST_Q3_SQL, replaceWHERE TRUEwith conditions that keep completed orders from West stores placed in Q3 2025. Filter the quarter with a date range ono.order_date. - Run the cell, then run the next cell to compare Claude's result with yours and check Claude's status filter.
Hint
Q3 2025 starts on July 1 and ends before October 1. Use `>=` for the first day and `<` for the day after the quarter ends, such as `o.order_date >= DATE '2025-07-01'`. Join the conditions with `AND`.Solution
WHERE o.status = 'completed' AND s.region = 'West' AND o.order_date >= DATE '2025-07-01' AND o.order_date < DATE '2025-10-01'Observed Completion
You should see the following output. If the first line shows `False`, check the date range in your filter: the table below it is your query's result.Claude's result matches the trusted query: True Claude's query counts only completed orders: True category_name revenue revenue_share_pct 0 Budget Phones 92433.77 6.6 1 Smartwatches 91759.71 6.6 2 Gaming Laptops 82017.39 5.9 3 Flagship Phones 73104.2 5.2 4 Ultrabooks 72710.31 5.2Note: Claude filtered the quarter with a date range, as you did. It sometimes writes
EXTRACT(QUARTER FROM o.order_date) = 3instead. That's also correct, but PostgreSQL can't use an index onorder_datefor it, while a date range returns the same rows and can use one. You'll look at indexes in Step 6.
In the next step, you'll ask Claude for one query in four SQL dialects and translate a legacy Oracle query.
-
Challenge
Step 4: Work across SQL dialects
In this step, you'll answer one question in four SQL dialects, then move a legacy Oracle query to PostgreSQL and SQLite and compare Claude's translation with a rule-based tool.
Why dialects matter
The four databases in this step agree on most of SQL and differ in the details that analysts use every day:
| | PostgreSQL | SQLite | MySQL | Oracle | |---|---|---|---|---| | Join text |
a \|\| b|a \|\| b|CONCAT(a, b)|a \|\| b| | Top three rows |LIMIT 3|LIMIT 3|LIMIT 3|FETCH FIRST 3 ROWS ONLY| | First day of a month |DATE_TRUNC('month', d)|date(d, 'start of month')|DATE_FORMAT(d, '%Y-%m-01')|TRUNC(d, 'MM')| | Replace a NULL |COALESCE|COALESCE|COALESCE|NVLorCOALESCE|Claude knows these differences. Your job is to tell it which dialects you need and to check what comes back.
One reply, four queries
To get four queries back in a form the notebook can use, the notebook asks for structured output. It describes the reply as a small class with one field per dialect, and
client.messages.parse()returns an object with exactly those fields:class DialectAnswers(BaseModel): postgresql: str = Field(description="The query for PostgreSQL 16") sqlite: str = Field(description="The query for SQLite 3.51") mysql: str = Field(description="The query for MySQL 8") oracle: str = Field(description="The query for Oracle 19c")The lab runs PostgreSQL and SQLite, so you can run those two versions and compare their results. It has no MySQL or Oracle server, so the
validate_sql(sql, dialect)helper parses those versions with thesqlglotlibrary and lists any problems it finds. A query that parses isn't proven correct, and one that doesn't parse needs a closer look. It's probably wrong, althoughsqlglotoccasionally rejects valid syntax that's specific to one dialect.You'll write the PostgreSQL version yourself first, so you have a result you trust to compare Claude's with.
Task 4.1: One question, four dialects
The question is: which three stores had the highest revenue in Q4 2025?
In the Task 4.1 cell of the lab notebook:
- Replace the TODO comment in
MY_TOP_STORES_SQLwith a PostgreSQL query that answers the question. Returnstore_label(the city, then' - ', then the store name, such asBoston - Back Bay) andrevenue(rounded to 2 decimals), with the highest revenue first. - Run the cell.
- Run the next cell to ask Claude for the same query in all four dialects.
- Run the cell after that to compare the results.
Hint
Join `orders` to `order_items` and `stores`. Keep completed orders from October 1, 2025 up to, but not including, January 1, 2026. Build the label with `s.city || ' - ' || s.store_name`, group by `s.store_id` and the two label columns, so that two stores with the same city and name would stay separate, and finish with `ORDER BY revenue DESC LIMIT 3`.Solution
SELECT s.city || ' - ' || s.store_name AS store_label, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN stores s ON s.store_id = o.store_id WHERE o.status = 'completed' AND o.order_date >= DATE '2025-10-01' AND o.order_date < DATE '2026-01-01' GROUP BY s.store_id, s.city, s.store_name ORDER BY revenue DESC LIMIT 3Observed Completion
Your query should return these three stores:store_label revenue 0 New York - Fifth Avenue 788910.8 1 Los Angeles - Santa Monica 749013.76 2 San Francisco - Union Square 658240.87Claude's four queries should each show
Problems: []. Look at how they differ: MySQL usesCONCAT(), and Oracle converts the dates withTO_DATE()and ends withFETCH FIRST 3 ROWS ONLY:--- postgresql | Problems: [] SELECT CONCAT(s.city, ' - ', s.store_name) AS store_label, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct))::numeric, 2) AS revenue FROM stores s JOIN orders o ... AND o.order_date >= '2025-10-01' AND o.order_date < '2026-01-01' ... ORDER BY revenue DESC LIMIT 3 --- sqlite | Problems: [] SELECT city || ' - ' || store_name AS store_label, ... ORDER BY revenue DESC LIMIT 3 --- mysql | Problems: [] SELECT CONCAT(s.city, ' - ', s.store_name) AS store_label, ... ORDER BY revenue DESC LIMIT 3 --- oracle | Problems: [] SELECT city || ' - ' || store_name AS store_label, ... AND o.order_date >= TO_DATE('2025-10-01', 'YYYY-MM-DD') AND o.order_date < TO_DATE('2026-01-01', 'YYYY-MM-DD') ... ORDER BY revenue DESC FETCH FIRST 3 ROWS ONLYAnd the comparison should show that all three results you can run agree:
Your query and Claude's PostgreSQL agree: True Claude's PostgreSQL and SQLite agree: True
Translating a legacy query
Your company is retiring an Oracle reporting database. Its monthly revenue report,
LEGACY_ORACLE_SQL, uses two Oracle features:TRUNC(o.order_date, 'MM')returns the first day of the order's month, andNVL()replaces a NULL discount with 0.The notebook translates the query two ways:
- With Claude. The prompt asks for a PostgreSQL version and a SQLite version, and for a short list of risks: behavior that could differ between the databases. A translation can run without errors and still return different results, so the risks tell you what to test.
- With a transpiler.
sqlglot.transpile()rewrites SQL from one dialect to another by fixed rules. It's fast, free and predictable, but it only knows the rules it was given.
You can't run the Oracle original here, so the check is that the two translations return the same rows in PostgreSQL and in SQLite.
Task 4.2: Translate a legacy Oracle query
In the lab notebook:
- Run the cell that defines
LEGACY_ORACLE_SQL, then the cell below it, where Claude translates the query. - Run the next cell to transpile the same query to SQLite with
sqlglot. Read the error: the transpiler turnedTRUNC(..., 'MM')intoDATE_TRUNC('MM', ...), and SQLite has no such function. - In the Task 4.2 cell, set
SQLITE_MONTH_STARTto a SQLite expression that returns the first day of the month ofo.order_dateas'YYYY-MM-DD'text. The cell puts your expression in place ofDATE_TRUNC('MM', o.order_date)and compares the result with Claude's translation. - Run the cell.
Hint
SQLite's `date()` function takes a date and one or more modifiers. The modifier `'start of month'` moves a date back to the first day of its month.Solution
SQLITE_MONTH_START = "date(o.order_date, 'start of month')"strftime('%Y-%m-01', o.order_date)also works.Observed Completion
Claude's translation should print its SQLite query and risks, then confirm that both translations agree. The risks may be worded differently:SELECT DATE(o.order_date, 'start of month') AS month_start, s.region, ROUND(SUM(oi.quantity * oi.unit_price * (1 - COALESCE(oi.discount_pct, 0))), 2) AS revenue, COUNT(DISTINCT o.order_id) AS order_count FROM orders o ... ORDER BY month_start, revenue DESC Risks: PostgreSQL DATE_TRUNC returns a timestamp; cast to DATE to ensure 'YYYY-MM-DD' output format. SQLite DATE with 'start of month' returns text; both databases should handle the string comparison correctly if order_date is stored as text or DATE type. Floating-point arithmetic may produce minor rounding differences between databases. PostgreSQL and SQLite agree: TrueThe transpiled query should fail:
The transpiled query failed: no such function: DATE_TRUNCThe same output would fail in PostgreSQL too. PostgreSQL has
DATE_TRUNC, but'MM'isn't one of its units ('month'is). A transpiler's output needs the same checks as Claude's.And with your fix, the transpiled query should match Claude's translation. The table shows the first eight rows of the result:
Your fix matches Claude's SQLite translation: True month_start region revenue order_count 0 2025-01-01 West 413024.9 961 1 2025-01-01 East 365077.15 887 2 2025-01-01 South 288994.71 720 3 2025-01-01 Central 269713.09 673 4 2025-02-01 West 369741.77 827 5 2025-02-01 East 302261.57 713 6 2025-02-01 South 262049.2 634 7 2025-02-01 Central 250072.68 628
In the next step, you'll guide Claude toward CTEs, window functions and recursive queries, and have it rewrite a slow query.
- Replace the TODO comment in
-
Challenge
Step 5: Write advanced SQL and optimize queries
In this step, you'll steer how Claude writes a query, not only what the query returns. You'll ask for CTEs and window functions, see a recursive query roll up the category hierarchy, and have Claude rewrite a slow query.
Asking for a style
Many questions have several correct queries. Month-over-month change can come from a self-join, a correlated subquery or the
LAG()window function. All three return the same numbers here, because every region has sales in every month, but the window function reads the table once and is the easiest to follow.LAG()returns the previous row, not the previous calendar month, so if a month were missing it would compare with the wrong one.Claude picks one approach unless you say which you want. A short style guide in the system prompt tells it. The notebook's
generate_sql()function takes astyle_guideand adds it to the prompt inside<style_guide>tags, next to the schema and business rules from Step 3.Two features make up most of the style you'll ask for:
- A CTE (common table expression) names one step of a query:
WITH monthly AS (SELECT ...). Later steps read frommonthlyas if it were a table. - A window function calculates across related rows without collapsing them.
LAG(revenue) OVER (PARTITION BY region ORDER BY month)returns the previous month's revenue for the same region, andSUM(revenue) OVER (PARTITION BY region ORDER BY month)returns a running total.
Two details matter when you reuse the running total. With
ORDER BYand no frame clause, the window covers the partition's first row to the current row, plus any rows that tie with the current one on theORDER BYcolumns. Each region has one row per month here, so the default is safe. Where ties are possible, addROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This lab's question also covers a single year. Across several years, a year-to-date total needs the year inPARTITION BYas well.
Task 5.1: Use CTEs and window functions
The question in
REGION_MONTHLY_QUESTIONasks for each region's monthly revenue in 2025, the change from the previous month and the year-to-date total.In the lab notebook:
- Run the cell that defines
REGION_MONTHLY_QUESTION. - In the Task 5.1 cell, write a style guide in
STYLE_GUIDE, in plain English and with one rule per line. Ask for one named CTE per step, window functions such asLAG()andSUM() OVER (...)instead of self-joins or correlated subqueries, and exactly the column names the question asks for. - Run the cell, then run the next cell. It sends the question with your style guide and checks that Claude's query uses a CTE and a window function.
Note: Your style guide becomes part of the request, so the next cell makes a live call unless your text matches the solution exactly. Expect it to use several hundred tokens.
Hint
Write the rules the way you'd brief a colleague, starting each line with a dash. Name the functions you want, such as `LAG()`, so Claude doesn't have to guess.Solution
STYLE_GUIDE = """ - Build the query from named CTEs, one per step. - Use window functions such as LAG() and SUM() OVER (...) instead of self-joins or correlated subqueries. - Use exactly the column names the question asks for. """Observed Completion
You should see output similar to the following. Claude's SQL will differ, but both checks should show `True` and the table, which shows the first 13 rows, should match. Values in `mom_change` and `ytd_revenue` can differ by a cent, depending on whether Claude rounds before or after the window functions.If you see a line that starts with
[note: no <sql> tags in the reply, Claude wrapped its query in a Markdown code block instead of the tags the prompt asked for, and the notebook read the query from the code block. Replies don't always follow the format, which is why code that reads them needs a fallback:Assumptions: No products table is needed; revenue calculation uses only order_items and orders. 2025 includes all data from 2025-01-01 through 2025-12-31. [note: no <sql> tags in the reply; using its code block instead] WITH completed_orders AS ( ... ), monthly_revenue AS ( ... ), with_mom_change AS ( SELECT month, region, revenue, ROUND((revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month))::NUMERIC, 2) AS mom_change FROM monthly_revenue ), with_ytd AS ( SELECT month, region, revenue, mom_change, ROUND(SUM(revenue) OVER (PARTITION BY region ORDER BY month)::NUMERIC, 2) AS ytd_revenue FROM with_mom_change ) SELECT month, region, revenue, mom_change, ytd_revenue FROM with_ytd ORDER BY region, month; Uses a CTE: True Uses a window function: True month region revenue mom_change ytd_revenue 0 2025-01 Central 269713.09 <NA> 269713.09 1 2025-02 Central 250072.68 -19640.41 519785.77 2 2025-03 Central 351169.72 101097.04 870955.49 3 2025-04 Central 298758.44 -52411.28 1169713.93 4 2025-05 Central 323316.94 24558.5 1493030.87 5 2025-06 Central 290556.01 -32760.93 1783586.88 6 2025-07 Central 343486.62 52930.61 2127073.5 7 2025-08 Central 361113.16 17626.54 2488186.66 8 2025-09 Central 287568.21 -73544.95 2775754.87 9 2025-10 Central 379510.86 91942.65 3155265.73 10 2025-11 Central 445330.78 65819.92 3600596.51 11 2025-12 Central 523217.29 77886.51 4123813.8 12 2025-01 East 365077.15 <NA> 365077.15
Recursive queries for hierarchies
The
categoriestable is a tree. Each row points to its parent throughparent_category_id: a subcategory such as Budget Phones points to the category Smartphones, which points to the department Electronics. Products can hang off a category at level 2 or level 3."Revenue by department" therefore needs every category below each department, however deep. A recursive CTE does this in two parts joined by
UNION ALL: a starting query that selects the departments, and a second query that joinscategoriesto the CTE's own rows to add the children, then their children, until none are left.Recursive queries are easy to get subtly wrong, so the prompt in this task describes the approach step by step. The cell also runs a check that needs no trusted query: every product belongs to exactly one department, so the departments must add up to all 2025 revenue. A roll-up that counts sales twice or loses a level fails that check.
Task 5.2: Roll up a category hierarchy
- Run the Task 5.2 cell.
- Find the two parts of the recursive CTE in Claude's SQL, then compare the two totals printed below it.
Note: This cell shows a saved response, so it uses no tokens.
Observed Completion
You should see the following output. The two totals differ by a cent because each department's revenue is rounded before it's added up:Assumptions: Recursive CTE traverses all children regardless of depth by repeatedly joining on parent_category_id until no more children exist. Each product belongs to exactly one category, which may be a level 1, 2, or 3 category, and revenue is attributed to the department (top-level ancestor). WITH RECURSIVE category_tree AS ( SELECT category_id, category_name AS department, category_id AS leaf_category_id FROM categories WHERE level = 1 UNION ALL SELECT ct.category_id, ct.department, c.category_id FROM category_tree ct JOIN categories c ON c.parent_category_id = ct.leaf_category_id ) SELECT ct.department, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct))::numeric, 2) AS revenue FROM category_tree ct JOIN products p ON p.category_id = ct.leaf_category_id JOIN order_items oi ON oi.product_id = p.product_id JOIN orders o ON o.order_id = oi.order_id WHERE o.status = 'completed' AND EXTRACT(YEAR FROM o.order_date) = 2025 GROUP BY ct.category_id, ct.department ORDER BY revenue DESC; Departments add up to 19,449,275.35; all 2025 revenue is 19,449,275.34 department revenue 0 Electronics 5499668.81 1 Clothing 3573054.11 2 Home & Kitchen 3392034.26 3 Sports & Outdoors 2454245.67 4 Beauty 2304527.43 5 Toys & Games 2225745.07
Rewriting a slow query
SLOW_SQLlists Platinum customers in California with their 2025 order count and revenue. It returns the right rows, slowly: for every matching row it runs two correlated subqueries, each of which readsordersagain, and thenDISTINCTremoves the duplicates the extra join created.Claude can rewrite a query like this, but a rewrite is only useful if it returns the same rows. Two things keep it honest:
- Tell Claude what the query is for. SQL shows what a query does, not what it's meant to do. A one-sentence purpose removes the guesswork, especially for details that a join can break. Here, joining
orderstoorder_itemsproduces one row per order line, so a plainCOUNT(*)would count lines instead of orders. - Check the result and measure. The notebook compares the rewrite's rows with the original's using
compare_results(), then times both withbenchmark().
Task 5.3: Rewrite an inefficient query
In the lab notebook:
- Run the cell that defines
SLOW_SQLand read the query. - In the Task 5.3 cell, describe in
SLOW_PURPOSE, in plain English, which customers the query returns and what it reports for each. Say that each order is counted once, not once per order line. - Run the cell, then run the next cell. It sends your purpose and the query to Claude, checks the rewrite and times both versions.
Note: Your purpose becomes part of the request, so the next cell makes a live call unless your text matches the solution exactly. Expect it to use several hundred tokens.
Hint
Read the query's `WHERE` clause for the customers it keeps, and its two subqueries for what it reports. One sentence is enough.Solution
SLOW_PURPOSE = ("Platinum customers in California with at least one completed order in 2025, with each one's " "number of completed 2025 orders (each order counted once, not once per order line) and their " "2025 revenue.")Observed Completion
You should see output similar to the following. Claude's rewrite may differ, and your timings will too, but `Same rows` should show `True` and the rewrite should be many times faster. If `Same rows` shows `False`, make your purpose more specific and run both cells again:Replaced correlated subqueries with a single JOIN pass, eliminating redundant table scans and improving efficiency. Used COUNT(DISTINCT order_id) and GROUP BY to count orders once per order instead of DISTINCT with subqueries. Removed unnecessary o2 join and simplified WHERE clause by filtering directly on the main orders join condition. SELECT c.customer_id, c.first_name, c.last_name, COUNT(DISTINCT o.order_id) AS orders_2025, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 2) AS revenue_2025 FROM customers c JOIN orders o ON o.customer_id = c.customer_id AND EXTRACT(YEAR FROM o.order_date) = 2025 AND o.status = 'completed' JOIN order_items oi ON oi.order_id = o.order_id WHERE c.loyalty_tier = 'Platinum' AND c.state = 'CA' GROUP BY c.customer_id, c.first_name, c.last_name Same rows: True query median_ms fastest_ms 0 original 1701.9 1665.7 1 rewrite 29.5 29.1Note: Claude filtered the year with
EXTRACT(YEAR FROM o.order_date)here and in Task 5.2. Both queries return the correct rows, but this is the pattern the note in Step 3 warned about, because PostgreSQL can't use an index onorder_datefor it. Claude writes a date range only some of the time unless you ask. To get one every time, add a rule to your style guide, such as "Filter dates with a range on the column, not a function of the column."
In the next step, you'll look at how PostgreSQL runs a query, and use Claude to read the plan and recommend an index.
- A CTE (common table expression) names one step of a query:
-
Challenge
Step 6: Analyze performance with execution plans
In this step, you'll get a query's execution plan from PostgreSQL, have Claude explain it and recommend an index, then create the index and measure the difference.
Reading an execution plan
Before PostgreSQL runs a query, it plans how: which table to read first, whether to use an index, how to join.
EXPLAIN ANALYZEruns the query and reports the plan it used, with the number of rows each step produced. The notebook'sexplain(sql)helper returns that plan as text.A plan is a tree, and you read it from the most indented lines outward. A few terms cover most plans:
- Seq Scan reads every row of a table. That's fine for a small table and expensive for a large one when the query needs only a few rows.
- Index Scan and Bitmap Index Scan use an index to go straight to the matching rows.
- Rows Removed by Filter counts rows that were read and then thrown away. A large number next to a small
actual rowsis the sign of a missing index. - Hash Join and Nested Loop are two ways to join tables.
Giving Claude evidence
Asking Claude to "make this query faster" with only the SQL forces it to guess. It can't see how big your tables are, which indexes already exist or where the time goes, so it lists every general tip it knows. The notebook shows you that reply first.
A better prompt gives Claude what a database administrator would ask for, each in its own XML tags: the query, the plan, the table sizes from
table_stats()and the existing indexes fromlist_indexes(). With that evidence, Claude can name the one step that matters and recommend only indexes you don't already have.
Task 6.1: Explain a plan with Claude
PLAN_SQLreports December 2025 revenue by day and loyalty tier for store 5.In the lab notebook:
- Run the cell that defines
PLAN_SQL. It prints the query's plan. Find theSeq Scan on ordersline and the rows removed below it. - Run the next cell, the vague request, and skim Claude's reply.
- Run the Task 6.1 cell and compare its reply with the vague one.
Note: The vague request shows a saved response, so it uses no tokens.
Observed Completion
You should see the plan first. PostgreSQL read all 100,000 orders to keep 411:GroupAggregate (actual rows=133 loops=1) Group Key: o.order_date, (COALESCE(c.loyalty_tier, 'Guest or none'::text)) -> Sort (actual rows=1205 loops=1) Sort Key: o.order_date, (COALESCE(c.loyalty_tier, 'Guest or none'::text)) -> Nested Loop (actual rows=1205 loops=1) -> Hash Left Join (actual rows=411 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Seq Scan on orders o (actual rows=411 loops=1) Filter: ((order_date >= '2025-12-01'::date) AND (order_date < '2026-01-01'::date) AND (store_id = 5) AND (status = 'completed'::text)) Rows Removed by Filter: 99589 -> Hash (actual rows=20000 loops=1) -> Seq Scan on customers c (actual rows=20000 loops=1) -> Index Scan using order_items_order_id_idx on order_items oi (actual rows=3 loops=411) Index Cond: (order_id = o.order_id)The vague request gets a long, generic reply that runs out of room. It rewrites the query without knowing where the time goes, recommends an index on
order_items(order_id)that already exists, and suggests changing the table itself:# Optimized Query ... ## Key Changes 1. **Remove COALESCE from GROUP BY** (line 10) ... 2. **Add these indexes** (if they don't exist): CREATE INDEX idx_orders_store_date_status ON orders(store_id, order_date, status); CREATE INDEX idx_order_items_order_id ON order_items(order_id); ... 3. **Optional: Pre-calculate revenue** (for very large datasets) ALTER TABLE order_items ADD COLUMN net_amount AS (quantity * unit_price * (1 - discount_pct)); ... [Claude's reply was cut off at max_tokens=500]The Task 6.1 reply is short and specific. It names the sequential scan as the bottleneck and recommends one index:
The query retrieves revenue by order date and loyalty tier for completed orders at store 5 in December 2025. It performs a sequential scan on the orders table filtering by date, store, and status, then joins to customers via hash join and order_items via index lookup. The results are sorted and aggregated by order date and loyalty tier, returning 133 final rows from 1205 intermediate rows. Bottleneck: The sequential scan on the orders table examines 99,589 rows that don't match the filter conditions (store_id = 5, date range, status = 'completed') before finding the 411 qualifying rows, causing significant I/O waste. CREATE INDEX idx_orders_store_date_status ON orders(store_id, order_date, status)
Trust the measurement
Claude's recommendation is a hypothesis. An index helps only if PostgreSQL chooses to use it, and every index makes inserts and updates a little slower, so you should create one only when you can show that it pays off. The test has three parts:
- Run the query and time it with
benchmark(). - Create the index, then get the plan again and check that the sequential scan is gone.
- Time the query again, and confirm that the results haven't changed.
The notebook's
apply_indexes()helper creates the index. It accepts onlyCREATE INDEXstatements and skips an index that already exists.On a production database, build the index with
CREATE INDEX CONCURRENTLY. A plainCREATE INDEXblocks writes to the table until it finishes. The concurrent form doesn't, although it takes longer to build. The lab's table is small and has no other users, so the plain form is fine here.
Task 6.2: Apply an index and measure
In the Task 6.2 cell of the lab notebook:
- Set
MY_INDEX_SQLto aCREATE INDEXstatement onordersfor the columns the query filters on. Start from Claude's recommendation and the plan from Task 6.1, and choose the column order yourself. - Run the cell.
Note: You can run this step again. The cell that defines
PLAN_SQLremoves the indexes you created, so run it first to start over.Hint
The `Filter` line in the plan lists the columns: `store_id`, `order_date` and `status`. The syntax is `CREATE INDEX index_name ON table_name (column_1, column_2, ...)`. Put the columns compared with `=` before the date range.Solution
MY_INDEX_SQL = "CREATE INDEX idx_orders_store_status_date ON orders (store_id, status, order_date)"Claude's recommendation in Task 6.1 put
order_datebeforestatus. Both orders work for this query. With the=columns first, the entries the query needs sit next to each other in the index, so that's the order to prefer.Observed Completion
You should see output similar to the following. The sequential scan on `orders` is now a bitmap scan on your index that reads only the 411 matching rows. Your timings will differ, but the query should run about twice as fast, with the same results:Created: CREATE INDEX idx_orders_store_status_date ON orders (store_id, status, order_date) GroupAggregate (actual rows=133 loops=1) Group Key: o.order_date, (COALESCE(c.loyalty_tier, 'Guest or none'::text)) -> Sort (actual rows=1205 loops=1) Sort Key: o.order_date, (COALESCE(c.loyalty_tier, 'Guest or none'::text)) -> Nested Loop (actual rows=1205 loops=1) -> Hash Left Join (actual rows=411 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Bitmap Heap Scan on orders o (actual rows=411 loops=1) Recheck Cond: ((store_id = 5) AND (status = 'completed'::text) AND (order_date >= '2025-12-01'::date) AND (order_date < '2026-01-01'::date)) -> Bitmap Index Scan on idx_orders_store_status_date (actual rows=411 loops=1) Index Cond: ((store_id = 5) AND (status = 'completed'::text) AND (order_date >= '2025-12-01'::date) AND (order_date < '2026-01-01'::date)) -> Hash (actual rows=20000 loops=1) -> Seq Scan on customers c (actual rows=20000 loops=1) -> Index Scan using order_items_order_id_idx on order_items oi (actual rows=3 loops=411) Index Cond: (order_id = o.order_id) Same results: True query median_ms fastest_ms 0 before 15.6 15.6 0 after 6.5 6.5If you indexed only
store_idandorder_date, the plan also showsFilter: (status = 'completed'::text)andRows Removed by Filter: 43. That index works too: it reads 454 rows instead of 411.
In the next step, you'll turn a one-off query into something your team can reuse safely.
-
Challenge
Step 7: Document, parameterize and safeguard queries
In this final step, you'll have Claude document a query and turn it into a reusable template, then put three safeguards around SQL that you didn't write yourself: parameters, a read-only check and a review for logic errors.
From a one-off query to a template
QUERY_TO_DOCUMENTanswers one question: revenue by category in the West region in Q3 2025. Next quarter, someone will copy it and edit the dates by hand. Two changes make it reusable:- Documentation. A short header comment says what the query is for, what it takes and returns, and which business rules it applies. Claude writes this well, because describing code is a strength of language models. The risk is that it changes the logic while it adds comments, so the notebook checks that the documented query still returns the same rows.
- Parameters. A template replaces the values that change with placeholders. With the
psycopgdriver that this lab uses for PostgreSQL, a placeholder looks like%(region)s. You run the template withrun_sql(sql, params={"region": "West", ...}).
Write a placeholder with no quotes around it, even for text and dates. The driver sends the values to the database separately from the SQL and handles the quoting itself.
Task 7.1: Document and parameterize a query
- Run the cell that defines
QUERY_TO_DOCUMENT. - Run the Task 7.1 cell. It asks Claude for a documented version and a template version of the query, checks the documented version and saves it to the
queriesfolder. To open the saved file, use the Jupyter file browser.
Observed Completion
You should see output similar to the following. Claude's comments may be worded differently, but `Same results` should show `True`:-- Purpose: Calculate revenue by product category for completed orders -- Inputs: Store region, date range (start inclusive, end exclusive) -- Output: category_name, revenue (quantity * unit_price * (1 - discount_pct)) -- Business rules: Only completed orders in specified region and date range SELECT c.category_name, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id JOIN categories c ON c.category_id = p.category_id JOIN stores s ON s.store_id = o.store_id WHERE o.status = 'completed' AND s.region = 'West' -- Filter by store region AND o.order_date >= DATE '2025-07-01' AND o.order_date < DATE '2025-10-01' -- Q3 2025 GROUP BY c.category_id, c.category_name ORDER BY revenue DESC -- Highest revenue first Same results: True Saved queries/category_revenue_documented.sqlRead the header as critically as the SQL. Here the
Inputsline lists a store region and a date range, but this version of the query hard-codes both, and they become inputs only in the template.Same resultscompares rows, so it can't catch a comment that misdescribes the query.
Task 7.2: Run a template safely
Claude wrote a template in Task 7.1. Before you look at it, write your own, so you have something to check it against.
In the Task 7.2 cell of the lab notebook:
- Replace the TODO comment in
MY_TEMPLATE_SQLwith a copy ofQUERY_TO_DOCUMENTin which'West'becomes%(region)s, the start date becomes%(start_date)sand the end date becomes%(end_date)s. - Run the cell. It runs your template for the West region in Q3 2025 and compares the result with the original query, prints Claude's template and compares that too, then runs your template for the East region in Q4 2025.
Hint
Replace the whole value, including its quotes and the word `DATE`: `s.region = 'West'` becomes `s.region = %(region)s`, and `DATE '2025-07-01'` becomes `%(start_date)s`.Solution
SELECT c.category_name, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id JOIN categories c ON c.category_id = p.category_id JOIN stores s ON s.store_id = o.store_id WHERE o.status = 'completed' AND s.region = %(region)s AND o.order_date >= %(start_date)s AND o.order_date < %(end_date)s GROUP BY c.category_id, c.category_name ORDER BY revenue DESCObserved Completion
You should see output similar to the following. Your template should match the original query, and Claude's template, which keeps its comments, should give the same result as yours:Matches the original West Q3 query: True Claude's template: -- Purpose: Calculate revenue by product category for completed orders -- Inputs: Store region, date range (start inclusive, end exclusive) -- Output: category_name, revenue (quantity * unit_price * (1 - discount_pct)) -- Business rules: Only completed orders in specified region and date range SELECT c.category_name, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id JOIN categories c ON c.category_id = p.category_id JOIN stores s ON s.store_id = o.store_id WHERE o.status = 'completed' AND s.region = %(region)s -- Filter by store region AND o.order_date >= %(start_date)s AND o.order_date < %(end_date)s -- Date range GROUP BY c.category_id, c.category_name ORDER BY revenue DESC -- Highest revenue first Claude's template gives the same result: TrueIf the first line shows
False, check your placeholders. A placeholder inside quotes, such as'%(region)s', makes the database look for a region whose name includes the quote marks, so the query returns no rows.The last table shows the first five rows of your template's result for the East region in Q4 2025:
category_name revenue 0 Suits 120869.76 1 Budget Phones 96556.92 2 Action Figures 85160.05 3 Jeans 83188.72 4 Dresses 77352.6
Why parameters matter: SQL injection
Parameters do more than save typing. The notebook's
unsafe_region_revenue()function builds its query by pasting the region into the SQL text. Called with the regionWest' OR '1'='1, the pasted text closes the quote early and adds a condition that's always true, so the query returns every region. This is SQL injection, and the same trick can read or change data the caller was never meant to reach.A parameter can't do that. The database receives the value separately from the SQL, so it's only ever compared as a value and never read as part of the query.
Allowing only read-only SQL
SQL that Claude writes, or that a colleague pastes in, should be checked before it runs. The notebook's
is_safe_select()function allows a statement only if all of these hold:- It parses, and it's exactly one statement.
SELECT 1; DELETE FROM ordersis two. - It's a
SELECT, or aUNIONof them. - Nothing inside it writes data. A CTE can hide a
DELETE, so the function searches the whole statement. - It has no
INTOclause, becauseSELECT ... INTOcreates a table.
The function parses the SQL with
sqlglotinstead of searching the text for words likeDROP. A text search is easy to fool, and it blocks harmless queries that mention such a word in a string or a column name.A check like this is one layer of protection. The lab adds a second one that you should always have:
run_sql()connects to PostgreSQL as a user that can only read.Neither layer judges what a
SELECTcosts. A query that joins every row to every other row, or one that calls a function such aspg_sleep(), is read-only and can still tie up the database. In production, grant the read-only user only the tables it needs, set astatement_timeoutfor it and cap the number of rows a query can return.
Task 7.3: Block unsafe statements
In the lab notebook:
- Run the cell that defines
unsafe_region_revenue(). Its result shows all four regions, although the function was asked for one. - Run the next cell, which defines
is_safe_select()and eight test cases. - In the Task 7.3 cell, replace the two empty strings in
MY_ATTACKSwith two SQL statements of your own that change or destroy data. - Run the cell. Every line should start with
ok.
Hint
Try a statement that isn't a `SELECT`, such as an `UPDATE` or `INSERT`, and a harmless `SELECT` followed by a semicolon and a second, harmful statement.Solution
MY_ATTACKS = [ "UPDATE orders SET status = 'completed'", "SELECT * FROM orders; DROP TABLE customers", ]Observed Completion
The injection should return every region:region revenue 0 Central 7976209.54 1 East 9958947.77 2 South 8456687.61 3 West 12586564.27These four figures add up to 38,978,409.19, which is more than the finance team's 37,817,399.58 for completed orders. In the pasted SQL,
ANDbinds more tightly thanOR, so the injectedOR '1'='1'also lets West orders skip the status filter, and West's figure includes returned and cancelled orders. An injection can switch off any condition in a query, not only the one it was pasted into.And the safety check should print one line per case, ending with your two statements. The last two lines will show your own SQL:
ok allowed: SELECT * FROM orders WHERE order_id = 1 ok allowed: WITH t AS (SELECT store_id FROM stores) SELECT store_id FROM t ok allowed: SELECT store_id FROM stores UNION SELECT store_id FROM orders ok blocked: DROP TABLE orders ok blocked: SELECT 1; DELETE FROM orders ok blocked: WITH d AS (DELETE FROM orders RETURNING *) SELECT * FROM d ok blocked: SELECT * INTO orders_copy FROM orders ok blocked: ATTACH DATABASE 'other.db' AS other ok blocked: UPDATE orders SET status = 'completed' ok blocked: SELECT * FROM orders; DROP TABLE customersIf one of your lines starts with
WRONG, the check allowed your statement. Check whether the statement really changes data. If it does, you've found a gap in the check, which is why the read-only database user matters.
Plausible but wrong
The hardest errors to catch are in queries that run, look reasonable and return the wrong answer.
FLAWED_SQLis meant to list customers who have never placed an online order:SELECT customer_id, first_name, last_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE channel = 'online') ORDER BY customer_idIt returns no rows at all. The cause is the guest orders you saw in Step 2, which have a NULL
customer_id.NOT INcompares each customer with every value in the list, and a comparison with NULL is never true, so one NULL in the list rejects every customer.Claude is a useful reviewer for this kind of bug, as long as you give it what a reviewer needs: the question the query should answer, the schema and the symptom. The schema matters most here, because it's where Claude learns that
customer_idcan be NULL.There are two standard fixes: filter the NULLs out of the list, or use
NOT EXISTS, which isn't affected by them.
Task 7.4: Catch plausible but wrong SQL
In the lab notebook:
- Run the cell that defines
FLAWED_SQLand confirm that it returns 0 rows. - Run the next cell, where Claude reviews the query, and read its explanation and fix.
- In the Task 7.4 cell, replace the TODO comment in
MY_FIXED_SQLwith your own corrected query, usingNOT EXISTS. Keep the same columns and order. - Run the cell to compare your fix with Claude's.
- Run the last cell to see your token total for the lab.
Hint
Give `customers` an alias, such as `c`. Then keep the customers for whom no matching order exists: `WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND ...)`.Solution
SELECT c.customer_id, c.first_name, c.last_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.channel = 'online' ) ORDER BY c.customer_idObserved Completion
Claude's review should explain the NULL problem and give a fix, similar to the following. Here it kept `NOT IN` and filtered out the NULLs, which is the other standard fix:The query uses NOT IN with a subquery that includes NULL values from guest orders (where customer_id is NULL). In SQL, NOT IN returns FALSE when comparing against NULL, causing all rows to be filtered out. The fix is to exclude NULL values from the subquery. SELECT customer_id, first_name, last_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE channel = 'online' AND customer_id IS NOT NULL) ORDER BY customer_idClaude's explanation is slightly off. With a NULL in the list,
NOT INreturns NULL (unknown), not FALSE, for a customer who isn't in the list.WHEREkeeps only the rows where the condition is true, so the effect is the same and every row is dropped. The fix is correct.Your fix should return 5,189 customers and match Claude's. The table shows the first five:
Rows returned: 5189 Matches Claude's fix: True customer_id first_name last_name 0 1 Jessica Brown 1 5 Jack King 2 7 Ava Young 3 15 Amanda Martin 4 18 Benjamin GonzalezThe last cell prints your token total for the lab in the same format as in Step 2. Your numbers depend on how many live calls you made.
Wrapping up
You've used Claude at every stage of an analyst's work with SQL: writing queries from business questions, moving them between dialects, shaping and speeding them up, reading execution plans, and documenting and protecting the result.
The same three habits ran through every step, and they apply to any model and any database:
- Give Claude your context. The schema, the business rules, the purpose of a query and the execution plan turned guesses into specific, correct answers.
- Pin down the output. Exact column names, a reply format and a style guide made Claude's answers easy to use and to compare.
- Check before you trust. You compared results with a trusted query, checked totals, measured timings and tested templates. Claude writes SQL quickly, and you decide whether it's right.
About the author
Real skill practice before real-world application
Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.
Learn by doing
Engage hands-on with the tools and technologies you’re learning. You pick the skill, we provide the credentials and environment.
Follow your guide
All labs have detailed instructions and objectives, guiding you through the learning process and ensuring you understand every step.
Turn time into mastery
On average, you retain 75% more of your learning if you take time to practice. Hands-on labs set you up for success to make those skills stick.