Featured resource
2026 Tech Forecast
2026 Tech Forecast

1,500+ tech insiders, business leaders, and Pluralsight Authors share their predictions on what’s shifting fastest and how to stay ahead.

Download the forecast
  • Lab
    • Libraries: If you want this lab, consider one of these libraries.
    • AI
Labs

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 platform
Lab Info
Level
Intermediate
Last updated
Nov 16, 2026
Duration
1h 0m

Contact sales

By clicking submit, you agree to our Privacy Policy and Terms of Use, and consent to receive marketing emails from Pluralsight.
Table of Contents
  1. 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.ipynb holds the completed SQL and prompts for every task.

    Keep two things in mind as you work:

    1. 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.
    2. 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.
  2. 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 an anthropic client. 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 the anthropic Python SDK, which follows API version 2026-10-21. That version removed the temperature, top_p and top_k settings, 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 a thinking block. The notebook's get_text(response) function joins the text of every block whose type is text, so it keeps working whichever model you choose.


    Task 2.1: Connect to Claude

    In the Task 2.1 cell of the lab notebook:

    1. Replace <TODO_ENTER_API_KEY_HERE> with the API key shown on the lab page.
    2. Read MODEL and get_text(), then run the cell.
    3. 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, returned or cancelled. Only completed orders count toward revenue, so it's worth knowing how many orders have each status.

    In order_items, the schema describes unit_price as the price charged. That's the price before the discount in discount_pct, which is applied separately when you calculate revenue.


    Task 2.2: Explore the sales data

    In the lab notebook:

    1. Run the cell below Task 2.1's test cell to print the compact schema.
    2. Run the next cell to see the first five orders.
    3. In the Task 2.2 cell, replace the TODO comment in ORDERS_BY_STATUS_SQL with a query that counts the orders for each status. Return status and order_count, with the most orders first.
    4. 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 DESC
    
    Observed 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_id is <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  completed
    

    And 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
    
  3. 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.

    1. Run the Task 3.1 cell.
    2. 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:

    1. A role and a dialect: "You are a senior data analyst who writes PostgreSQL SQL for a retail chain."
    2. The schema, inside <schema> tags.
    3. 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.
    4. 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, and generate_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, and extract_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:

    1. Set REVENUE_SQL to the SQL expression for revenue: the sum, over order_items, of quantity times unit price times (1 minus the discount fraction). unit_price is the price before the discount, so the discount is applied once in this expression. Write the columns with their full table names, such as order_items.quantity, because Claude reads the rule without your aliases.
    2. Set COMPLETED_SQL to the condition that keeps only completed orders, again with the full table name.
    3. Run the cell, then run the next cell. It tests your two rules on the database, then builds BUSINESS_RULES from them and prints the result. If the test stops with a message, correct your rules and run both cells again.
    4. 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 Problems should be an empty list and the result table should match. Problems lists anything the sqlglot parser 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:

    1. 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, the compare_results(a, b) helper compares the two result tables value by value, ignoring column names, and prints the first difference it finds. The notebook passes check_order=True, because the order matters for a top five.
    2. Against the business rules. A query can return the right numbers today for the wrong reason. The notebook's filters_completed_orders() function uses the sqlglot library 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:

    1. In TRUSTED_WEST_Q3_SQL, replace WHERE TRUE with conditions that keep completed orders from West stores placed in Q3 2025. Filter the quarter with a date range on o.order_date.
    2. 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.2
    

    Note: Claude filtered the quarter with a date range, as you did. It sometimes writes EXTRACT(QUARTER FROM o.order_date) = 3 instead. That's also correct, but PostgreSQL can't use an index on order_date for 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.

  4. 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 | NVL or COALESCE |

    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 the sqlglot library 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, although sqlglot occasionally 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:

    1. Replace the TODO comment in MY_TOP_STORES_SQL with a PostgreSQL query that answers the question. Return store_label (the city, then ' - ', then the store name, such as Boston - Back Bay) and revenue (rounded to 2 decimals), with the highest revenue first.
    2. Run the cell.
    3. Run the next cell to ask Claude for the same query in all four dialects.
    4. 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 3
    
    Observed 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.87
    

    Claude's four queries should each show Problems: []. Look at how they differ: MySQL uses CONCAT(), and Oracle converts the dates with TO_DATE() and ends with FETCH 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 ONLY
    

    And 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, and NVL() replaces a NULL discount with 0.

    The notebook translates the query two ways:

    1. 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.
    2. 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:

    1. Run the cell that defines LEGACY_ORACLE_SQL, then the cell below it, where Claude translates the query.
    2. Run the next cell to transpile the same query to SQLite with sqlglot. Read the error: the transpiler turned TRUNC(..., 'MM') into DATE_TRUNC('MM', ...), and SQLite has no such function.
    3. In the Task 4.2 cell, set SQLITE_MONTH_START to a SQLite expression that returns the first day of the month of o.order_date as 'YYYY-MM-DD' text. The cell puts your expression in place of DATE_TRUNC('MM', o.order_date) and compares the result with Claude's translation.
    4. 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: True
    

    The transpiled query should fail:

    The transpiled query failed: no such function: DATE_TRUNC
    

    The 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.

  5. 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 a style_guide and 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 from monthly as 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, and SUM(revenue) OVER (PARTITION BY region ORDER BY month) returns a running total.

    Two details matter when you reuse the running total. With ORDER BY and 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 the ORDER BY columns. Each region has one row per month here, so the default is safe. Where ties are possible, add ROWS 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 in PARTITION BY as well.


    Task 5.1: Use CTEs and window functions

    The question in REGION_MONTHLY_QUESTION asks for each region's monthly revenue in 2025, the change from the previous month and the year-to-date total.

    In the lab notebook:

    1. Run the cell that defines REGION_MONTHLY_QUESTION.
    2. 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 as LAG() and SUM() OVER (...) instead of self-joins or correlated subqueries, and exactly the column names the question asks for.
    3. 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 categories table is a tree. Each row points to its parent through parent_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 joins categories to 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

    1. Run the Task 5.2 cell.
    2. 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_SQL lists 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 reads orders again, and then DISTINCT removes 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:

    1. 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 orders to order_items produces one row per order line, so a plain COUNT(*) would count lines instead of orders.
    2. Check the result and measure. The notebook compares the rewrite's rows with the original's using compare_results(), then times both with benchmark().

    Task 5.3: Rewrite an inefficient query

    In the lab notebook:

    1. Run the cell that defines SLOW_SQL and read the query.
    2. 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.
    3. 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.1
    

    Note: 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 on order_date for 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.

  6. 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 ANALYZE runs the query and reports the plan it used, with the number of rows each step produced. The notebook's explain(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 rows is 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 from list_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_SQL reports December 2025 revenue by day and loyalty tier for store 5.

    In the lab notebook:

    1. Run the cell that defines PLAN_SQL. It prints the query's plan. Find the Seq Scan on orders line and the rows removed below it.
    2. Run the next cell, the vague request, and skim Claude's reply.
    3. 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:

    1. Run the query and time it with benchmark().
    2. Create the index, then get the plan again and check that the sequential scan is gone.
    3. Time the query again, and confirm that the results haven't changed.

    The notebook's apply_indexes() helper creates the index. It accepts only CREATE INDEX statements and skips an index that already exists.

    On a production database, build the index with CREATE INDEX CONCURRENTLY. A plain CREATE INDEX blocks 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:

    1. Set MY_INDEX_SQL to a CREATE INDEX statement on orders for the columns the query filters on. Start from Claude's recommendation and the plan from Task 6.1, and choose the column order yourself.
    2. Run the cell.

    Note: You can run this step again. The cell that defines PLAN_SQL removes 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_date before status. 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.5
    

    If you indexed only store_id and order_date, the plan also shows Filter: (status = 'completed'::text) and Rows 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.

  7. 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_DOCUMENT answers 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 psycopg driver that this lab uses for PostgreSQL, a placeholder looks like %(region)s. You run the template with run_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

    1. Run the cell that defines QUERY_TO_DOCUMENT.
    2. 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 queries folder. 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.sql
    

    Read the header as critically as the SQL. Here the Inputs line 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 results compares 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:

    1. Replace the TODO comment in MY_TEMPLATE_SQL with a copy of QUERY_TO_DOCUMENT in which 'West' becomes %(region)s, the start date becomes %(start_date)s and the end date becomes %(end_date)s.
    2. 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 DESC
    
    Observed 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: True
    

    If 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 region West' 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:

    1. It parses, and it's exactly one statement. SELECT 1; DELETE FROM orders is two.
    2. It's a SELECT, or a UNION of them.
    3. Nothing inside it writes data. A CTE can hide a DELETE, so the function searches the whole statement.
    4. It has no INTO clause, because SELECT ... INTO creates a table.

    The function parses the SQL with sqlglot instead of searching the text for words like DROP. 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 SELECT costs. A query that joins every row to every other row, or one that calls a function such as pg_sleep(), is read-only and can still tie up the database. In production, grant the read-only user only the tables it needs, set a statement_timeout for it and cap the number of rows a query can return.


    Task 7.3: Block unsafe statements

    In the lab notebook:

    1. Run the cell that defines unsafe_region_revenue(). Its result shows all four regions, although the function was asked for one.
    2. Run the next cell, which defines is_safe_select() and eight test cases.
    3. In the Task 7.3 cell, replace the two empty strings in MY_ATTACKS with two SQL statements of your own that change or destroy data.
    4. 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.27
    

    These 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, AND binds more tightly than OR, so the injected OR '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 customers
    

    If 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_SQL is 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_id
    

    It returns no rows at all. The cause is the guest orders you saw in Step 2, which have a NULL customer_id. NOT IN compares 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_id can 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:

    1. Run the cell that defines FLAWED_SQL and confirm that it returns 0 rows.
    2. Run the next cell, where Claude reviews the query, and read its explanation and fix.
    3. In the Task 7.4 cell, replace the TODO comment in MY_FIXED_SQL with your own corrected query, using NOT EXISTS. Keep the same columns and order.
    4. Run the cell to compare your fix with Claude's.
    5. 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_id
    
    Observed 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_id
    

    Claude's explanation is slightly off. With a NULL in the list, NOT IN returns NULL (unknown), not FALSE, for a customer who isn't in the list. WHERE keeps 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  Gonzalez
    

    The 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:

    1. Give Claude your context. The schema, the business rules, the purpose of a query and the execution plan turned guesses into specific, correct answers.
    2. Pin down the output. Exact column names, a reply format and a style guide made Claude's answers easy to use and to compare.
    3. 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

Daniil Maslyuk is an independent business intelligence consultant, trainer, book author, and speaker who specializes in Microsoft Power BI.

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.

Get started with Pluralsight