{ "cells": [ { "cell_type": "markdown", "id": "w4-visible-00", "metadata": {}, "source": [ "# Week 4 — Understand ReAct by running an agent loop\n", "\n", "[![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/ralbu85/stml_2026/blob/main/lectures/week04/W4_lab_sql_manual.ipynb)\n", "\n", "**Goal:** explain how an observation changes the next action, and how ReAct writes that connection as a **Thought**.\n", "\n", "Start with the messages and one tool request you know from Week 3. First execute one exchange yourself; then repeat that same code in a loop; finally add ReAct to the same question. The comparison helps us identify the role of Thought. Basic can also solve the task.\n", "\n", "| Stage | What you do | Approx. time |\n", "|---|---|---|\n", "| §1 Prepare | Load the supplied shop and tools | 10 min |\n", "| §2 One exchange | Request → execute → save | 20 min |\n", "| §3 Repeat | Read and run the visible loop | 15 min |\n", "| §4 Add ReAct | Inspect Thought on the same question | 15 min |\n", "| §5 Apply and check | Average order value; explain one decision | 20 min |\n", "| §6 Investigate together | Category decrease → product comparison | +20 min |\n", "\n", "Sections 1–5 take about 80 minutes. Section 6 adds about 20 minutes of instructor-led practice using the same data and loop.\n", "\n", "The shop is **synthetic teaching data**, January–March 2025, in USD. Model replies are generated when you run the notebook; the labeled worked examples below are written for explanation.\n" ] }, { "cell_type": "markdown", "id": "w4-visible-01", "metadata": {}, "source": [ "## 1. Prepare the question, data, and tools\n", "\n", "Open this notebook in Colab and choose **File → Save a copy in Drive**. Run the installation and setup cells below. The setup cell automatically downloads `w4_support.py`; no separate file upload is needed. In local Jupyter, you can also keep the helper beside the notebook using `W4_lab_bundle.zip`.\n", "\n", "The provided module creates the synthetic database, evaluates arithmetic, describes the request format, and prints saved exchanges. It contains **no model calls or agent loop**. You do not need to study its implementation today.\n", "\n", "Enter your API key in the **hidden input** when prompted, or use an existing `OPENAI_API_KEY` environment variable. The key is not written into notebook source or output. Each model request uses the OpenAI SDK; Python executes the returned tool request in the notebook." ] }, { "cell_type": "code", "execution_count": null, "id": "b75054bc", "metadata": { "tags": [ "install" ] }, "outputs": [], "source": [ "%pip install -q \"openai==1.109.1\"" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-02", "metadata": { "tags": [ "setup" ] }, "outputs": [], "source": [ "import json\n", "import os\n", "import sqlite3\n", "from getpass import getpass\n", "from pathlib import Path\n", "from urllib.request import urlopen\n", "\n", "# Download the supplied data/tool utilities when opening in a fresh Colab session.\n", "SUPPORT_URL = \"https://raw.githubusercontent.com/ralbu85/stml_2026/main/lectures/week04/w4_support.py\"\n", "if not Path(\"w4_support.py\").is_file():\n", " with urlopen(SUPPORT_URL, timeout=30) as response:\n", " Path(\"w4_support.py\").write_bytes(response.read())\n", "\n", "from openai import OpenAI\n", "from w4_support import prepare_shop, calculate, finish, action_tools, show_exchange, show_trace, DB_CONTEXT\n", "\n", "API_KEY = os.environ.get(\"OPENAI_API_KEY\") or getpass(\"OpenAI API key (input hidden): \")\n", "client = OpenAI(api_key=API_KEY)\n", "MODEL = \"gpt-4.1\"\n", "MAX_CALLS = 10\n", "DB_PATH = prepare_shop(Path(\"outputs/w4_sql_manual/shop.sqlite\"))\n", "print(\"Ready:\", DB_PATH)" ] }, { "cell_type": "markdown", "id": "w4-visible-03", "metadata": {}, "source": [ "### What is in the shop?\n", "\n", "Our question is: **What were total sales from completed orders in March 2025?**\n", "\n", "`orders` has one row per order, including its date and status. `order_items` has one row per product within an order, including quantity and unit price. Match them using `order_id`.\n", "\n", "| orders: order_id | ordered_at | status |\n", "|---|---|---|\n", "| 182 | 2025-03-02 | completed |\n", "| 183 | 2025-03-03 | completed |\n", "\n", "| order_items: order_id | quantity | unit_price |\n", "|---|---|---|\n", "| 182 | 2 | 25 |\n", "| 182 | 3 | 10 |\n", "| 183 | 2 | 10 |\n", "| 183 | 3 | 10 |\n", "\n", "These are four actual rows from the generated data. Order 182 contributes `2 × 25 + 3 × 10 = 80` USD. For total sales, select **all completed March orders** and sum their item amounts. The sample alone is not the answer.\n", "\n", "### What can Python execute?\n", "\n", "| Operation | Input from the model | Result returned to the model |\n", "|---|---|---|\n", "| `run_sql` | One read-only SQL query | Column names, rows, and row count; or an error |\n", "| `calculate` | Arithmetic such as `3055 / 57` | A number; or an error |\n", "| `finish` | Final answer text | Submitted answer; an empty answer returns an error |\n", "\n", "The next cell defines `run_sql`. It reads our database without changing it. A SQL error is returned as an observation that the model can use to revise its request.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-04", "metadata": { "tags": [ "sql-tool" ] }, "outputs": [], "source": [ "def run_sql(query: str) -> dict:\n", " \"\"\"Execute one read-only SQLite query. Return all rows and row_count, or an error if the result exceeds 1,000 rows.\n", "\n", " Args:\n", " query: One read-only SQLite query using the supplied schema.\n", " \"\"\"\n", " try:\n", " with sqlite3.connect(f'file:{DB_PATH.resolve()}?mode=ro', uri=True) as db:\n", " db.execute('PRAGMA query_only = ON')\n", " cursor = db.execute(query)\n", " rows = cursor.fetchmany(1001)\n", " if len(rows) > 1000:\n", " return {'error': 'Result exceeds 1,000 rows. No partial result was returned. Submit a query with a smaller result.'}\n", " return {'columns': [c[0] for c in cursor.description or []],\n", " 'rows': rows, 'row_count': len(rows)}\n", " except sqlite3.Error as error:\n", " return {'error': str(error)}\n" ] }, { "cell_type": "markdown", "id": "w4-visible-05", "metadata": {}, "source": [ "### Give the model the same information\n", "\n", "`BASE` states the task rules; `DB_CONTEXT` lists the table columns and row meanings. `BASIC_TOOLS` describes the JSON request format for a native tool named `act`: **`action` chooses an operation; `action_input` holds its input**.\n", "\n", "`operations` maps those names to Python functions. The schema describes a request; this dictionary lets our code execute it. Neither one selects the next action.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-06", "metadata": { "tags": [ "instructions" ] }, "outputs": [], "source": [ "BASE = \"\"\"Answer the user's question using this shop database. Respond in English.\n", "Sales are quantity * unit_price for completed orders; exclude cancelled orders.\n", "Use SQL to filter, join, and aggregate database rows. Count the requested unit:\n", "an order may have several item rows. Use calculate for arithmetic on returned\n", "numbers when needed; SQL may also compute the final value directly.\n", "Consult the data before answering. Reuse returned evidence; correct failed queries.\n", "Submit the supported answer with finish, stating any unresolved limitation.\n", "A successful query or finish does not prove that an answer is correct.\n", "\"\"\" + DB_CONTEXT\n", "BASIC_TOOLS = action_tools()\n", "operations = {\"run_sql\": run_sql, \"calculate\": calculate, \"finish\": finish}\n", "question = \"What were total sales from completed orders in March 2025?\"\n" ] }, { "cell_type": "markdown", "id": "w4-visible-07", "metadata": {}, "source": [ "## 2. Execute one exchange yourself\n", "\n", "### A. Ask the model for a request\n", "\n", "`messages` is the conversation sent to the model. It starts with the shared instructions (`system`) and our question (`user`).\n", "\n", "The API returns a message containing one `act` tool call. `tool_choice=\"required\"` requires a tool request; `parallel_tool_calls=False` limits it to one per call. These options **do not run Python**. Parse the JSON arguments into `request` and read what it asks for.\n", "\n", "Before running: what month and status should a correct first SQL query select?\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-08", "metadata": { "tags": [ "manual-request" ] }, "outputs": [], "source": [ "messages = [\n", " {\"role\": \"system\", \"content\": BASE},\n", " {\"role\": \"user\", \"content\": question},\n", "]\n", "response = client.chat.completions.create(\n", " model=MODEL, messages=messages, tools=BASIC_TOOLS,\n", " tool_choice=\"required\", parallel_tool_calls=False,\n", " temperature=0, max_tokens=2400,\n", ")\n", "message = response.choices[0].message\n", "if len(message.tool_calls or []) != 1:\n", " raise RuntimeError(\"Expected one tool request; inspect response before continuing.\")\n", "call = message.tool_calls[0]\n", "request = json.loads(call.function.arguments)\n", "print(json.dumps(request, indent=2))\n" ] }, { "cell_type": "markdown", "id": "w4-visible-09", "metadata": {}, "source": [ "### B. Execute that request in Python\n", "\n", "For example, if `request[\"action\"]` is `run_sql`, the dictionary selects our SQL function. The next line passes `action_input` to it and stores the returned value as `result`. This is the **Observation**.\n", "\n", "No new model call occurs in this cell. Read the actual query and result together.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-10", "metadata": { "tags": [ "manual-execute" ] }, "outputs": [], "source": [ "result = operations[request[\"action\"]](request[\"action_input\"])\n", "print(json.dumps(result, indent=2))\n" ] }, { "cell_type": "markdown", "id": "w4-visible-11", "metadata": {}, "source": [ "**Worked example — authored SQL, independently executed result.** Your model may write a different query.\n", "\n", "```sql\n", "SELECT SUM(quantity * unit_price) AS sales\n", "FROM orders JOIN order_items USING (order_id)\n", "WHERE status = 'completed' AND ordered_at LIKE '2025-03%';\n", "```\n", "\n", "It returns `{\"columns\": [\"sales\"], \"rows\": [[3055.0]], \"row_count\": 1}`. This means **one returned total of 3,055 USD**. `row_count=1` does not mean one order. If your result differs, inspect the filters and measured quantity. If it is an error, the next model request should repair the query.\n", "\n", "### C. Save the request and result for the next call\n", "\n", "The model does not automatically see what Python returned. Append **both** messages: the assistant's tool request and the tool's result. `tool_call_id` connects a result to its request; `model_dump` converts the SDK message into a dictionary without dropping its arguments.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-12", "metadata": { "tags": [ "manual-save" ] }, "outputs": [], "source": [ "messages.append(message.model_dump(exclude_none=True))\n", "messages.append({\"role\": \"tool\", \"tool_call_id\": call.id,\n", " \"content\": json.dumps(result)})\n", "print(\"Saved roles:\", [m[\"role\"] for m in messages])\n", "show_trace(messages)\n" ] }, { "cell_type": "markdown", "id": "w4-visible-13", "metadata": {}, "source": [ "You should now see `system → user → assistant → tool`. The next API call can use this updated `messages` list to decide whether to query again or submit an answer.\n", "\n", "**Pause:** which line executes Python? Which two lines make the next model call aware of that execution? What would the model be missing if you saved only the request?\n", "\n", "Run A → B → C in order once. To retry this demonstration, restart at A; rerunning only C duplicates the exchange. If the API fails in A, fix the key, network, or access error before continuing.\n", "\n", "## 3. Repeat the same exchange in a loop\n", "\n", "`run_loop` below places A → B → C inside `for`. It starts a **fresh conversation** for the supplied question, so the manual demonstration stays separate. Each next iteration receives the list updated by the previous iteration.\n", "\n", "The return value contains `answer`, saved `messages`, and the number of model `calls`. A successful `finish` returns the submitted answer. Errors stay in the history so the next request can address them. At the call limit, the function returns `answer=None`: stopping is not the same as answering.\n", "\n", "Read the code and locate the four operations: **model request → Python execution → history update → stopping decision**.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-14", "metadata": { "tags": [ "loop-definition" ] }, "outputs": [], "source": [ "def run_loop(question, instructions=BASE, tools=BASIC_TOOLS, max_calls=MAX_CALLS):\n", " messages = [{\"role\": \"system\", \"content\": instructions},\n", " {\"role\": \"user\", \"content\": question}]\n", " for turn in range(max_calls):\n", " # A. Ask for the next request using all messages so far.\n", " response = client.chat.completions.create(\n", " model=MODEL, messages=messages, tools=tools,\n", " tool_choice=\"required\", parallel_tool_calls=False,\n", " temperature=0, max_tokens=2400,\n", " )\n", " message = response.choices[0].message\n", " if len(message.tool_calls or []) != 1:\n", " raise RuntimeError(\"Expected one tool request; inspect response.\")\n", " call = message.tool_calls[0]\n", " request = json.loads(call.function.arguments)\n", " # B. Execute the selected Python operation.\n", " result = operations[request[\"action\"]](request[\"action_input\"])\n", " # C. Save both sides of the exchange for the next model call.\n", " messages.append(message.model_dump(exclude_none=True))\n", " messages.append({\"role\": \"tool\", \"tool_call_id\": call.id,\n", " \"content\": json.dumps(result)})\n", " print(f\"\\n--- Exchange {turn + 1} ---\")\n", " show_exchange(request, result)\n", " # D. Stop only after a successful answer submission.\n", " if request[\"action\"] == \"finish\" and \"answer\" in result:\n", " return {\"answer\": result[\"answer\"], \"messages\": messages, \"calls\": turn + 1}\n", " print(\"Call limit reached without a submitted answer.\")\n", " return {\"answer\": None, \"messages\": messages, \"calls\": max_calls}\n" ] }, { "cell_type": "markdown", "id": "w4-visible-15", "metadata": {}, "source": [ "Run the total-sales question from the beginning. Read the first SQL observation and predict the next request before reading it. The model may retrieve more information; it should finish once the question is supported.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-16", "metadata": { "tags": [ "basic-run" ] }, "outputs": [], "source": [ "basic = run_loop(question)\n", "print(\"Basic answer:\", basic[\"answer\"])\n" ] }, { "cell_type": "markdown", "id": "w4-visible-17", "metadata": {}, "source": [ "## 4. Add ReAct to the same loop\n", "\n", "A **Thought** is the model's written assessment of the evidence, remaining uncertainty, and next action. In ReAct, this assessment is updated after an Observation and guides the next Action or `finish`. Written reasoning can be wrong; check it against the query and returned values.\n", "\n", "We change only the reasoning instruction and request format. `REACT_TOOLS` adds a `thought` field before `action` and `action_input`. The loop, question, model, data, operations, temperature, and limits remain the same. The loop saves the full request, so the Thought is also part of the next call's history.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-18", "metadata": { "tags": [ "react-run" ] }, "outputs": [], "source": [ "REACT = \"\"\"\n", "Before each action, write thought: assess the evidence available so far,\n", "what is still missing, and why this next action addresses it.\n", "After an observation, check what the query or calculation actually measured.\n", "If it was wrong, revise the next action. Before finish, explain how the\n", "observations support each requested part of the answer or leave a limitation.\n", "\"\"\"\n", "REACT_TOOLS = action_tools(react=True)\n", "react = run_loop(question, instructions=BASE + REACT, tools=REACT_TOOLS)\n", "print(\"ReAct answer:\", react[\"answer\"])\n" ] }, { "cell_type": "markdown", "id": "w4-visible-19", "metadata": {}, "source": [ "### Find where the Observation changed the next decision\n", "\n", "The following cell **only reads saved messages**; it makes no API or tool calls. Choose one SQL result and read the next request. Basic may choose the same action. Identify what the added Thought explains.\n", "\n", "For example, after the total-sales query returns 3055, an authored Thought could be: *“The query selected completed March orders and summed their item amounts. The returned total answers the question, so I can finish.”* This is an explanation to assess, not proof that ReAct is more accurate.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-20", "metadata": { "tags": [ "inspect-trace" ] }, "outputs": [], "source": [ "show_trace(react[\"messages\"])\n", "# To inspect the Basic exchange instead: show_trace(basic[\"messages\"])\n" ] }, { "cell_type": "markdown", "id": "w4-visible-21", "metadata": {}, "source": [ "## 5. Apply it: average value per order\n", "\n", "Change the question: **What was the average value of a completed order in March 2025?**\n", "\n", "Keep the same month and order status. We already know the numerator is total sales. What should the denominator count? Order 182 has two item rows but is still **one order**. Predict the required SQL result before running.\n", "\n", "The two calls below each start a fresh conversation. A model may request total sales and order count, then calculate their ratio; it may also compute the correct average directly in SQL.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-22", "metadata": { "tags": [ "average-runs" ] }, "outputs": [], "source": [ "average_question = \"What was the average value of a completed order in March 2025?\"\n", "basic_average = run_loop(average_question)\n", "react_average = run_loop(average_question, instructions=BASE + REACT, tools=REACT_TOOLS)\n" ] }, { "cell_type": "markdown", "id": "w4-visible-23", "metadata": {}, "source": [ "### Check the answer independently\n", "\n", "The next cell contains an instructor-written reference query. It calls only the local SQL and calculator functions, **not the model**. It does not enter either agent's history or fix its answer.\n", "\n", "After joining items, `COUNT(*)` counts item rows; `COUNT(DISTINCT order_id)` counts each order once. Inspect the filters as well as the numbers.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "w4-visible-24", "metadata": { "tags": [ "independent-check" ] }, "outputs": [], "source": [ "reference = run_sql(\"\"\"\n", "SELECT COUNT(*) AS item_rows, COUNT(DISTINCT order_id) AS orders,\n", " SUM(quantity * unit_price) AS sales\n", "FROM orders JOIN order_items USING (order_id)\n", "WHERE status = 'completed' AND ordered_at LIKE '2025-03%';\n", "\"\"\")\n", "print(reference)\n", "print(calculate(\"3055 / 57\"))\n" ] }, { "cell_type": "markdown", "id": "w4-visible-25", "metadata": {}, "source": [ "The reference is **116 item rows, 57 orders, and 3,055 USD**; the average is **53.60 USD per completed order**, rounded. A valid submitted answer still needs to match the requested unit and population.\n", "\n", "**Use one saved exchange to answer:**\n", "\n", "1. What did its query actually measure? Which result supports that interpretation?\n", "2. What did the next Thought infer, and did the next action follow from it?\n", "3. If both methods succeeded, what does the Thought make explicit? One pair does not establish an accuracy advantage.\n", "4. If a query failed or the answer was wrong, did the later request repair the error? If `answer` is `None`, inspect the last exchange and explain why the budget ended.\n", "\n", "**Try one change:** replace March with February in `average_question` and rerun the two calls in the previous cell. Change `2025-03%` to `2025-02%` in the independent query too. Compute sales divided by distinct orders using the newly returned numbers; do not reuse March's answer. Explain one transition before comparing call counts." ] }, { "cell_type": "markdown", "id": "e4630f4b", "metadata": { "tags": [ "sales-extension-intro" ] }, "source": [ "## 6. Investigate together: where did sales decrease?\n", "\n", "We have measured total sales and average order value. Now compare **February and March 2025** and use the first result to choose what to investigate next.\n", "\n", "The question is where the decrease appears in the sales records. These records do not contain inventory levels, advertising, or customer reasons.\n", "\n", "Use the same `run_loop`, ReAct instructions, tools, and database. We already compared Basic and ReAct; these two activities concentrate on interpreting a saved Observation and the next Thought or action.\n", "\n", "`products` supplies **`category`** and **`name`**. Join it to `order_items` by `product_id`, and join the items to `orders` by `order_id` for dates and completion status. Its categories are Electronics, Home, and Stationery.\n", "\n", "### 6.1 Compare categories before selecting a product\n", "\n", "**Before running together:** the total can reveal that sales decreased, but can it reveal which category changed? What should the next query return?\n", "\n", "Request both months and define **change = March sales − February sales**. A negative change is a decrease." ] }, { "cell_type": "code", "execution_count": null, "id": "1c05db4d", "metadata": { "tags": [ "category-run" ] }, "outputs": [], "source": [ "category_question = (\n", " \"Compare completed sales in February and March 2025 by category. \"\n", " \"Show each month's sales and the change (March minus February), \"\n", " \"plus the overall change. Which category contributed most to the decrease?\"\n", ")\n", "category_run = run_loop(category_question, instructions=BASE + REACT, tools=REACT_TOOLS)\n", "print(\"Category comparison:\", category_run[\"answer\"])" ] }, { "cell_type": "markdown", "id": "9a964482", "metadata": { "tags": [ "category-discussion" ] }, "source": [ "Read one category query and its returned values, then read the next Thought.\n", "\n", "- Did the query select both months and completed orders?\n", "- Is there enough evidence to answer the category question, or is another operation needed?\n", "- Which category should we investigate next, and which returned number supports that choice?\n", "\n", "A model may calculate all requested values in one SQL query. That is valid if the result supports the answer; extra tool calls are not a goal. If `answer` is `None`, use the saved trace to find what remains unresolved.\n", "\n", "The next cell redisplays saved messages without another API call." ] }, { "cell_type": "code", "execution_count": null, "id": "8e7cabe1", "metadata": { "tags": [ "category-trace" ] }, "outputs": [], "source": [ "show_trace(category_run[\"messages\"])" ] }, { "cell_type": "markdown", "id": "44bf583a", "metadata": { "tags": [ "category-reference-intro" ] }, "source": [ "### Check the category values independently\n", "\n", "This instructor-written query runs locally. It does not enter the model's history or change its answer. `CASE` places each item's amount in the corresponding month's sum." ] }, { "cell_type": "code", "execution_count": null, "id": "60f38c7d", "metadata": { "tags": [ "category-reference" ] }, "outputs": [], "source": [ "category_reference = run_sql(\"\"\"\n", "WITH category_sales AS (\n", " SELECT p.category,\n", " SUM(CASE WHEN o.ordered_at LIKE '2025-02%'\n", " THEN i.quantity * i.unit_price ELSE 0 END) AS feb_sales,\n", " SUM(CASE WHEN o.ordered_at LIKE '2025-03%'\n", " THEN i.quantity * i.unit_price ELSE 0 END) AS mar_sales\n", " FROM orders AS o\n", " JOIN order_items AS i USING (order_id)\n", " JOIN products AS p USING (product_id)\n", " WHERE o.status = 'completed'\n", " AND o.ordered_at >= '2025-02-01' AND o.ordered_at < '2025-04-01'\n", " GROUP BY p.category\n", ")\n", "SELECT category, feb_sales, mar_sales, mar_sales - feb_sales AS change\n", "FROM category_sales\n", "ORDER BY change;\n", "\"\"\")\n", "print(json.dumps(category_reference, indent=2))" ] }, { "cell_type": "markdown", "id": "7cba0784", "metadata": { "tags": [ "product-investigation-intro" ] }, "source": [ "For the supplied data, the independent result is:\n", "\n", "| Category | February sales (USD) | March sales (USD) | Change (USD) |\n", "|---|---:|---:|---:|\n", "| Electronics | 5,710 | 280 | −5,430 |\n", "| Stationery | 1,075 | 835 | −240 |\n", "| Home | 1,345 | 1,940 | +595 |\n", "| **Total** | **8,130** | **3,055** | **−5,075** |\n", "\n", "**Discuss together:** why can the Electronics decrease exceed the overall decrease? Home increased, offsetting part of the decline. Check that `−5,430 − 240 + 595 = −5,075`.\n", "\n", "Electronics is the category with the largest decrease. We now have a reason to inspect its products; the total alone did not identify that next target.\n", "\n", "### 6.2 Use that finding to choose the next question\n", "\n", "`run_loop` starts a **fresh conversation** each time. The previous call's messages are not automatically included. Put the category selected from the observed result into `CATEGORY_TO_INSPECT`; the new question also states the dates explicitly.\n", "\n", "For the main walkthrough use Electronics. Home and Stationery are available for an optional follow-up. The category question is now answered; the class is asking a more detailed product question.\n", "\n", "The prompt includes an SQL hint: total the eligible sales first, then join those totals to the product list. This keeps the focus on reading the Observation and judging the next action.\n", "\n", "**Before running:** if a product sold in February but not in March, should it disappear from the comparison, or appear with March sales of zero?" ] }, { "cell_type": "code", "execution_count": null, "id": "28ab3d23", "metadata": { "tags": [ "product-run" ] }, "outputs": [], "source": [ "CATEGORY_TO_INSPECT = \"Electronics\" # Choose from the category result above.\n", "assert CATEGORY_TO_INSPECT in {\"Electronics\", \"Home\", \"Stationery\"}\n", "product_question = (\n", " f\"For {CATEGORY_TO_INSPECT}, compare completed sales by product in February \"\n", " \"and March 2025. Show each month's sales and the change (March minus February). \"\n", " \"Keep every product in this category, showing zero for a month with no \"\n", " \"completed sales. Aggregate completed order items by product and month first, \"\n", " \"then join those totals to the full product list and fill missing totals with zero. \"\n", " \"Which products sold in February but not March? \"\n", " \"What can these sales records establish, and what remains unknown \"\n", " \"about why the sales changed?\"\n", ")\n", "product_run = run_loop(product_question, instructions=BASE + REACT, tools=REACT_TOOLS)\n", "print(\"Product comparison:\", product_run[\"answer\"])" ] }, { "cell_type": "markdown", "id": "4e86e329", "metadata": { "tags": [ "product-discussion" ] }, "source": [ "Inspect the SQL, its Observation, and the following Thought.\n", "\n", "- Does the result include products with zero completed sales in March? Do the product totals agree with the category result above?\n", "- Does the Thought interpret zero as **no completed sales in the period**, or invent a reason such as a stockout?\n", "- Does the next action obtain needed evidence, or finish with a supported answer and a clear limit?\n", "\n", "If the query failed, inspect whether the next request uses the error to repair it. A query that runs successfully can still omit the products we needed to compare." ] }, { "cell_type": "code", "execution_count": null, "id": "83bc4e96", "metadata": { "tags": [ "product-trace" ] }, "outputs": [], "source": [ "show_trace(product_run[\"messages\"])" ] }, { "cell_type": "markdown", "id": "b755dee0", "metadata": { "tags": [ "product-reference-intro" ] }, "source": [ "### Check that products with zero sales stay in the result\n", "\n", "The reference below first totals completed sales across both months. It then starts from **all products in the selected category** and uses `LEFT JOIN` to keep products even when the totals are absent. `COALESCE(..., 0)` displays a missing total as zero.\n", "\n", "This is a reference query, not a required query template for the model. Other SQL is valid if it returns the same requested products and values. It uses the same `CATEGORY_TO_INSPECT` selected above.\n", "\n", "**A possible SQL error without an exception:** restricting `orders` in a `LEFT JOIN` does not remove unmatched `order_items`. Summing those item amounts can include the wrong dates or cancelled orders. Filtering and aggregating the eligible sales first avoids this mistake." ] }, { "cell_type": "code", "execution_count": null, "id": "f6459e68", "metadata": { "tags": [ "product-reference" ] }, "outputs": [], "source": [ "product_reference = run_sql(f\"\"\"\n", "WITH product_sales AS (\n", " SELECT i.product_id,\n", " SUM(CASE WHEN o.ordered_at LIKE '2025-02%'\n", " THEN i.quantity * i.unit_price ELSE 0 END) AS feb_sales,\n", " SUM(CASE WHEN o.ordered_at LIKE '2025-03%'\n", " THEN i.quantity * i.unit_price ELSE 0 END) AS mar_sales\n", " FROM orders AS o JOIN order_items AS i USING (order_id)\n", " WHERE o.status = 'completed'\n", " AND o.ordered_at >= '2025-02-01' AND o.ordered_at < '2025-04-01'\n", " GROUP BY i.product_id\n", ")\n", "SELECT p.name,\n", " COALESCE(s.feb_sales, 0) AS feb_sales,\n", " COALESCE(s.mar_sales, 0) AS mar_sales,\n", " COALESCE(s.mar_sales, 0) - COALESCE(s.feb_sales, 0) AS change\n", "FROM products AS p\n", "LEFT JOIN product_sales AS s USING (product_id)\n", "WHERE p.category = '{CATEGORY_TO_INSPECT}'\n", "ORDER BY change;\n", "\"\"\")\n", "print(json.dumps(product_reference, indent=2))" ] }, { "cell_type": "markdown", "id": "16526aea", "metadata": { "tags": [ "sales-extension-wrapup" ] }, "source": [ "For **Electronics**, compare your result with these independently checked values:\n", "\n", "| Product | February sales (USD) | March sales (USD) | Change (USD) |\n", "|---|---:|---:|---:|\n", "| Headphones | 2,400 | 0 | −2,400 |\n", "| Keyboard | 2,300 | 0 | −2,300 |\n", "| Mouse | 650 | 0 | −650 |\n", "| Cable | 360 | 280 | −80 |\n", "\n", "Their changes total **−5,430 USD**, matching the category result. Headphones, Keyboard, and Mouse account for −5,350 USD; Cable contributes the remaining −80 USD. A March-only list would miss the first three products.\n", "\n", "These records establish the change in **completed sales**, not whether the products were out of stock, removed from sale, or no longer wanted. Those claims would require additional data. It is appropriate to finish the answer with that limitation.\n", "\n", "**Close the activity together:** point to one Observation that justified a next action or a final answer. Then explain why the class selected Electronics for the second question. The two questions are connected by that finding; they do not share a conversation automatically.\n", "\n", "If time permits, select Home in `CATEGORY_TO_INSPECT` and rerun the product activity and reference. Explain the increases using the newly returned values." ] }, { "cell_type": "markdown", "id": "77a405bc", "metadata": { "tags": [ "sources" ] }, "source": [ "### Sources\n", "\n", "API message pattern: [OpenAI function-calling guide](https://developers.openai.com/api/docs/guides/function-calling). The visible loop is course-authored. ReAct concept: [Yao et al., ReAct](https://arxiv.org/abs/2210.03629)." ] } ], "metadata": { "colab": { "name": "W4_lab_sql_manual.ipynb", "provenance": [] }, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.11" } }, "nbformat": 4, "nbformat_minor": 5 }