{ "cells": [ { "cell_type": "markdown", "id": "d705c787", "metadata": { "tags": [ "hw-intro" ] }, "source": [ "# W4 Homework — Follow the Evidence in a Sales Investigation\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_hw_sales_investigation.ipynb)\n", "\n", "**Goal:** complete the agent loop, use an observed result to select a follow-up question, and explain why the next action or final answer follows from the evidence.\n", "\n", "Use the same synthetic shop, tools, and ReAct instructions as the lab. This homework compares **January and February 2025**; it does not reuse the lab's February–March answers. All sales are in USD and include only completed orders. Define **change = February sales − January sales**.\n", "\n", "1. Complete the loop's execute → save → stop block.\n", "2. Compare categories, then select the category with the largest increase and investigate its products.\n", "3. Record your findings and explain one Observation → next decision transition.\n", "4. Run the format check and submit the executed notebook on the LMS. Due date: see the LMS.\n", "\n", "The student check verifies **format only**: fields, types, and the structure of saved records. It does not compare your numbers with correct answers or award a correctness score. Loop behavior, findings, and explanations are graded after submission.\n", "\n", "Allow about 60–75 minutes. Complete the marked `FILL IN` blocks before running the cells that use them. You may refer to the lab." ] }, { "cell_type": "markdown", "id": "9cd4beef", "metadata": { "tags": [ "hw-setup-intro" ] }, "source": [ "## 1. Setup and identity\n", "\n", "In Colab choose **File → Save a copy in Drive**, then run these cells. The helper downloads automatically. Enter your API key in the hidden input when prompted; do not paste it into notebook code. Local Jupyter can use an existing `OPENAI_API_KEY` environment variable.\n", "\n", "The helper supplies data generation, arithmetic, request descriptions, and trace display. The model call and loop remain visible below. This is the same seeded teaching database as the lab, not real customer transactions." ] }, { "cell_type": "code", "execution_count": null, "id": "df43e236", "metadata": { "tags": [ "install" ] }, "outputs": [], "source": [ "%pip install -q \"openai==1.109.1\"" ] }, { "cell_type": "code", "execution_count": null, "id": "83769be9", "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_homework/shop.sqlite\"))\n", "print(\"Ready:\", DB_PATH)" ] }, { "cell_type": "code", "execution_count": null, "id": "fe87e008", "metadata": { "tags": [ "identity" ] }, "outputs": [], "source": [ "### FILL IN (START) ###\n", "STUDENT_NAME = \"\"\n", "STUDENT_ID = \"\"\n", "### FILL IN (END) ###\n", "print(\"submitting as:\", STUDENT_NAME or \"?\", STUDENT_ID or \"?\")" ] }, { "cell_type": "markdown", "id": "1935b5cf", "metadata": { "tags": [ "hw-tools-intro" ] }, "source": [ "### Provided tool and model instructions\n", "\n", "`run_sql(query)` returns column names, rows, and a row count, or an error. `calculate` accepts numeric arithmetic expressions; `finish` submits an answer without grading it. SQL can compute the requested values directly. A single sufficient query is valid.\n", "\n", "`orders` supplies `ordered_at` and `status`. `order_items` supplies `quantity` and `unit_price`. `products` supplies each product's `name` and `category`. Join on `order_id` and `product_id` as in the lab. Use `quantity * unit_price` for sales." ] }, { "cell_type": "code", "execution_count": null, "id": "c57d7d98", "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)}" ] }, { "cell_type": "code", "execution_count": null, "id": "030ef6b1", "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", "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)" ] }, { "cell_type": "markdown", "id": "5bda6887", "metadata": { "tags": [ "hw-loop-intro" ] }, "source": [ "## 2. Complete execute → save → stop\n", "\n", "The request step A is provided. Complete B–D inside `run_loop`:\n", "\n", "- Call the Python operation named by the model with its supplied input.\n", "- Append the **full assistant message** and the **actual tool result** to `messages`. The tool message must use the same `call.id` so that the next model call can use the Observation.\n", "- Display the exchange using `show_exchange(request, result)`.\n", "- Stop only when `finish` returns an `answer`. Return `{\"answer\": ..., \"messages\": messages, \"calls\": turn + 1}`.\n", "\n", "An error is also an Observation: save it and allow the next request to respond to it. At the call limit, the provided final lines return `answer=None`. Do not add model calls outside the loop or change the supplied database." ] }, { "cell_type": "code", "execution_count": null, "id": "a6498f63", "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", " # Complete B–D using the same contract as the lab.\n", " ### FILL IN (START) ###\n", " # B. Execute request[\"action\"] with request[\"action_input\"].\n", " # C. Save the assistant message and the tool result, including call.id.\n", " # Use json.dumps(result) for the tool message content.\n", " # D. Return answer/messages/calls only after a successful finish.\n", " raise NotImplementedError(\"Complete execution, message saving, and stopping.\")\n", " ### FILL IN (END) ###\n", " print(\"Call limit reached without a submitted answer.\")\n", " return {\"answer\": None, \"messages\": messages, \"calls\": max_calls}" ] }, { "cell_type": "markdown", "id": "aa5dcf76", "metadata": { "tags": [ "hw-category-intro" ] }, "source": [ "## 3. Compare categories, then choose a follow-up\n", "\n", "### 3.1 Run the category comparison\n", "\n", "Run the question below with your completed loop. Read the SQL filters, returned values, and the next Thought. Check that the months are January and February 2025 and that cancelled orders are excluded.\n", "\n", "If `answer` is `None`, inspect the saved messages and repair the missing loop behavior or clarify the question before rerunning. A completed run can still contain incorrect SQL or an unsupported answer." ] }, { "cell_type": "code", "execution_count": null, "id": "79588dc0", "metadata": { "tags": [ "category-run" ] }, "outputs": [], "source": [ "category_question = (\n", " \"Compare completed sales in January and February 2025 by category. \"\n", " \"Show each month's sales and the change (February minus January), \"\n", " \"plus the overall change. Which category has the largest increase?\"\n", ")\n", "category_run = run_loop(category_question, instructions=BASE + REACT, tools=REACT_TOOLS)\n", "print(\"Category answer:\", category_run[\"answer\"])" ] }, { "cell_type": "markdown", "id": "75f8e9ba", "metadata": { "tags": [ "hw-product-intro" ] }, "source": [ "### 3.2 Select a category from that result\n", "\n", "Set `CATEGORY_TO_INSPECT` to the category with the largest increase. Do not copy the lab's choice without checking this period's result.\n", "\n", "Each `run_loop` call starts a **fresh conversation**. The new question must explicitly include the selected category and dates. The question below does that for you. It also asks for every product, including zero sales in a month. Its SQL hint separates eligible sales from the full product list." ] }, { "cell_type": "code", "execution_count": null, "id": "87a49684", "metadata": { "tags": [ "product-run" ] }, "outputs": [], "source": [ "### FILL IN (START) ###\n", "CATEGORY_TO_INSPECT = \"\"\n", "### FILL IN (END) ###\n", "if not CATEGORY_TO_INSPECT.strip():\n", " raise ValueError(\"Select the category from the preceding result first.\")\n", "product_question = (\n", " f\"For {CATEGORY_TO_INSPECT}, compare completed sales by product in January \"\n", " \"and February 2025. Show each month's sales and the change (February minus January). \"\n", " \"Keep every product in this category, showing zero for a month with no completed sales. \"\n", " \"Aggregate completed order items by product and month first, then join those totals \"\n", " \"to the full product list and fill missing totals with zero. \"\n", " \"Which products account for the category's change? \"\n", " \"What can these sales records establish, and what remains unknown about why sales changed?\"\n", ")\n", "product_run = run_loop(product_question, instructions=BASE + REACT, tools=REACT_TOOLS)\n", "print(\"Product answer:\", product_run[\"answer\"])" ] }, { "cell_type": "markdown", "id": "ee6490ad", "metadata": { "tags": [ "hw-traces-intro" ] }, "source": [ "### Read the saved exchanges\n", "\n", "The next cell makes no API calls. Use the saved SQL and actual Observations to check the scope, missing products, and whether product changes add to the category change. You may use `run_sql` for additional independent queries in a new cell above the final format check; record corrections in your explanation." ] }, { "cell_type": "code", "execution_count": null, "id": "9071c1a7", "metadata": { "tags": [ "inspect-trace" ] }, "outputs": [], "source": [ "show_trace(category_run[\"messages\"])\n", "show_trace(product_run[\"messages\"])" ] }, { "cell_type": "markdown", "id": "3c472843", "metadata": { "tags": [ "hw-findings-intro" ] }, "source": [ "## 4. Record findings and explain a decision\n", "\n", "Enter the findings as **literal lists of dictionaries**, using numeric USD amounts rather than formatted strings. Include every category and every product in the selected category. Do not paste a model's formatted table as one string.\n", "\n", "- `category_findings`: each row has `category` (text), `jan_sales`, `feb_sales`, and `change` (numbers).\n", "- `product_findings`: each row has `product` (text), `jan_sales`, `feb_sales`, and `change` (numbers).\n", "\n", "Use the actual returned evidence. If the model's answer conflicts with the data, inspect or query further, enter the corrected findings, and explain the correction. No expected answers are included in this notebook." ] }, { "cell_type": "code", "execution_count": null, "id": "d6b4156e", "metadata": { "tags": [ "findings" ] }, "outputs": [], "source": [ "### FILL IN (START) ###\n", "category_findings = []\n", "product_findings = []\n", "### FILL IN (END) ###" ] }, { "cell_type": "markdown", "id": "8a9cdca5", "metadata": { "tags": [ "hw-explanation-intro" ] }, "source": [ "### Explain one transition in your own words\n", "\n", "Choose a non-final Observation and the next request from one saved run. `run` is `\"categories\"` or `\"products\"`; `observation_exchange` is its **1-based exchange number** in the trace.\n", "\n", "In `observation`, identify the returned value or error you used. In `next_decision`, describe the following Thought and Action and explain whether they were justified. In `category_choice`, explain the numerical reason for selecting your category and why the follow-up needed to state it again. In `limitation`, distinguish the measured change from a cause that would require other data. Use `correction` for any discrepancy you repaired, or say that you found none and state what you checked." ] }, { "cell_type": "code", "execution_count": null, "id": "75cc30d9", "metadata": { "tags": [ "explanation" ] }, "outputs": [], "source": [ "### FILL IN (START) ###\n", "explanation = {\n", " \"run\": \"\",\n", " \"observation_exchange\": None,\n", " \"observation\": \"\",\n", " \"next_decision\": \"\",\n", " \"category_choice\": \"\",\n", " \"limitation\": \"\",\n", " \"correction\": \"\",\n", "}\n", "### FILL IN (END) ###" ] }, { "cell_type": "markdown", "id": "96814502", "metadata": { "tags": [ "hw-format-intro" ] }, "source": [ "## 5. Format check and submission\n", "\n", "This check tests **only submission format**. `PASS` and `HOMEWORK COMPLETE` mean the required fields and records are present; they do **not** mean the answer or loop is correct. Correctness is assessed after submission by the instructor.\n", "\n", "Run the notebook from top to bottom after completing your work. Keep the model/tool outputs, run this check last, then use **File → Download → Download .ipynb** and upload the file to the LMS by its listed deadline. Submit the notebook itself, not screenshots. The hidden API input is not saved in the notebook." ] }, { "cell_type": "code", "execution_count": null, "id": "b315d74e", "metadata": { "tags": [ "format-check" ] }, "outputs": [], "source": [ "import math\n", "\n", "def text_filled(value):\n", " return isinstance(value, str) and bool(value.strip())\n", "\n", "def table_format(rows, label):\n", " fields = {label, \"jan_sales\", \"feb_sales\", \"change\"}\n", " return isinstance(rows, list) and bool(rows) and all(\n", " isinstance(row, dict) and set(row) == fields and text_filled(row[label])\n", " and all(type(row[key]) in (int, float) and math.isfinite(row[key])\n", " for key in (\"jan_sales\", \"feb_sales\", \"change\"))\n", " for row in rows\n", " )\n", "\n", "def run_format(run):\n", " if not isinstance(run, dict) or not {\"answer\", \"messages\", \"calls\"} <= run.keys():\n", " return False\n", " messages = run[\"messages\"]\n", " return (text_filled(run[\"answer\"]) and type(run[\"calls\"]) is int\n", " and isinstance(messages, list) and all(isinstance(m, dict) for m in messages)\n", " and any(m.get(\"role\") == \"assistant\" and m.get(\"tool_calls\") for m in messages)\n", " and any(m.get(\"role\") == \"tool\" and text_filled(m.get(\"content\")) for m in messages))\n", "\n", "entry = globals().get(\"explanation\", {})\n", "if not isinstance(entry, dict):\n", " entry = {}\n", "selected = {\"categories\": globals().get(\"category_run\"), \"products\": globals().get(\"product_run\")}.get(entry.get(\"run\"))\n", "messages = selected.get(\"messages\", []) if isinstance(selected, dict) else []\n", "if not isinstance(messages, list):\n", " messages = []\n", "observations = [m for m in messages if isinstance(m, dict) and m.get(\"role\") == \"tool\"]\n", "index = entry.get(\"observation_exchange\")\n", "completion = {\n", " \"name and student ID\": text_filled(globals().get(\"STUDENT_NAME\")) and text_filled(globals().get(\"STUDENT_ID\")),\n", " \"loop function defined\": callable(globals().get(\"run_loop\")),\n", " \"category run: answer and saved record fields\": run_format(globals().get(\"category_run\")),\n", " \"product run: answer and saved record fields\": run_format(globals().get(\"product_run\")),\n", " \"selected category: text\": text_filled(globals().get(\"CATEGORY_TO_INSPECT\")),\n", " \"category findings: column names and types\": table_format(globals().get(\"category_findings\"), \"category\"),\n", " \"product findings: column names and types\": table_format(globals().get(\"product_findings\"), \"product\"),\n", " \"explanation: run and a non-final exchange number\": type(index) is int and 1 <= index < len(observations),\n", " \"explanation: all text fields filled\": all(text_filled(entry.get(k)) for k in\n", " (\"observation\", \"next_decision\", \"category_choice\", \"limitation\", \"correction\")),\n", "}\n", "print(\"submitted by:\", globals().get(\"STUDENT_NAME\") or \"?\", globals().get(\"STUDENT_ID\") or \"?\", \"\\n\")\n", "for label, ok in completion.items():\n", " print(\"PASS \" if ok else \"FAIL \", label)\n", "print(\"\\nHOMEWORK COMPLETE — format only; download and submit\" if all(completion.values()) else \"\\nNOT COMPLETE YET\")" ] } ], "metadata": { "colab": { "name": "W4_hw_sales_investigation.ipynb", "provenance": [] }, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.11" } }, "nbformat": 4, "nbformat_minor": 5 }