{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Optional: find the best buyer\n",
        "\n",
        "Optional exercise: group purchases to find a product's best buyer, then follow a weak relation from the calculated ID to the customer.\n",
        "\n",
        "[Watch lesson 8](https://asqueel.org/learn/07-best-buyer/)\n",
        "\n",
        "**Run the cells from top to bottom in a fresh Python kernel.** Each notebook includes its own starting state. SQLite runs in memory; restarting the kernel discards the database. To repeat the exercise, restart the kernel and run all cells. Colab execution requires a Google account.\n",
        "\n",
        "\u00a9 2026 Softwell S.r.l. \u00b7 Apache-2.0"
      ],
      "id": "l8-c0-735362adeb79"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "**Optional extension.** You can skip this notebook and continue with [lesson 9: writes and transactions](https://asqueel.org/learn/06-aggregations/). No later lesson requires the best-buyer formula."
      ],
      "id": "l8-c1-988eafa976b7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Before the examples: `_ = ...` is optional\n",
        "\n",
        "It assigns a return value we do not need, preventing its automatic display in the Python console or notebook. The call still runs normally. This is a Python convention, not an Asqueel requirement. When you need the result, use a meaningful variable name."
      ],
      "id": "l8-c2-5261ce0eb0bf"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Prepare the notebook\n",
        "\n",
        "The video starts in a terminal. Here, install the same official package in the notebook kernel. No virtual environment or separate Python console is needed in Colab."
      ],
      "id": "l8-c3-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l8-c4-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l8-c5-7a5eb4f63b61",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Recreate the starting point\n",
        "\n",
        "The previous lessons are rebuilt below. The four fictional CSV files are included in this notebook and written into a new temporary folder. No downloads or existing database are needed."
      ],
      "id": "l8-c6-26bce07fb451"
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Prepare the sample CSV files\n",
        "from pathlib import Path\n",
        "import os\n",
        "import tempfile\n",
        "_lesson_folder = tempfile.TemporaryDirectory(prefix=\"asqueel-notebook-\")\n",
        "os.chdir(_lesson_folder.name)\n",
        "_csv_files = {'customers.csv': 'id,name,city\\n1,Ada,Florence\\n2,Grace,London\\n3,Linus,Helsinki\\n4,Guido,Amsterdam\\n5,Elena,Florence\\n6,Alan,Manchester\\n7,Margaret,New York\\n8,Barbara,Boston\\n', 'invoice_rows.csv': 'id,invoice_id,product_code,quantity,unit_price,total\\n1,101,TEA,2,3.50,7.00\\n2,101,COF,1,6.90,6.90\\n3,101,MUG,4,5.25,21.00\\n4,102,BTEA,2,3.20,6.40\\n5,102,HTEA,3,4.10,12.30\\n6,102,HON,4,7.50,30.00\\n7,103,SUG,3,2.30,6.90\\n8,103,CHO,4,3.90,15.60\\n9,103,BIS,1,2.80,2.80\\n10,104,JAM,4,4.60,18.40\\n11,104,OAT,1,2.40,2.40\\n12,104,JUI,2,2.70,5.40\\n13,105,WAT,1,0.90,0.90\\n14,105,LEM,2,1.80,3.60\\n15,105,KET,3,32.90,98.70\\n16,106,POT,2,18.50,37.00\\n17,106,PLT,3,6.40,19.20\\n18,106,BWL,4,7.80,31.20\\n19,107,SPO,3,9.50,28.50\\n20,107,TRY,4,14.90,59.60\\n21,107,JAR,1,8.20,8.20\\n22,108,NAP,4,12.00,48.00\\n23,108,TWL,1,5.60,5.60\\n24,108,FLT,2,2.10,4.20\\n25,109,TIN,1,6.70,6.70\\n26,109,MAT,2,11.40,22.80\\n27,109,CAF,3,29.90,89.70\\n28,110,BOT,2,13.50,27.00\\n29,110,STR,3,4.80,14.40\\n30,110,SCL,4,24.90,99.60\\n31,111,TEA,3,3.50,10.50\\n32,111,COF,4,6.90,27.60\\n33,111,MUG,1,5.25,5.25\\n34,112,BTEA,4,3.20,12.80\\n35,112,HTEA,1,4.10,4.10\\n36,112,HON,2,7.50,15.00\\n', 'invoices.csv': 'id,number,customer_id,invoice_date\\n101,INV-001,1,2026-09-28\\n102,INV-002,2,2026-09-29\\n103,INV-003,3,2026-09-30\\n104,INV-004,4,2026-10-01\\n105,INV-005,5,2026-10-02\\n106,INV-006,6,2026-10-03\\n107,INV-007,7,2026-10-04\\n108,INV-008,8,2026-10-05\\n109,INV-009,1,2026-10-06\\n110,INV-010,2,2026-10-07\\n111,INV-011,5,2026-10-08\\n112,INV-012,3,2026-10-09\\n', 'products.csv': 'code,name,category,stock\\nBTEA,Black tea,Drinks,30\\nHTEA,Herbal tea,Drinks,18\\nHON,Honey,Food,22\\nSUG,Cane sugar,Food,40\\nCHO,Dark chocolate,Food,35\\nBIS,Biscuits,Food,28\\nJAM,Strawberry jam,Food,16\\nOAT,Oat drink,Drinks,32\\nJUI,Apple juice,Drinks,26\\nWAT,Mineral water,Drinks,60\\nLEM,Lemonade,Drinks,30\\nKET,Kettle,Kitchen,9\\nPOT,Teapot,Kitchen,14\\nPLT,Plate,Kitchen,36\\nBWL,Bowl,Kitchen,24\\nSPO,Spoon set,Kitchen,20\\nTRY,Serving tray,Kitchen,11\\nJAR,Storage jar,Kitchen,25\\nNAP,Cotton napkins,Kitchen,18\\nTWL,Tea towel,Kitchen,30\\nFLT,Coffee filters,Kitchen,45\\nTIN,Tea tin,Kitchen,15\\nMAT,Table mats,Kitchen,16\\nCAF,Coffee maker,Kitchen,7\\nBOT,Water bottle,Kitchen,21\\nSTR,Tea strainer,Kitchen,23\\nSCL,Kitchen scale,Kitchen,8\\n'}\n",
        "for _name, _text in _csv_files.items():\n",
        "    _ = Path(_name).write_text(_text, encoding=\"utf-8\")\n"
      ],
      "id": "l8-c7-89efb228754e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Recreate the previous lessons\n",
        "\n",
        "######## Import our two tools ########\n",
        "\n",
        "from asqueel import AsqueelDb, SqlDatabaseConfig\n",
        "\n",
        "\n",
        "######## Choose SQLite in memory ########\n",
        "\n",
        "model = SqlDatabaseConfig()\n",
        "root = model.source.db(implementation=\"sqlite\")\n",
        "_ = root.connection(name=\":memory:\", implementation=\"sqlite\")\n",
        "\n",
        "\n",
        "######## Describe a product table ########\n",
        "\n",
        "shop = root.packages().package(\"shop\", sqlschema=\"shop\")\n",
        "product = shop.table(\"product\", pkey=\"code\")\n",
        "_ = product.column(\"code\", size=\":10\")\n",
        "_ = product.column(\"name\", size=\":60\")\n",
        "\n",
        "\n",
        "######## Create that table ########\n",
        "\n",
        "db = AsqueelDb(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Add our first product ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.insert({\"code\": \"TEA\", \"name\": \"Tea\"})\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read our product ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "\n",
        "\n",
        "######## Change its name ########\n",
        "\n",
        "products.update({\"name\": \"Green tea\"}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check the update ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Track category and stock ########\n",
        "\n",
        "_ = product.column(\"category\", size=\":30\")\n",
        "_ = product.column(\"stock\", dtype=\"I\")\n",
        "\n",
        "\n",
        "######## Load the updated model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "\n",
        "\n",
        "######## Add those database columns ########\n",
        "\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Check our original row ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.query(columns=\"$name, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Give tea its stock ########\n",
        "\n",
        "products.update({\"category\": \"Drinks\", \"stock\": 8}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read the new values ########\n",
        "\n",
        "products.query(columns=\"$name, $category, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Add products from CSV ########\n",
        "\n",
        "from io import StringIO\n",
        "data = StringIO(\n",
        "    \"code,name,category,stock\\n\"\n",
        "    \"COF,Coffee,Drinks,24\\n\"\n",
        "    \"MUG,Mug,Kitchen,12\\n\"\n",
        ")\n",
        "\n",
        "\n",
        "######## Import those rows ########\n",
        "\n",
        "products.sql_insert(data, format=\"csv\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## List just the drinks ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$name, $stock\",\n",
        "    where=\"$category = :category\", category=\"Drinks\",\n",
        "    order_by=\"$stock DESC\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson two.\n",
        "\n",
        "######## Check our starting point ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Describe our customers ########\n",
        "\n",
        "customer = shop.table(\"customer\", pkey=\"id\")\n",
        "_ = customer.column(\"id\", dtype=\"I\")\n",
        "_ = customer.column(\"name\", size=\":60\")\n",
        "_ = customer.column(\"city\", size=\":60\")\n",
        "\n",
        "\n",
        "######## Create the customer table ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Describe a dated invoice ########\n",
        "\n",
        "invoice = shop.table(\"invoice\", pkey=\"id\")\n",
        "_ = invoice.column(\"id\", dtype=\"I\")\n",
        "_ = invoice.column(\"number\", size=\":20\")\n",
        "_ = invoice.column(\"invoice_date\", dtype=\"D\")\n",
        "\n",
        "\n",
        "######## Connect invoices to customers ########\n",
        "\n",
        "customer_id = invoice.column(\"customer_id\", dtype=\"I\")\n",
        "_ = customer_id.relation(\n",
        "    \"shop.customer.id\", foreign_key=True, relation_name=\"invoices\",\n",
        ")\n",
        "\n",
        "\n",
        "######## Describe an invoice row ########\n",
        "\n",
        "invoice_row = shop.table(\"invoice_row\", pkey=\"id\")\n",
        "_ = invoice_row.column(\"id\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"quantity\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"unit_price\", dtype=\"N\", size=\"12,2\")\n",
        "_ = invoice_row.column(\"total\", dtype=\"N\", size=\"12,2\")\n",
        "\n",
        "\n",
        "######## Connect the row at both ends ########\n",
        "\n",
        "invoice_id = invoice_row.column(\"invoice_id\", dtype=\"I\")\n",
        "_ = invoice_id.relation(\n",
        "    \"shop.invoice.id\", foreign_key=True, relation_name=\"rows\",\n",
        ")\n",
        "product_code = invoice_row.column(\"product_code\", size=\":10\")\n",
        "_ = product_code.relation(\n",
        "    \"shop.product.code\", foreign_key=True, relation_name=\"rows\",\n",
        ")\n",
        "\n",
        "\n",
        "######## Apply the new model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Get fresh table handles ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "customers = db.table(\"shop.customer\")\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Inspect the row file ########\n",
        "\n",
        "from pathlib import Path\n",
        "print(\"\\n\".join(Path(\"invoice_rows.csv\").read_text().splitlines()[:4]))\n",
        "\n",
        "\n",
        "######## Expand the product catalogue ########\n",
        "\n",
        "with open(\"products.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    products.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Count the catalogue ########\n",
        "\n",
        "products.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import customers and their cities ########\n",
        "\n",
        "with open(\"customers.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    customers.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "customers.query(columns=\"$name, $city\", limit=3).fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import the invoices ########\n",
        "\n",
        "with open(\"invoices.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    invoices.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Import the invoice rows ########\n",
        "\n",
        "with open(\"invoice_rows.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    rows.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check how much we imported ########\n",
        "\n",
        "invoices.query().count()\n",
        "rows.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read invoices with their customers ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $invoice_date, \"\n",
        "            \"@customer_id.name, @customer_id.city\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Inspect the first invoice ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, \"\n",
        "            \"$quantity, $unit_price, $total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read across both connections ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.number, \"\n",
        "            \"@invoice_id.@customer_id.city, \"\n",
        "            \"@product_code.name, $total\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson three.\n",
        "\n",
        "######## Find invoices for Florence ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number\",\n",
        "    where=\"@customer_id.city = :city\", city=\"Florence\",\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Sort invoices by city ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, @customer_id.city\",\n",
        "    order_by=\"@customer_id.city\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Show the newest rows first ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.invoice_date, $total\",\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Find rows for Florence ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $quantity\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city\",\n",
        "    city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Combine the conditions ########\n",
        "\n",
        "from datetime import date\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $total\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city \"\n",
        "          \"AND @invoice_id.invoice_date >= :since\",\n",
        "    city=\"Florence\", since=date(2026, 10, 1),\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Close this read transaction ########\n",
        "\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 4.\n",
        "\n",
        "######## Call this path city ########\n",
        "\n",
        "_ = invoice_row.aliasColumn(\n",
        "    \"city\", relation_path=\"@invoice_id.@customer_id.city\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read and filter with that name ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$city, @product_code.name\",\n",
        "    where=\"$city = :city\", city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Calculate quantity times price ########\n",
        "\n",
        "_ = invoice_row.formulaColumn(\n",
        "    \"calculated_total\",\n",
        "    sql_formula=\"ROUND($quantity * $unit_price, 2)\",\n",
        "    dtype=\"N\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the calculated amounts ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$quantity, $unit_price, $calculated_total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        "    order_by=\"$id\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check whether the database needs changing ########\n",
        "\n",
        "db.migration_plan().empty\n",
        "\n",
        "\n",
        "######## Temporarily change the quantity to three ########\n",
        "\n",
        "rows.update({\"quantity\": 3}, pkey=1)\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=1,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Undo that change ########\n",
        "\n",
        "db.rollback()\n",
        "\n",
        "# Continue with lesson 5.\n",
        "\n",
        "######## Give each invoice a calculated total ########\n",
        "\n",
        "_ = invoice.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$invoice_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "\n",
        "\n",
        "######## Read those totals as ordinary fields ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the invoices of each customer ########\n",
        "\n",
        "_ = customer.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice\",\n",
        "        columns=\"ROUND(SUM($total), 2)\",\n",
        "        where=\"$customer_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "customers = db.table(\"shop.customer\")\n",
        "\n",
        "\n",
        "######## Read each customer's sales total ########\n",
        "\n",
        "customers.query(\n",
        "    columns=\"$name, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the sales of each product ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the product totals ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $total\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Summarize each customer's invoices ########\n",
        "\n",
        "customers = db.table(\"shop.customer\")\n",
        "customers.query(\n",
        "    columns=\"$name, @invoices.aggr(\"\n",
        "            \"sum($total), count(), avg($total))\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Count the rows of each invoice too ########\n",
        "\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "invoices.query(\n",
        "    columns=\"$number, $total, @rows.aggr(count())\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Average the recorded selling prices ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, @rows.aggr(count(), avg($unit_price))\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Name the average price so we can filter it ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"average_price\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\", columns=\"AVG($unit_price)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Filter by average price ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $average_price\",\n",
        "    where=\"$average_price BETWEEN :min_price AND :max_price\",\n",
        "    min_price=3.2, max_price=4.8,\n",
        "    order_by=\"$average_price, $code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 6.\n",
        "\n",
        "######## Total sales by city ########\n",
        "\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "products = db.table(\"shop.product\")\n",
        "rows.query(\n",
        "    columns=\"$city, SUM($calculated_total)\",\n",
        "    group_by=\"$city\", order_by=\"$city\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Name the report columns ########\n",
        "\n",
        "report = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales, \"\n",
        "            \"COUNT(*) AS row_count\",\n",
        "    group_by=\"$city\", order_by=\"$city\",\n",
        ")\n",
        "report.fetch()\n",
        "\n",
        "\n",
        "######## Filter rows and groups ########\n",
        "\n",
        "selected = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales\",\n",
        "    where=\"$unit_price >= :min_price\", min_price=5,\n",
        "    group_by=\"$city\",\n",
        "    having=\"SUM($calculated_total) >= :min_sales\", min_sales=100,\n",
        "    order_by=\"$city\",\n",
        ")\n",
        "selected.fetch()\n",
        "db.commit()\n"
      ],
      "id": "l8-c8-7d4b62695246",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l8-c9-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Welcome to this optional exercise. We will reuse group by to find the customer who spent the most on each product. Then we will turn that calculated ID into a relationship. You can skip this exercise and continue with writes and transactions; no later lesson needs this formula."
      ],
      "id": "l8-c10-929ed03a8beb"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "Optional: the best buyer of a product\n",
        "Group purchases \u2192 choose the largest total\n",
        "Calculated customer ID \u2192 name and city\n",
        "\n",
        "Prerequisite: GROUP BY from lesson 6\n",
        "Skip to lesson 9: writes and transactions\n",
        "```"
      ],
      "id": "l8-c11-62766c11d976"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Choose a buyer by total spending"
      ],
      "id": "l8-c12-d0e9adc0257a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Why do we group before sorting? Imagine Ada buys this product twice, spending six each time, while Grace spends ten once. The largest single purchase belongs to Grace. But Ada's combined twelve makes her the best buyer. Group by combines her purchases before order by and limit choose a winner. We use the grouping from lesson six to choose the winning customer."
      ],
      "id": "l8-c13-e24e5313cc31"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Best buyer = most spent across all purchases\n",
        "\n",
        "Illustrative example:\n",
        "Ada:     6 + 6 = 12\n",
        "Grace:      10 = 10\n",
        "\n",
        "ORDER BY one row + LIMIT 1 \u2192 Grace (10)\n",
        "GROUP BY buyer + SUM + ORDER BY + LIMIT 1 \u2192 Ada (12)\n",
        "\n",
        "Group first, then sort and keep one customer\n",
        "```"
      ],
      "id": "l8-c14-f882e92e8dc5"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Calculate each product's best buyer"
      ],
      "id": "l8-c15-b2aa3e06ae13"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's find each product's best buyer. Best means the customer who spent the most on this product across all their invoices. Group its rows by customer, sum each customer's spending, then sort and keep one ID. Sorting individual rows would find the largest single purchase instead. If totals are equal, the lower customer ID wins."
      ],
      "id": "l8-c16-3b6ea204fcd4"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Find each product's best buyer ########\n",
        "\n",
        "best_customer = product.formulaColumn(\n",
        "    \"best_customer_id\", dtype=\"I\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"@invoice_id.customer_id\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "        group_by=\"@invoice_id.customer_id\",\n",
        "        order_by=\"SUM($calculated_total) DESC, \"\n",
        "                 \"@invoice_id.customer_id\",\n",
        "        limit=1,\n",
        "    ),\n",
        ")"
      ],
      "id": "l8-c17-25f25f5de198",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Follow a relation from the calculated ID"
      ],
      "id": "l8-c18-c20cbfe3880d"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's connect that ID to the customer. A weak relation starts from the calculated column. It creates no stored foreign key. Reload the model and refresh the table handles."
      ],
      "id": "l8-c19-69e42df2945a"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Connect that ID to the customer ########\n",
        "\n",
        "_ = best_customer.weak_relation(\"shop.customer.id\")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")"
      ],
      "id": "l8-c20-5fb0c7a480e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's read the buyer's name and city. The path starts from our formula, just like a stored ID. Linus is the best buyer of biscuits and black tea; Grace is the best buyer of bottles. With no sales, the ID and customer fields are null. True confirms that no migration is needed."
      ],
      "id": "l8-c21-7fd41a35c938"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Read the buyer's name and city ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $best_customer_id, \"\n",
        "            \"@best_customer_id.name, @best_customer_id.city\",\n",
        "    order_by=\"$code\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "db.migration_plan().empty"
      ],
      "id": "l8-c22-8e536c156182",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5 \u2014 Thank you \u2014 next steps"
      ],
      "id": "l8-c23-7ae52d8c3197"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Thanks for following along! We combined purchases, selected one winning customer, and followed a weak relation from a calculated ID. This optional extension adds no stored columns. Continue with writes and transactions; that lesson also works without this exercise."
      ],
      "id": "l8-c24-743387f296d4"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you!\n",
        "GROUP BY buyer \u2192 combined spending\n",
        "ORDER BY + LIMIT 1 \u2192 winning customer ID\n",
        "weak_relation \u2192 customer name and city\n",
        "\n",
        "Next: writes and transactions\n",
        "```"
      ],
      "id": "l8-c25-9ae087051b17"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l8-c26-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from decimal import Decimal\n",
        "from math import isclose\n",
        "assert rows.query().count() == 36\n",
        "expected_buyers = {'BTEA': 3, 'HTEA': 2, 'HON': 2, 'SUG': 3, 'CHO': 3, 'BIS': 3, 'JAM': 4, 'OAT': 4, 'JUI': 4, 'WAT': 5, 'LEM': 5, 'KET': 5, 'POT': 6, 'PLT': 6, 'BWL': 6, 'SPO': 7, 'TRY': 7, 'JAR': 7, 'NAP': 8, 'TWL': 8, 'FLT': 8, 'TIN': 1, 'MAT': 1, 'CAF': 1, 'BOT': 2, 'STR': 2, 'SCL': 2, 'TEA': 5, 'COF': 5, 'MUG': 1}\n",
        "expected_customer_details = {1: {'id': '1', 'name': 'Ada', 'city': 'Florence'}, 2: {'id': '2', 'name': 'Grace', 'city': 'London'}, 3: {'id': '3', 'name': 'Linus', 'city': 'Helsinki'}, 4: {'id': '4', 'name': 'Guido', 'city': 'Amsterdam'}, 5: {'id': '5', 'name': 'Elena', 'city': 'Florence'}, 6: {'id': '6', 'name': 'Alan', 'city': 'Manchester'}, 7: {'id': '7', 'name': 'Margaret', 'city': 'New York'}, 8: {'id': '8', 'name': 'Barbara', 'city': 'Boston'}}\n",
        "\n",
        "result = products.query(columns=\"$code, $best_customer_id, @best_customer_id.name, @best_customer_id.city\").fetch()\n",
        "assert len(result) == 30\n",
        "for row in result:\n",
        "    buyer = expected_buyers[row[\"code\"]]\n",
        "    assert row[\"best_customer_id\"] == buyer\n",
        "    assert row[\"_best_customer_id_name\"] == expected_customer_details[buyer][\"name\"]\n",
        "    assert row[\"_best_customer_id_city\"] == expected_customer_details[buyer][\"city\"]\n",
        "db.commit()\n",
        "products.insert({\"code\": \"CHECK\", \"name\": \"Unsold test product\"})\n",
        "empty = products.query(columns=\"$best_customer_id, @best_customer_id.name, @best_customer_id.city\", where=\"$code = :code\", code=\"CHECK\").fetch()\n",
        "assert len(empty) == 1 and all(value is None for value in empty[0].values())\n",
        "db.rollback()\n",
        "assert products.query().count() == 30\n",
        "db.commit()\n",
        "assert db.migration_plan().empty\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l8-c27-ce953e00b593",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 8
    }
  }
}