{
 "nbformat": 4,
 "nbformat_minor": 5,
 "metadata": {
  "kernelspec": {
   "name": "provsql-studio",
   "display_name": "ProvSQL (SQL)",
   "language": "sql"
  },
  "language_info": {
   "name": "sql"
  },
  "provsql": {
   "scheme": "semiring",
   "database": "cs6",
   "generated_from": "doc/source/user/casestudy6.rst"
  }
 },
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Case Study: City Air-Quality Sensor Network\n",
    "\n",
    "This case study applies ProvSQL’s continuous distributions (see [the chapter on continuous distributions](https://provsql.org/docs/user/continuous-distributions.html)) to an air-quality sensor network, through ProvSQL Studio (see [the Studio chapter](https://provsql.org/docs/user/studio.html)), whose plots of densities, histograms and circuits suit random variables better than text output.\n",
    "\n",
    "## The Scenario\n",
    "\n",
    "A municipal observatory runs a small network of sensors reporting a $\\mathit{PM}_{2.5}$ concentration (fine particulate matter, in micrograms per cubic metre). Each kind of sensor has its own noise model: a normal distribution for high-end units, uniform for low-cost ones, exponential for a drift-prone unit, Erlang for a multi-pass one; a reference station reports exact values. Readings fall into three categories, *Good*, *Moderate* and *Unhealthy* (a simplified form of the US EPA breakpoints), each station is in calibration with some probability, and yesterday’s readings are kept in a table of the same shape.\n",
    "\n",
    "Your tasks:\n",
    "\n",
    "- compute the probability that a reading exceeds a threshold, and its expectation beyond it;\n",
    "- model calibration as a mixture, aggregate readings per district, and watch the simplifier fold the result;\n",
    "- combine batches with `UNION`, and compare probability methods;\n",
    "- find the worst reading per district, percentiles, and expected exceedances;\n",
    "- couple two sensors through a shared plume, and measure the coupling;\n",
    "- compute fleet statistics when sensors drop out;\n",
    "- read calibration scores as graded confidence.\n",
    "\n",
    "## Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*The following cells set up the database with all the content this notebook requires; run them first, ideally on a fresh database.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "DROP TABLE IF EXISTS readings CASCADE;\n",
    "DROP TABLE IF EXISTS historical_readings CASCADE;\n",
    "DROP TABLE IF EXISTS stations CASCADE;\n",
    "DROP TABLE IF EXISTS calibration_status CASCADE;\n",
    "DROP TABLE IF EXISTS categories CASCADE;"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "-- Four monitoring stations across two districts.\n",
    "DROP TABLE IF EXISTS stations CASCADE;\n",
    "CREATE TABLE stations (\n",
    "  id        TEXT PRIMARY KEY,\n",
    "  name      TEXT NOT NULL,\n",
    "  district  TEXT NOT NULL\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "TRUNCATE stations;\n",
    "INSERT INTO stations VALUES\n",
    "  ('s1', 'City Centre',         'centre'),\n",
    "  ('s2', 'Riverside Park',      'centre'),\n",
    "  ('s3', 'Industrial Estate',   'east'),\n",
    "  ('s4', 'Suburban Reference',  'east');"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT add_provenance('stations');"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "-- Per-station calibration probability (Bernoulli).  The high-end and\n",
    "-- multi-pass units are well calibrated; the low-cost and drift-prone\n",
    "-- ones less so; the reference station is always in-spec.\n",
    "DROP TABLE IF EXISTS calibration_status CASCADE;\n",
    "CREATE TABLE calibration_status (\n",
    "  station_id  TEXT PRIMARY KEY REFERENCES stations(id),\n",
    "  p           DOUBLE PRECISION NOT NULL\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "TRUNCATE calibration_status;\n",
    "INSERT INTO calibration_status VALUES\n",
    "  ('s1', 0.95),\n",
    "  ('s2', 0.70),\n",
    "  ('s3', 0.60),\n",
    "  ('s4', 1.00);"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT add_provenance('calibration_status');"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "-- Regulatory categories (deterministic, no provenance).\n",
    "DROP TABLE IF EXISTS categories CASCADE;\n",
    "CREATE TABLE categories (\n",
    "  name  TEXT PRIMARY KEY,\n",
    "  lo    DOUBLE PRECISION NOT NULL,\n",
    "  hi    DOUBLE PRECISION NOT NULL\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "TRUNCATE categories;\n",
    "INSERT INTO categories VALUES\n",
    "  ('Good',       0.0,    12.0),\n",
    "  ('Moderate',   12.1,   35.0),\n",
    "  ('Unhealthy',  35.1,   1000.0);"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "-- Today's readings.  Eight rows of pm25, one per (station, sample),\n",
    "-- exercising the normal, uniform, exponential, and Erlang noise models\n",
    "-- plus the implicit numeric cast for the reference station.\n",
    "DROP TABLE IF EXISTS readings CASCADE;\n",
    "CREATE TABLE readings (\n",
    "  id          INTEGER PRIMARY KEY,\n",
    "  station_id  TEXT NOT NULL REFERENCES stations(id),\n",
    "  ts          TIMESTAMP NOT NULL,\n",
    "  pm25        random_variable  -- NULL: sensor offline, no reading\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "TRUNCATE readings;\n",
    "INSERT INTO readings (id, station_id, ts, pm25) VALUES\n",
    "  (1, 's1', '2026-05-12 08:00', normal(28.0, 2.0)),       -- high-end Gaussian\n",
    "  (2, 's2', '2026-05-12 08:00', uniform(10.0, 22.0)),     -- low-cost uniform window\n",
    "  (3, 's3', '2026-05-12 08:00', exponential(0.04)),       -- drift-prone, mean = 25\n",
    "  (4, 's4', '2026-05-12 08:00', 15.0),                    -- reference (implicit cast)\n",
    "  (5, 's1', '2026-05-12 09:00', normal(40.0, 4.0)),       -- high-end, into Unhealthy\n",
    "  (6, 's2', '2026-05-12 09:00', uniform(12.0, 24.0)),     -- low-cost, into Moderate\n",
    "  (7, 's3', '2026-05-12 09:00', erlang(3, 0.1)),          -- multi-pass Erlang, mean = 30\n",
    "  (8, 's4', '2026-05-12 09:00', 16.5);                    -- reference"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT add_provenance('readings');"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "-- Yesterday's batch, used for the UNION ALL step.  Same shape, slightly\n",
    "-- different distributions (a heatwave bumped the means).\n",
    "DROP TABLE IF EXISTS historical_readings CASCADE;\n",
    "CREATE TABLE historical_readings (\n",
    "  id          INTEGER PRIMARY KEY,\n",
    "  station_id  TEXT NOT NULL REFERENCES stations(id),\n",
    "  ts          TIMESTAMP NOT NULL,\n",
    "  pm25        random_variable NOT NULL\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "TRUNCATE historical_readings;\n",
    "INSERT INTO historical_readings (id, station_id, ts, pm25) VALUES\n",
    "  (1, 's1', '2026-05-11 08:00', normal(34.0, 2.5)),\n",
    "  (2, 's2', '2026-05-11 08:00', uniform(15.0, 28.0)),\n",
    "  (3, 's3', '2026-05-11 08:00', exponential(0.03)),\n",
    "  (4, 's4', '2026-05-11 08:00', 18.0),\n",
    "  (5, 's1', '2026-05-11 09:00', normal(42.0, 3.0)),\n",
    "  (6, 's2', '2026-05-11 09:00', uniform(20.0, 35.0)),\n",
    "  (7, 's3', '2026-05-11 09:00', erlang(3, 0.08)),\n",
    "  (8, 's4', '2026-05-11 09:00', 19.5);"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT add_provenance('historical_readings');"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "-- A provenance mapping so the Studio eval-strip's sr_formula and\n",
    "-- PROV-XML export can label leaves with station names rather than\n",
    "-- raw UUIDs.\n",
    "DROP TABLE IF EXISTS station_mapping CASCADE;\n",
    "CREATE TABLE station_mapping AS\n",
    "  SELECT s.name AS value, r.provsql AS provenance\n",
    "  FROM readings r JOIN stations s ON s.id = r.station_id;\n",
    "SELECT remove_provenance('station_mapping');\n",
    "CREATE INDEX ON station_mapping(provenance);"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The script creates the schema below and seeds the random-variable readings via the constructors documented in [the chapter on continuous distributions](https://provsql.org/docs/user/continuous-distributions.html). It is five data tables plus a provenance mapping:\n",
    "\n",
    "- `stations(id, name, district)` – four monitoring stations across two districts, provenance-tracked.\n",
    "- `readings(station_id, ts, pm25 random_variable)` – one `pm25` reading per station per timestamp; the `random_variable` carries the per-station noise model (normal, uniform, exponential, erlang, or a deterministic lifted from the reference station).\n",
    "- `calibration_status(station_id, p)` – Bernoulli probability that each station is in calibration on the day of interest.\n",
    "- `categories(name, lo, hi)` – three regulatory categories (*Good* / *Moderate* / *Unhealthy*) keyed by their interval bounds.\n",
    "- `historical_readings(...)` – same shape as `readings`, populated from yesterday’s batch.\n",
    "- `station_mapping(value, provenance)` – a provenance mapping from each reading’s token to its station’s name, so that [`sr_formula`](https://provsql.org/doxygen-sql/html/group__compiled__semirings.html#gae49f63c94b0094be6756f2f42d4caecd) and PROV-XML export show station names instead of raw UUIDs.\n",
    "\n",
    "The schema panel lists the fixture’s six relations: the four provenance-tracked tables (`stations`, `calibration_status`, `readings`, `historical_readings`) carry the purple `prov` pill, `categories` is plain, and `station_mapping` is tagged `mapping`. The `pm25` column on `readings` and `historical_readings` is flagged with a terracotta `rv` pill: comparison and arithmetic operators on this column build provenance circuits, so a condition like `pm25 > 35` yields an uncertain event, not a Boolean.\n",
    "\n",
    "## Step 1: Inspect a Noisy Reading\n",
    "\n",
    "In the Studio query box:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT id, ts, pm25\n",
    "FROM readings\n",
    "WHERE station_id = 's1'\n",
    "ORDER BY ts"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result table renders `pm25` as a clickable `random_variable` cell carrying the underlying gate UUID. Click into a row’s `pm25`: Studio renders its circuit, a single `gate_rv` leaf with the distribution-kind initial in the circle (N for a Normal, U for Uniform, Exp for Exponential, Erl for Erlang). Pick `Distribution profile` from the `Distribution` group of the eval strip and click `Run`: the panel returns headline stats: support, mean $\\mu$, standard deviation $\\sigma$, and entropy $H$, as well as an inline histogram with a PDF/CDF toggle.\n",
    "\n",
    "The histogram is backed server-side by [`rv_histogram`](https://provsql.org/doxygen-sql/html/group__circuit__introspection.html#ga4e287ff30a597e203f43c80d12098c89); pinning `provsql.monte_carlo_seed` in the Config panel (under `Evaluation`) makes the shape reproducible across re-runs.\n",
    "\n",
    "## Step 2: A First Probabilistic Threshold\n",
    "\n",
    "The $\\mathit{PM}_{2.5}$ *Unhealthy* category begins above 35.1. Find the rows whose reading might cross it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT id, station_id, ts\n",
    "FROM readings\n",
    "WHERE pm25 > 35"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because `pm25` is a random variable, the comparison is not a yes/no test: it stands for the *event* “this reading exceeds 35”, and ProvSQL attaches that event to each row’s provenance.\n",
    "\n",
    "Click into a result row’s auto-added `provsql` cell. Circuit mode shows the Boolean wrapper (a `gate_times` over the row’s input token and the `gate_cmp`); the cmp’s child link reaches into the `gate_rv` from Step 1.\n",
    "\n",
    "The eval strip’s `Marginal probability` entry exposes the full method catalogue (see [the chapter on probabilities](https://provsql.org/docs/user/probabilities.html)). Pick `monte-carlo` and set `n = 10000`; the panel returns the probability with a Hoeffding confidence band. Pin `provsql.monte_carlo_seed = 42` in the Config panel and re-run: the result is now identical across runs. Set the seed back to `-1` and re-run to see the band shift between runs.\n",
    "\n",
    "## Step 3: The Simplifier in Action\n",
    "\n",
    "Each comparison is recorded as a `gate_cmp`. A *simplifier* then folds comparators whose answer can be decided from the operands’ support alone: for example, `U(10, 22) > 35` is universally false because the uniform’s upper bound is below the threshold. The fold is controlled by `provsql.simplify_on_load` (default on), which the Config panel exposes under `Evaluation`.\n",
    "\n",
    "Click into row 2’s auto-added `provsql` cell from the Step 2 result (station `s2`, `pm25 ~ U(10, 22)`). With `provsql.simplify_on_load` on, the canvas shows a single `𝟘` (zero) gate: the simplifier resolved the comparator to a constant-false leaf and dropped the whole subtree. Toggle the GUC off in the Config panel and click the cell again: the canvas now shows the raw construction shape, a `gate_times` (`⊗`) over the row’s input token `ι` and a `gate_cmp` (`>`) whose children are the `U(10, 22)` leaf and the constant `35`. Both views are semantically identical.\n",
    "\n",
    "## Step 4: Calibration via Mixtures\n",
    "\n",
    "Each station has a probability of being mis-calibrated; a mis-calibrated unit over-reports by 20% (the *reading* it records is `1.2` times the true value). The corrected estimate of the true reading is therefore `pm25` with probability `p` (the station is in spec) and `pm25 / 1.2` with probability `1 - p` (the report needs to be scaled back). Express this as a Bernoulli mixture:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT r.id, r.station_id,\n",
    "       mixture(cs.p, r.pm25, r.pm25 / 1.2) AS pm25_calibrated\n",
    "FROM readings r JOIN calibration_status cs USING (station_id)\n",
    "WHERE r.station_id = 's1'"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Click into a result row’s `pm25_calibrated` cell. Circuit mode renders the `gate_mixture` as a `Mix` node with three labelled outgoing edges (`p` / `x` / `y`) matching the SQL constructor’s argument order: `p` points to the Bernoulli mixing probability, `x` to the in-spec arm, and `y` to the correction arm.\n",
    "\n",
    "The same node-inspector panel exposes `Distribution profile` on the mixture root. Because station `s1` is in spec 95% of the time, the histogram is dominated by the `N(28, 2)` arm and the out-of-spec `N(23.33, 1.667)` contributes only a small left shoulder rather than a visually distinct second mode; the panel headline reflects this with a mixture mean slightly below 28. To see clear bimodality, re-run the query with a larger calibration error, e.g., replace `r.pm25 / 1.2` with `r.pm25 / 2.0` so the out-of-spec arm folds to `N(14, 1)`, well separated from the in-spec `N(28, 2)`; the two peaks then show up distinctly on the histogram even at the 95%/5% weighting.\n",
    "\n",
    "## Step 5: Aggregation Over Random Variables\n",
    "\n",
    "Compute average $\\mathit{PM}_{2.5}$ per district:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT s.district,\n",
    "       avg(r.pm25)  AS avg_pm25,\n",
    "       sum(r.pm25)  AS total_pm25\n",
    "FROM readings r JOIN stations s ON s.id = r.station_id\n",
    "GROUP BY s.district"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Click into a row’s `avg_pm25` cell. Circuit mode shows the [`avg`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga3e227bce63085af57849b6f021d3992e) circuit: a `gate_arith(DIV, num, denom)` over two `gate_arith(PLUS, …)` subtrees, each child a per-row `gate_mixture`. The right child of the outer division is the count of *included* rows under their per-row provenance: rows whose provenance is false contribute the additive identity to both numerator and denominator. Run `Distribution profile` on the root: the panel shows the per-district average as a tight distribution centred at the inclusion-weighted mean.\n",
    "\n",
    "## Step 6: Conditional Inference\n",
    "\n",
    "Re-open the filtered query from Step 2:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT id, station_id, ts, pm25\n",
    "FROM readings\n",
    "WHERE pm25 > 35\n",
    "  AND station_id = 's1'"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Click a result row’s `pm25` cell and pick `Distribution profile` in the eval strip. The strip then shows a `Condition on` text input, preset to the row’s provenance UUID, with the `Conditioned by:` badge underneath it active. Run: the histogram now shows the *truncated* shape, restricted to the tail above `35`. Pick `Moment` with `k = 1` and `raw`: the panel returns the closed-form Mills-ratio mean of the [truncated normal](https://en.wikipedia.org/wiki/Truncated_normal_distribution), exactly $\\mu + \\sigma \\cdot \\frac{\\phi(\\alpha)}{1 - \\Phi(\\alpha)}$ with $\\alpha = (35 - \\mu)/\\sigma$. Click the active badge to clear the conditioning; the panel reverts to the unconditional mean $\\mu$. Click the muted badge to restore the row provenance.\n",
    "\n",
    "The closed-form truncation table covers every family implementing truncated moments – Normal (Mills ratio), Uniform (intersected support), Exponential (memorylessness on a lower bound or finite-interval truncation), Log-normal, Weibull, Pareto (tail self-similarity), and Beta. For other shapes, the conditional moment is estimated by rejection sampling at budget `provsql.rv_mc_samples`.\n",
    "\n",
    "## Step 7: Diagnostic Sampling\n",
    "\n",
    "For raw inspection or downstream analytics, draw samples from the conditional distribution. With the same row pinned and the `Conditioned by:` badge active, pick `Sample` from the `Distribution` group; set `n = 200` and run. The result panel shows a six-value inline preview with a “show full list” expander; clicking it dumps all 200 samples.\n",
    "\n",
    "Both rows of the query are bare Normals conditioned on an interval, so all 200 samples come straight from the truncated distribution; for other shapes, [`rv_sample`](https://provsql.org/doxygen-sql/html/group__circuit__introspection.html#gaec7d70d0f94f8225861e3377682ce348) falls back to rejection sampling and may return fewer (see `continuous-distributions`).\n",
    "\n",
    "## Step 8: Combining Batches via UNION\n",
    "\n",
    "Both batches share the same id space (rows `1` through `8`, one per `(station, timestamp)` slot), so a `UNION` (without `ALL`) over `(station_id, id)` deduplicates a slot to a single result row whose provenance combines today’s reading and yesterday’s reading via the semiring addition. With `WHERE pm25 > 35` lifted on each branch, each contributing row carries a `gate_cmp(pm25 > 35)` of its own:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "(SELECT station_id, id FROM readings            WHERE pm25 > 35)\n",
    "UNION\n",
    "(SELECT station_id, id FROM historical_readings WHERE pm25 > 35)\n",
    "ORDER BY station_id, id"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pick a result row’s `provsql` cell: Circuit mode shows a `gate_plus` (`⊕`) over the two contributing inputs (today’s row and yesterday’s row), each carrying its own `gate_cmp(pm25 > 35)` from the lifted `WHERE`. Run `Marginal probability` from the eval strip: it gives the probability that *at least one* of the two days produced an Unhealthy reading for that slot. We deliberately keep the `random_variable` `pm25` column out of the `SELECT`: there is no duplicate-elimination semantics for `random_variable`, so a `UNION` over an RV column would have no well-defined meaning.\n",
    "\n",
    "## Step 9: Filtering Grouped Random Variables by Expected Value\n",
    "\n",
    "Filter the per-district aggregates from Step 5 by their expected average. Because [`avg`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga3e227bce63085af57849b6f021d3992e) over a `random_variable` column returns a `random_variable` (not an `agg_token`), and [`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e) collapses it to a plain `double`, the `HAVING` condition is an ordinary comparison that PostgreSQL evaluates directly; each surviving group’s provenance still gets a `delta(gate_agg)` wrapper:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT s.district, avg(r.pm25) AS avg_pm25\n",
    "FROM readings r JOIN stations s ON s.id = r.station_id\n",
    "GROUP BY s.district\n",
    "HAVING expected(avg(r.pm25)) > 20"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e) collapses the inner [`avg`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga3e227bce63085af57849b6f021d3992e) (the division circuit of Step 5) to its mean. It does so exactly here: no probability has been written on these rows’ tokens, so every row is certainly present, the count in the denominator is a constant, and the mean of the average is the average of the means. `> 20` then compares two `double` values, so a group survives exactly when its expected average exceeds the threshold. For the case-study fixture both districts pass (centre at 25.5, east at 21.625); clicking either result row’s `provsql` cell shows the `delta(gate_agg)` shape, identical to the no-HAVING aggregate from Step 5 but filtered to the surviving groups.\n",
    "\n",
    "## Step 10: Independent vs Monte Carlo\n",
    "\n",
    "For threshold queries whose contributing rows have structurally independent provenance, the `'independent'` probability method (see [the chapter on probabilities](https://provsql.org/docs/user/probabilities.html)) is *exact* and far cheaper than Monte Carlo. Re-run the Step 2 query, click a result row’s `provsql` cell, and run `Marginal probability` from the eval strip three times, with the methods `independent`, `tree-decomposition`, and `monte-carlo` at `n = 10000`: the two exact methods return the same value to full precision, while `monte-carlo` returns a Hoeffding-bounded estimate that tightens as `n` grows. From SQL, the same methods are arguments of [`probability_evaluate`](https://provsql.org/doxygen-sql/html/group__probability.html#gabffa40fef0e37a75d39d4c39c4a1ec0f).\n",
    "\n",
    "## Step 11: The Worst Reading in a District\n",
    "\n",
    "Which district had the worst air this morning? The question asks for the distribution of the *maximum* over each district’s readings – an order statistic, not a sum. The `max` aggregate over a `random_variable` column builds exactly that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT s.district, max(r.pm25) AS worst,\n",
    "       expected(max(r.pm25)) AS worst_mean\n",
    "FROM readings r JOIN stations s ON s.id = r.station_id\n",
    "GROUP BY s.district\n",
    "ORDER BY s.district"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The *centre* district’s worst reading averages ≈ 40.0 – its `N(40, 4)` reading dominates the maximum – and *east* comes out at ≈ 39.6 (a Monte Carlo estimate, see below, so it varies by a few tenths between runs and with the seed), driven by the heavy right tails of `Exp(0.04)` and `erlang(3, 0.1)` even though both means sit near 25–30. Note how the extremum tells a different story from the averages of Step 5 (≈ 25.5 and ≈ 21.6): the east district looks fine on average and just as alarming at the extreme. `worst_mean` itself is a plain number – `expected` has already collapsed the distribution to its mean – which is why the query also projects the unreduced aggregate: the `worst` cell is a clickable `random_variable` token whose circuit is a `gate_arith` `MAX` root over the per-row mixtures; a row absent in a world contributes `-∞`, the order-statistic identity, so it never perturbs the maximum. Since every row is certainly present, [`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e) reads the maximum of the readings themselves: over continuous distributions, as in the centre district, it is computed exactly by a one-dimensional integral; the east district’s constant reference readings fall outside that formula, and its expectation is estimated by Monte Carlo.\n",
    "\n",
    "The same-row form is `greatest` / `least`: the SQL keywords accept `random_variable` arguments, which ProvSQL rewrites into order statistics (Step 15 uses `least` outside any provenance-tracked relation):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT greatest(a.pm25, b.pm25) AS worse,\n",
    "       expected(greatest(a.pm25, b.pm25)) AS worse_of_two\n",
    "FROM readings a, readings b\n",
    "WHERE a.id = 1 AND b.id = 2"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clicking the `worse` cell shows the same `gate_arith` `MAX` root, but here the children *are* independent bare leaves (`N(28, 2)` and `U(10, 22)`), so the mean is computed analytically by the layer-cake integral – ≈ 28.00003, barely above the normal’s mean, since the uniform almost never wins.\n",
    "\n",
    "## Step 12: Percentiles and Exceedance Margins\n",
    "\n",
    "[`quantile`](https://provsql.org/doxygen-sql/html/group__probability.html#ga82ca9fd9531cf491bcefde6850de8321) reads the inverse CDF off any reading – the natural regulatory question is “what level is only exceeded 5% of the time?”:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT id, station_id, pm25, quantile(pm25, 0.95) AS p95\n",
    "FROM readings\n",
    "WHERE id IN (1, 3, 5, 7)\n",
    "ORDER BY id"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each family answers through its own inverse CDF: the normals give `μ + 1.645·σ` (≈ 31.29 for `N(28, 2)`, ≈ 46.58 for `N(40, 4)`), the exponential gives `−ln(0.05)/λ ≈ 74.89`, and the Erlang – which has no elementary inverse – is bisected on its closed-form CDF to ≈ 62.96. No sampling is involved in any of these. The same readout is available interactively: with a `pm25` cell pinned, pick `Quantile` from the `Distribution` group of the eval strip, set the fraction `p`, and run.\n",
    "\n",
    "Quantiles compose with conditioning: the median of a reading *given* that it crossed the Unhealthy line is the `p = 0.5` quantile of the truncated distribution, again in closed form:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT id, pm25, quantile(pm25, 0.5, provenance()) AS median_given_high\n",
    "FROM readings\n",
    "WHERE pm25 > 35 AND station_id = 's1'\n",
    "ORDER BY id"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Row 1 (`N(28, 2)`, for which the event is a far tail) gives ≈ 35.36, just above the threshold; row 5 (`N(40, 4)`) gives ≈ 40.53. In the eval strip, the same conditioning rides on the `Conditioned by:` badge from Step 6: with it active, `Quantile` returns the truncated-distribution quantile.\n",
    "\n",
    "## Step 13: Expected Excess via CASE\n",
    "\n",
    "*How far* above the threshold does a station land, on average? The excess `max(pm25 − 35, 0)` is a piecewise transform, written as a searched `CASE` (see [the continuous distributions chapter](https://provsql.org/docs/user/continuous-distributions.html)):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT id,\n",
    "       expected(CASE WHEN pm25 > 35 THEN pm25 - 35\n",
    "                     ELSE as_random(0) END) AS expected_excess\n",
    "FROM readings\n",
    "WHERE station_id = 's1'\n",
    "ORDER BY id"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The guard and the branches read the same `pm25`, and a piecewise function of one random variable is among the `CASE` shapes whose moments come back in closed form: the expectation integrates each branch over its guard’s region, so the correlation between “the reading exceeded 35” and “by how much” is preserved exactly, with no sampling – the answers hold even under `SET provsql.rv_mc_samples = 0`. Row 1 returns ≈ 0.0001 – `N(28, 2)` essentially never crosses – while row 5 returns the closed-form partial expectation $\\sigma\\\\,\\phi(\\alpha) + (\\mu - 35)\\\\,\\Phi(-\\alpha)$ with $\\alpha = (35 - \\mu)/\\sigma$, ≈ 5.2023 for `N(40, 4)`, to full precision. Note the explicit `as_random(0)` (or equivalently `0::random_variable`) on the `ELSE` branch: `CASE` type resolution needs the branches in a single type category, so a bare numeric literal will not lift on its own.\n",
    "\n",
    "## Step 14: Correlated Sensors\n",
    "\n",
    "The two centre-district stations sit a few hundred metres apart; on a windy day both are shifted by the same dust plume. Model the plume once and add it to both readings – the shared leaf is what makes them correlated – then measure it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "WITH plume AS (SELECT normal(0, 3) AS dust)\n",
    "SELECT covariance(a.pm25 + p.dust, b.pm25 + p.dust)  AS cov_shared,\n",
    "       correlation(a.pm25 + p.dust, b.pm25 + p.dust) AS corr_shared,\n",
    "       covariance(a.pm25, b.pm25)                    AS cov_indep,\n",
    "       stddev(a.pm25)                                AS sd_a\n",
    "FROM readings a, readings b, plume p\n",
    "WHERE a.id = 1 AND b.id = 2"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The two raw readings are structurally independent – their base-RV footprints are disjoint – so `cov_indep` is *exactly* `0` (no sampling), and `sd_a` is exactly `2`. The plume-shifted pair shares the `dust` leaf: in theory `Cov = Var(dust) = 9` and `ρ = 9 / √((4+9)·(12+9)) ≈ 0.545`; the readouts land close (≈ 9.06 and ≈ 0.544 at seed 42). A shared-leaf cross-moment has no closed form, so both statistics are estimated by Monte Carlo from coupled draws, the plume being drawn once per iteration and shared by both readings, which keeps the estimates tight around the theoretical values.\n",
    "\n",
    "## Step 15: Drift, Lifetime, and Spikes: More Families\n",
    "\n",
    "The vendor spec sheets bring in three more families, all available as constructors (see [the chapter on continuous distributions](https://provsql.org/docs/user/continuous-distributions.html)). The multi-pass unit’s zero drift accumulates as a Gamma; its per-batch drift check is a chi-squared test statistic:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT expected(gamma(2.5, 0.5))       AS drift_mean,\n",
    "       variance(gamma(2.5, 0.5))       AS drift_var,\n",
    "       quantile(chi_squared(4), 0.95)  AS chi2_crit"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`gamma(k = 2.5, λ = 0.5)` has mean `k/λ = 5` and variance `k/λ² = 10`, both exact; the χ²₄ critical value comes out ≈ 9.4877, bisected on the regularised-incomplete-gamma CDF.\n",
    "\n",
    "Sensor lifetimes are Weibull. A station carrying two redundant units fails over to the second when the first dies; the time to *first* failure is the minimum of the two lifetimes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT expected(least(weibull(1.5, 4.0),\n",
    "                      weibull(1.5, 4.0))) AS first_failure,\n",
    "       expected(weibull(1.5, 4.0))        AS single_lifetime"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A single `weibull(k = 1.5, λ = 4)` unit lasts `λ·Γ(1 + 1/k) ≈ 3.61` years on average; the minimum of two i.i.d. Weibulls is again Weibull (min-stability), so the first failure at `≈ 2.27` years is computed in closed form.\n",
    "\n",
    "Construction-season episodic spikes are heavy-tailed – a Pareto over a 30 μg/m³ floor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT expected(pareto(30, 2.5))       AS spike_mean,\n",
    "       quantile(pareto(30, 2.5), 0.95) AS spike_p95,\n",
    "       expected(pareto(30, 1.0))       AS undefined_mean"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `α = 2.5` spike averages `α·xₘ/(α−1) = 50` with a 95th percentile near 99.4 – the heavy tail at work. The last column shows moment honesty: `pareto(30, 1)` has *no* finite mean, and ProvSQL reports `Infinity` rather than estimating a divergent integral.\n",
    "\n",
    "Finally, does a spike beat the noisy high reading? A Pareto vs Normal comparison has no registered closed form, so ProvSQL integrates the pair by quadrature – exact-grade, no sampling:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT probability(x > y) AS p_spike_beats_normal\n",
    "FROM (SELECT pareto(30, 2.5) AS x,\n",
    "             normal(45, 5)   AS y) t"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "which returns ≈ 0.384: even though the spike’s *mean* (50) is above the normal’s (45), the spike spends most of its mass near its 30 floor and only wins through its tail.\n",
    "\n",
    "## Step 16: Fleet Statistics Under Sensor Dropout\n",
    "\n",
    "Every reading so far contributed unconditionally. Today’s maintenance log says otherwise: a station out of calibration silently drops out of the feed. Pin each reading’s provenance probability to its station’s calibration probability, so a row is *present* only when its station is in calibration:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT set_prob(provenance(),\n",
    "                (SELECT p FROM calibration_status cs\n",
    "                  WHERE cs.station_id = r.station_id))\n",
    "FROM readings r"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The calibration probability is fetched through a scalar subquery so that `provenance()` stays the `readings` row’s own input token: joining the two tracked tables instead would make it the *joint* row provenance (a `times` gate), which `set_prob` cannot pin. ProvSQL warns that the subquery’s data is read outside provenance tracking (“treated as certain”) – exactly the intent here: `p` parameterises the presence model, it is not uncertain data itself.\n",
    "\n",
    "The SQL-standard statistic aggregates ([`stddev_pop`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga48fa5423c7cb473cafc71563fafce37e) / [`stddev_samp`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#gad15d1502387e11fbf7f4e508c89772bb), [`covar_pop`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga4feb1f67e46285e9f30df43fd4ef6e80) / [`covar_samp`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#gac29ea8812e647c91cbc957d7b3f04665) / [`corr`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#gac75785665e3f63091097c200a9ef7b3f), and the ordered-set [`percentile_cont`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga42ddf88269d1d4b03c6a0b4be81f7292)) are lifted to `random_variable` rows with exactly these semantics: a row absent in a world drops out of every sum, the count, and the percentile’s member set. Start where the answer is checkable by hand – the reference station is always in calibration (`p = 1`) and its readings are lab-grade constants `15.0` and `16.5`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT expected(stddev_pop(pm25))     AS sd_ref,\n",
    "       expected(corr(pm25, pm25 * 2)) AS corr_ref\n",
    "FROM readings\n",
    "WHERE station_id = 's4'"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both statistics are Dirac – every Monte-Carlo draw is the same world and the same constants – so the answers are exact: `sd_ref = 0.75` (the population stddev of `{15, 16.5}`) and `corr_ref = 1` (a reading is perfectly correlated with its own rescaling, drawn once and observed by both argument positions).\n",
    "\n",
    "Now the probabilistic fleet: the *median* reading per district, where dropout changes the member set itself – when Riverside Park is out of calibration (30% of days), both of its rows leave the group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT s.district,\n",
    "       expected(percentile_cont(0.5)\n",
    "                WITHIN GROUP (ORDER BY r.pm25)) AS median_pm25\n",
    "FROM readings r JOIN stations s ON s.id = r.station_id\n",
    "GROUP BY s.district"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In each Monte-Carlo draw, only the values whose station is in calibration are kept, sorted, and interpolated (pin the result node and look for the `p50` circle in the canvas). Under dropout the centre district’s expected median lands around 26 – pulled between the two Normal City-Centre readings and the two lower Riverside uniforms that are only present 70% of the time.\n",
    "\n",
    "## Step 17: A Maintenance-Triage Headline via CASE over Aggregates\n",
    "\n",
    "Which district needs the maintenance crew first? The desk wants a single *headline* number per district: if even the weakest station’s calibration confidence clears 0.8, report the district’s total confidence weight; otherwise flag the weakest station (the crew’s target). That is a `CASE` whose guards **and** branches are aggregates (see [the aggregation chapter](https://provsql.org/docs/user/aggregation.html)). Its expectation is exact for `sum` / `count` / `min` / `max` guards and branches; an `avg` branch inside a `CASE` is exact only for an unconditional expectation and is evaluated by Monte Carlo otherwise. First make the maintenance log itself uncertain (each record is confirmed with a probability) and the station registry certain:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT set_prob(provenance(), 1.0) FROM stations;\n",
    "SELECT set_prob(provenance(),\n",
    "                CASE station_id WHEN 's1' THEN 1.0\n",
    "                                WHEN 's2' THEN 0.8\n",
    "                                WHEN 's3' THEN 0.7\n",
    "                                ELSE 1.0 END)\n",
    "FROM calibration_status;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One record per district is kept certain, so each district’s group exists in every world (an all-absent group would produce no headline row at all)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT s.district,\n",
    "       CASE WHEN min(cs.p) > 0.8 THEN sum(cs.p)\n",
    "            ELSE min(cs.p) END AS confidence_headline\n",
    "FROM calibration_status cs JOIN stations s ON s.id = cs.station_id\n",
    "GROUP BY s.district"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result cells display as `0.7 (*)` and `0.6 (*)` – the `CASE` evaluated on the actual data (with every record confirmed, both districts have a weak link below the bar, so each headlines its worst station), with the `(*)` marking a probabilistic aggregate whose value is world-dependent. The headline’s distribution is evaluated *exactly*, with no Monte Carlo, even under `SET provsql.rv_mc_samples = 0`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT s.district,\n",
    "       expected(CASE WHEN min(cs.p) > 0.8 THEN sum(cs.p)\n",
    "                     ELSE min(cs.p) END) AS expected_headline\n",
    "FROM calibration_status cs JOIN stations s ON s.id = cs.station_id\n",
    "GROUP BY s.district"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the centre district there are exactly two worlds: with both records confirmed (probability `0.8`) the weakest link `0.70` misses the bar and is the headline; with only City Centre’s record confirmed (`0.2`) the sole confidence `0.95` clears it and the total `0.95` is reported. So $E = 0.8 \\cdot 0.70 + 0.2 \\cdot 0.95 = 0.75$ exactly – and that is the value the query returns. The east district works out to $0.7 \\cdot 0.60 + 0.3 \\cdot 1.00 = 0.72$.\n",
    "\n",
    "## Step 18: Shared Information in Nats\n",
    "\n",
    "Step 14 measured the plume coupling with [`correlation`](https://provsql.org/doxygen-sql/html/group__probability.html#gab9108e85a4dbb83b43f606c0abcfa2e8); [`mutual_information`](https://provsql.org/doxygen-sql/html/group__probability.html#gac3a2e56e6753e67639fb9f43580924f4) is its information-theoretic counterpart – symmetric, in nats, and zero *exactly* when the two variables share no randomness:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "WITH plume AS (SELECT normal(0, 3) AS dust)\n",
    "SELECT mutual_information(a.pm25, b.pm25)  AS mi_indep,\n",
    "       mutual_information(a.pm25 + p.dust,\n",
    "                          b.pm25 + p.dust) AS mi_shared\n",
    "FROM readings a, readings b, plume p\n",
    "WHERE a.id = 1 AND b.id = 2"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`mi_indep` is **exactly 0** with no sampling at all: the two raw readings share no random leaf. The plume-shifted pair shares the `dust` leaf, and the readout estimates their mutual information by a 2-D histogram over *coupled* joint draws (each iteration draws the shared plume once and both sums observe it) – about `0.21` nats at seed 42. For a jointly-Gaussian pair with the Step 14 correlation $\\rho \\approx 0.545$ the closed form would give $-\\tfrac12\\ln(1-\\rho^2) \\approx 0.176$; the estimate lands in that neighbourhood, a little above: the pair is not jointly Gaussian (Riverside’s noise is uniform), and the plug-in histogram estimator carries a small upward bias.\n",
    "\n",
    "## Step 19: An Offline Sensor Reports NULL\n",
    "\n",
    "Sensors go offline. When Riverside Park misses its 10:00 sample, the natural encoding is a row whose `pm25` is **SQL NULL** – there is no reading, not even an uncertain one:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "INSERT INTO readings (id, station_id, ts, pm25)\n",
    "VALUES (9, 's2', '2026-05-12 10:00', NULL);"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Three things follow, each matching plain SQL:\n",
    "\n",
    "- **Threshold events are unknown, never true.** `WHERE pm25 > 35` over the offline row is *unknown* under SQL’s three-valued logic in every possible world, so the lifted comparison annotates the row with the circuit’s zero – [`probability_evaluate`](https://provsql.org/doxygen-sql/html/group__probability.html#gabffa40fef0e37a75d39d4c39c4a1ec0f) returns exactly 0, with no sampling. (A NULL reading is not “certainly exceeding”, and not “possibly exceeding” either: it is no evidence at all.)\n",
    "- **Null tests are ordinary and deterministic.** `WHERE pm25 IS NULL` finds the offline row like any other predicate; PostgreSQL evaluates it and provenance simply carries the row’s token through.\n",
    "- **Aggregates skip the offline row.** `expected(avg(pm25))` over Riverside Park is the same with or without row 9: the NULL reading contributes to neither the sum nor the count.\n",
    "\n",
    "Delete the placeholder row to return to the running dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "DELETE FROM readings WHERE id = 9;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The general rules – which predicates treat NULLs as unknown and what that means for the circuits – are in [the NULL semantics chapter](https://provsql.org/docs/user/nulls.html).\n",
    "\n",
    "## Step 20: Calibration as Graded Confidence\n",
    "\n",
    "The calibration scores of `calibration_status` can also be read as *degrees of confidence*, not probabilities. A district’s alert is corroborated when two of its stations agree; how confident is that corroboration? A fuzzy semiring combines the degrees, with `max` over alternatives and a t-norm for a conjunction. Turn the scores into a provenance mapping, and list the districts with a corroborating pair of stations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "DROP TABLE IF EXISTS calibration_degree;\n",
    "SELECT create_provenance_mapping('calibration_degree',\n",
    "                                 'calibration_status', 'p');\n",
    "\n",
    "SELECT DISTINCT s1.district\n",
    "FROM calibration_status c1\n",
    "JOIN stations s1 ON s1.id = c1.station_id\n",
    "JOIN calibration_status c2 ON c2.station_id > c1.station_id\n",
    "JOIN stations s2 ON s2.id = c2.station_id AND s2.district = s1.district\n",
    "ORDER BY s1.district;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Click the `provsql` cell of the centre’s row, pick `Łukasiewicz (numeric\n",
    "fuzzy)` from the `Numeric` group of the eval strip, select the `calibration_degree` mapping, and run; then do the same with `Viterbi\n",
    "(max-times)`. The Łukasiewicz semiring conjoins with the t-norm $\\max(a + b - 1, 0)$: the centre, corroborated by City Centre (0.95) and Riverside Park (0.70), gets 0.65. The two stations’ shortfalls from full confidence, 0.05 and 0.30, add up. Viterbi multiplies instead, 0.665. With the fully trusted Suburban Reference (1.00), the east district keeps the Industrial Estate’s 0.60 under both: a certain station costs nothing. The stations’ own tokens are not in the mapping and count as fully true. In SQL, the same values are those of [`sr_lukasiewicz`](https://provsql.org/doxygen-sql/html/group__compiled__semirings.html#ga27ff464e570641a7b195ed3578a66ab1) and [`sr_viterbi`](https://provsql.org/doxygen-sql/html/group__compiled__semirings.html#ga67d375630a9674bfecd4f87d0d003d31), with the mapping’s name as second argument.\n",
    "\n",
    "See [the chapter on continuous distributions](https://provsql.org/docs/user/continuous-distributions.html) for the full reference and [the Studio chapter](https://provsql.org/docs/user/studio.html) for the Studio reference."
   ]
  }
 ]
}