{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Aggregate related records\n",
        "\n",
        "Summarize related records with invoice, customer and product totals, counts and averages; filter products by average selling price.\n",
        "\n",
        "[Watch lesson 5](https://asqueel.org/learn/05-related-aggregates/)\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": "l5-c0-c11a5d9ce421"
    },
    {
      "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": "l5-c1-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": "l5-c2-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l5-c3-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l5-c4-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": "l5-c5-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": "l5-c6-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"
      ],
      "id": "l5-c7-b8423f49ceed",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l5-c8-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Welcome back! Here's what we'll learn in this lesson. Summarize related records. We will build invoice, customer and product totals, ask for counts and averages, then filter products by average selling price. We reuse the row formula from lesson four."
      ],
      "id": "l5-c9-c339d9c5c43a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "Related records \u2192 invoice, customer and product totals\n",
        "@relation.aggr(...) \u2192 sum, count and average\n",
        "Average price \u2192 filter a range\n",
        "\n",
        "Same SQLite database in memory\n",
        "```"
      ],
      "id": "l5-c10-813ac69a2a0a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Give every invoice its total"
      ],
      "id": "l5-c11-604ec8feae55"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's give each invoice a calculated total. Declare the calculation once in the model. Select its rows and add their calculated amounts. THIS id is the current invoice's key."
      ],
      "id": "l5-c12-4250d9018bdb"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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\")"
      ],
      "id": "l5-c13-504e222c579c",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's read those totals as ordinary fields. The query only asks for number and total. The calculation stays in the model."
      ],
      "id": "l5-c14-f049a790e8de"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Read those totals as ordinary fields ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()"
      ],
      "id": "l5-c15-54224b52bba5",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Give every customer their total"
      ],
      "id": "l5-c16-16673f8be7ad"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's total the invoices of each customer. Reuse the invoice total we just declared. THIS id now belongs to the customer."
      ],
      "id": "l5-c17-5d20cb23e5f3"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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\")"
      ],
      "id": "l5-c18-a9f15dd98515",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's read each customer's sales total. Ada has two invoices. Together they make one hundred fifty-four ten."
      ],
      "id": "l5-c19-3d142eaee549"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Read each customer's sales total ########\n",
        "\n",
        "customers.query(\n",
        "    columns=\"$name, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()"
      ],
      "id": "l5-c20-560e4ffed39a",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Give every product its sales total"
      ],
      "id": "l5-c21-de84aa419fc2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's total the sales of each product. Add the amounts of invoice rows carrying its code. This is sales revenue, not the quantity in stock."
      ],
      "id": "l5-c22-06e81f483416"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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\")"
      ],
      "id": "l5-c23-6699ce27a352",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's read the product totals. Again, the query uses a named field. Products with no sales have a null total."
      ],
      "id": "l5-c24-4796afcb59ac"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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()"
      ],
      "id": "l5-c25-37ce4a5e0205",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5 \u2014 Sum, count and average the related invoices"
      ],
      "id": "l5-c26-2fa10dfef13f"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now let's ask for several aggregates at once. A customer reaches its invoices through the inverse name declared in lesson two. Each customer still produces one result row."
      ],
      "id": "l5-c27-c79a90b89e65"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "customer \u2192 @invoices\n",
        "invoice  \u2192 @rows\n",
        "product  \u2192 @rows\n",
        "\n",
        "sum($total) \u2192 sales amount\n",
        "count()    \u2192 number of invoices\n",
        "avg($total) \u2192 average invoice amount\n",
        "```"
      ],
      "id": "l5-c28-2671ad9ac8fe"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's summarize each customer's invoices. Aggr adds their totals, counts the invoices, and averages their amounts. Asqueel names the returned columns automatically."
      ],
      "id": "l5-c29-6af280183656"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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()"
      ],
      "id": "l5-c30-b9d55eb436ed",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's count the rows of each invoice too. The same aggregation syntax follows rows. Count returns the number of invoice rows, not the sum of their quantities."
      ],
      "id": "l5-c31-d0049179d51d"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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()"
      ],
      "id": "l5-c32-c70902d55273",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 6 \u2014 Average the selling prices of each product"
      ],
      "id": "l5-c33-208a9e37165d"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's average the recorded selling prices. Follow a product's invoice rows and average their unit prices. This is one price per row, without weighting by quantity."
      ],
      "id": "l5-c34-6c35d948a007"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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()"
      ],
      "id": "l5-c35-3e3736f92998",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 7 \u2014 Filter products by their average selling price"
      ],
      "id": "l5-c36-a0424a37db65"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's name the average price so we can filter it. Define the average in the model. In this release, aggr returns columns; a named formula also works in where and order by."
      ],
      "id": "l5-c37-2fcd73458a1c"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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\")"
      ],
      "id": "l5-c38-a464798f2773",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's filter by average price. Choose three twenty to four eighty, passing both bounds as parameters. Between includes the endpoints. Sort by average price, and read the six matching products."
      ],
      "id": "l5-c39-814e49d9dd66"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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()"
      ],
      "id": "l5-c40-cf0c1fd16a3a",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 8 \u2014 Thank you \u2014 next steps"
      ],
      "id": "l5-c41-0728624c1cad"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Thanks for following along! We summarized related records while keeping one result per parent. Next, group by will combine sales rows into reports. We will use where and having to filter before and after grouping."
      ],
      "id": "l5-c42-86009a543915"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you!\n",
        "Related totals \u2192 invoice, customer and product\n",
        "@relation.aggr(...) \u2192 sum, count and average\n",
        "Named average \u2192 filter and sort\n",
        "\n",
        "Next: GROUP BY and HAVING\n",
        "```"
      ],
      "id": "l5-c43-1324e14d1718"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l5-c44-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from decimal import Decimal\n",
        "from math import isclose\n",
        "assert rows.query().count() == 36\n",
        "expected_invoices = {101: {'total': 34.9, 'count': 3, 'average': 11.633333333333333}, 102: {'total': 48.7, 'count': 3, 'average': 16.233333333333334}, 103: {'total': 25.3, 'count': 3, 'average': 8.433333333333334}, 104: {'total': 26.2, 'count': 3, 'average': 8.733333333333333}, 105: {'total': 103.2, 'count': 3, 'average': 34.4}, 106: {'total': 87.4, 'count': 3, 'average': 29.133333333333333}, 107: {'total': 96.3, 'count': 3, 'average': 32.1}, 108: {'total': 57.8, 'count': 3, 'average': 19.266666666666666}, 109: {'total': 119.2, 'count': 3, 'average': 39.733333333333334}, 110: {'total': 141.0, 'count': 3, 'average': 47.0}, 111: {'total': 43.35, 'count': 3, 'average': 14.45}, 112: {'total': 31.9, 'count': 3, 'average': 10.633333333333333}}\n",
        "expected_customers = {1: {'total': 154.1, 'count': 2, 'average': 77.05}, 2: {'total': 189.7, 'count': 2, 'average': 94.85}, 3: {'total': 57.2, 'count': 2, 'average': 28.6}, 4: {'total': 26.2, 'count': 1, 'average': 26.2}, 5: {'total': 146.55, 'count': 2, 'average': 73.275}, 6: {'total': 87.4, 'count': 1, 'average': 87.4}, 7: {'total': 96.3, 'count': 1, 'average': 96.3}, 8: {'total': 57.8, 'count': 1, 'average': 57.8}}\n",
        "expected_products = {'BTEA': {'total': 19.2, 'count': 2, 'average': 9.6}, 'HTEA': {'total': 16.4, 'count': 2, 'average': 8.2}, 'HON': {'total': 45.0, 'count': 2, 'average': 22.5}, 'SUG': {'total': 6.9, 'count': 1, 'average': 6.9}, 'CHO': {'total': 15.6, 'count': 1, 'average': 15.6}, 'BIS': {'total': 2.8, 'count': 1, 'average': 2.8}, 'JAM': {'total': 18.4, 'count': 1, 'average': 18.4}, 'OAT': {'total': 2.4, 'count': 1, 'average': 2.4}, 'JUI': {'total': 5.4, 'count': 1, 'average': 5.4}, 'WAT': {'total': 0.9, 'count': 1, 'average': 0.9}, 'LEM': {'total': 3.6, 'count': 1, 'average': 3.6}, 'KET': {'total': 98.7, 'count': 1, 'average': 98.7}, 'POT': {'total': 37.0, 'count': 1, 'average': 37.0}, 'PLT': {'total': 19.2, 'count': 1, 'average': 19.2}, 'BWL': {'total': 31.2, 'count': 1, 'average': 31.2}, 'SPO': {'total': 28.5, 'count': 1, 'average': 28.5}, 'TRY': {'total': 59.6, 'count': 1, 'average': 59.6}, 'JAR': {'total': 8.2, 'count': 1, 'average': 8.2}, 'NAP': {'total': 48.0, 'count': 1, 'average': 48.0}, 'TWL': {'total': 5.6, 'count': 1, 'average': 5.6}, 'FLT': {'total': 4.2, 'count': 1, 'average': 4.2}, 'TIN': {'total': 6.7, 'count': 1, 'average': 6.7}, 'MAT': {'total': 22.8, 'count': 1, 'average': 22.8}, 'CAF': {'total': 89.7, 'count': 1, 'average': 89.7}, 'BOT': {'total': 27.0, 'count': 1, 'average': 27.0}, 'STR': {'total': 14.4, 'count': 1, 'average': 14.4}, 'SCL': {'total': 99.6, 'count': 1, 'average': 99.6}, 'TEA': {'total': 17.5, 'count': 2, 'average': 8.75}, 'COF': {'total': 34.5, 'count': 2, 'average': 17.25}, 'MUG': {'total': 26.25, 'count': 2, 'average': 13.125}}\n",
        "expected_prices = {'BTEA': {'total': 6.4, 'count': 2, 'average': 3.2}, 'HTEA': {'total': 8.2, 'count': 2, 'average': 4.1}, 'HON': {'total': 15.0, 'count': 2, 'average': 7.5}, 'SUG': {'total': 2.3, 'count': 1, 'average': 2.3}, 'CHO': {'total': 3.9, 'count': 1, 'average': 3.9}, 'BIS': {'total': 2.8, 'count': 1, 'average': 2.8}, 'JAM': {'total': 4.6, 'count': 1, 'average': 4.6}, 'OAT': {'total': 2.4, 'count': 1, 'average': 2.4}, 'JUI': {'total': 2.7, 'count': 1, 'average': 2.7}, 'WAT': {'total': 0.9, 'count': 1, 'average': 0.9}, 'LEM': {'total': 1.8, 'count': 1, 'average': 1.8}, 'KET': {'total': 32.9, 'count': 1, 'average': 32.9}, 'POT': {'total': 18.5, 'count': 1, 'average': 18.5}, 'PLT': {'total': 6.4, 'count': 1, 'average': 6.4}, 'BWL': {'total': 7.8, 'count': 1, 'average': 7.8}, 'SPO': {'total': 9.5, 'count': 1, 'average': 9.5}, 'TRY': {'total': 14.9, 'count': 1, 'average': 14.9}, 'JAR': {'total': 8.2, 'count': 1, 'average': 8.2}, 'NAP': {'total': 12.0, 'count': 1, 'average': 12.0}, 'TWL': {'total': 5.6, 'count': 1, 'average': 5.6}, 'FLT': {'total': 2.1, 'count': 1, 'average': 2.1}, 'TIN': {'total': 6.7, 'count': 1, 'average': 6.7}, 'MAT': {'total': 11.4, 'count': 1, 'average': 11.4}, 'CAF': {'total': 29.9, 'count': 1, 'average': 29.9}, 'BOT': {'total': 13.5, 'count': 1, 'average': 13.5}, 'STR': {'total': 4.8, 'count': 1, 'average': 4.8}, 'SCL': {'total': 24.9, 'count': 1, 'average': 24.9}, 'TEA': {'total': 7.0, 'count': 2, 'average': 3.5}, 'COF': {'total': 13.8, 'count': 2, 'average': 6.9}, 'MUG': {'total': 10.5, 'count': 2, 'average': 5.25}}\n",
        "expected_window = ['BTEA', 'CHO', 'HTEA', 'JAM', 'STR', 'TEA']\n",
        "\n",
        "for table, key, expected in ((\"shop.invoice\", \"id\", expected_invoices), (\"shop.customer\", \"id\", expected_customers), (\"shop.product\", \"code\", expected_products)):\n",
        "    result = db.table(table).query(columns=f\"${key}, $total\").fetch()\n",
        "    assert len(result) == len(expected)\n",
        "    for row in result:\n",
        "        total = expected[row[key]][\"total\"]\n",
        "        assert row[\"total\"] is None if total is None else isclose(float(row[\"total\"]), total)\n",
        "report = db.table(\"shop.customer\").query(columns=\"$id, @invoices.aggr(sum($total), count(), avg($total))\").fetch()\n",
        "assert len(report) == 8\n",
        "for row in report:\n",
        "    expected = expected_customers[row[\"id\"]]\n",
        "    assert row[\"_invoices_count\"] == expected[\"count\"]\n",
        "    assert isclose(float(row[\"_invoices_total_sum\"]), expected[\"total\"])\n",
        "    assert isclose(float(row[\"_invoices_total_avg\"]), expected[\"average\"])\n",
        "report = products.query(columns=\"$code, $average_price, @rows.aggr(count(), avg($unit_price))\").fetch()\n",
        "assert len(report) == 30\n",
        "for row in report:\n",
        "    expected = expected_prices[row[\"code\"]]\n",
        "    assert row[\"_rows_count\"] == expected[\"count\"]\n",
        "    for mean in (row[\"average_price\"], row[\"_rows_unit_price_avg\"]):\n",
        "        assert mean is None if expected[\"average\"] is None else isclose(float(mean), expected[\"average\"])\n",
        "selected = products.query(columns=\"$code\", where=\"$average_price BETWEEN :lo AND :hi\", lo=3.2, hi=4.8).fetch()\n",
        "assert sorted(r[\"code\"] for r in selected) == expected_window\n",
        "db.commit()\n",
        "assert db.migration_plan().empty\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l5-c45-50c9012a50c0",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 5
    }
  }
}