{
"nbformat": 4,
"nbformat_minor": 5,
"metadata": {
"kernelspec": {
"name": "provsql-studio",
"display_name": "ProvSQL (SQL)",
"language": "sql"
},
"language_info": {
"name": "sql"
},
"provsql": {
"scheme": "semiring",
"database": "cs8",
"generated_from": "doc/source/user/casestudy8.rst"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Case Study: ProvSQL as a Probability Calculator\n",
"\n",
"This case study uses ProvSQL as an **exact, correlation-aware probability calculator that you drive in SQL**. Classic probability problems – base rates, correlated events, conditional expectation, truncated distributions, denial constraints – become ordinary queries, and the answers are computed *exactly* (not by sampling, unless you ask) and *correlation-aware* (the provenance circuit tracks shared events, so joint and conditional probabilities come out right without independence assumptions or hand-rolled [inclusion–exclusion](https://en.wikipedia.org/wiki/Inclusion%E2%80%93exclusion_principle)).\n",
"\n",
"A recurring thread is the conditioning operator `|`: once a model is loaded, `A | B` reads as “`A` given `B`”, for discrete events, for continuous random variables, and for probabilistic aggregates alike.\n",
"\n",
"## The Scenario\n",
"\n",
"**Warning:**\n",
"\n",
"> The agency, its screening programme, and every figure in this case study (prevalences, test accuracies, biomarker distributions, thresholds, dosing intervals, case counts, durations) are **fictional or simplified**, chosen solely to illustrate classic probability problems with ProvSQL. They convey no medical or epidemiological knowledge and are not health advice.\n",
"\n",
"An epidemiology desk at a public-health agency keeps a small probabilistic model of a screening programme and reaches for ProvSQL whenever a question needs probabilistic evaluation. A series of such questions follows; each is a recognisable textbook problem, and we work through each one step by step, building its model and then asking the calculator."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It is recommended to run this case study in ProvSQL Studio or the ProvSQL Playground, in notebook mode. There is no separate setup script: each problem creates its own small model below, so run the cells from top to bottom. Every model first drops and recreates its tables, so **Run All is safe to repeat**: nothing accumulates."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Problem 1: The Base-Rate Fallacy\n",
"\n",
"Screening tests are imperfect, but a good one is quite accurate. Ours catches 90% of true cases (its [sensitivity](https://en.wikipedia.org/wiki/Sensitivity_and_specificity)) and correctly clears 95% of healthy people (its [specificity](https://en.wikipedia.org/wiki/Sensitivity_and_specificity)); the disease itself is rare, affecting 1% of the population.\n",
"\n",
"A patient tests positive. What is the probability they actually have the disease? The tempting answer – “around 90%, since the test is that accurate” – is wrong, and the mistake is common enough to have a name: the [base-rate fallacy](https://en.wikipedia.org/wiki/Base_rate_fallacy). It forgets how rare the disease is. Among the 99% who are healthy, even a 5% false-positive rate produces a flood of positives, and that flood swamps the genuine cases – so most positive results are in fact false alarms. The quantity we really want, $\\Pr(\\text{disease} \\mid \\text{positive})$, comes out far below 90%. Let us compute it exactly.\n",
"\n",
"The model is the *joint sample space*: the four possible worlds for one screened patient – diseased or healthy, each crossed with a positive or negative test. We build it in small steps. First, keep the test’s three numbers in one place, named once and easy to change:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"DROP TABLE IF EXISTS params CASCADE;\n",
"CREATE TABLE params(prevalence float, sensitivity float, specificity float);\n",
"INSERT INTO params VALUES (0.01, 0.90, 0.95);"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now spell out the four worlds. Each world’s probability is the prevalence of its health state times the test’s rate in that state – the chain rule, written once per world:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"DROP TABLE IF EXISTS screening CASCADE;\n",
"CREATE TABLE screening(disease boolean, positive boolean, p float);\n",
"INSERT INTO screening\n",
"SELECT true, true, prevalence * sensitivity FROM params -- true positive\n",
"UNION ALL\n",
"SELECT true, false, prevalence * (1 - sensitivity) FROM params -- false negative\n",
"UNION ALL\n",
"SELECT false, true, (1 - prevalence) * (1 - specificity) FROM params -- false positive\n",
"UNION ALL\n",
"SELECT false, false, (1 - prevalence) * specificity FROM params; -- true negative"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Have a look: each row carries the probability of its world, and the four sum to 1."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT * FROM screening;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"These four worlds are *mutually exclusive*: for any one patient exactly one of them is the true state of affairs. [`repair_key`](https://provsql.org/doxygen-sql/html/group__table__management.html#gacfbe67438895e5f153b01bb9352cbc54) is how ProvSQL records that – it turns a set of rows into a *block of mutually-exclusive alternatives* (a “repair” of a key), exactly one of which holds at a time. Passing an *empty* key makes the whole table one such block, and [`set_prob`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga17334fe1d3969d969eaa18e32a93b4e0) then gives each world the probability in its `p` column:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT repair_key('screening', '');\n",
"SELECT disease, positive, set_prob(provenance(), p) FROM screening;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The model is loaded. Now let us *see* what a probabilistic event looks like to ProvSQL. Every row a query returns over a provenance-tracked table carries a *provenance token* in a `provsql` column: a handle to the little circuit explaining how that row came to be. Ask for the distinct values of `positive`:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT DISTINCT positive FROM screening;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Two rows come back, `true` and `false`, each with a token. **Click the token on the** `true` **row.** You stay in Notebook mode; Studio just adds a cell below showing that token’s circuit – a single *plus* gate joining the two mutually-exclusive worlds in which the test reads positive (the diseased true positive and the healthy false positive). That little circuit *is* the event “the test is positive”.\n",
"\n",
"On that circuit cell, press `Evaluate` to add an evaluation cell bound to the circuit above. Leave the scheme on `Probability` and press `Run`: ProvSQL walks the circuit and returns about **0.0585** – a positive result is uncommon.\n",
"\n",
"That click-path is one SQL query. [`probability_evaluate`](https://provsql.org/doxygen-sql/html/group__probability.html#gabffa40fef0e37a75d39d4c39c4a1ec0f) takes a token and returns its probability, and [`provenance`](https://provsql.org/doxygen-sql/html/group__provenance__output.html#ga93c314c9412aff767f5f5f994da091c9) hands back the token of a query’s rows – so the same number comes from one cell:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT probability_evaluate(provenance())\n",
"FROM (SELECT DISTINCT positive FROM screening) AS s\n",
"WHERE positive;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The question we actually care about is [conditional](https://en.wikipedia.org/wiki/Conditional_probability): given a positive test, how likely is the disease? ProvSQL writes “`A` given `B`” as `A | B`. We name both events – diseased and positive – and ask for the two conditionals (grouping on the column the `WHERE` fixes collapses each event’s worlds into a single token, just as `DISTINCT` did above):"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH e AS (\n",
" SELECT (SELECT provenance() FROM screening WHERE disease GROUP BY disease) AS d,\n",
" (SELECT provenance() FROM screening WHERE positive GROUP BY positive) AS pos)\n",
"SELECT probability_evaluate(pos | d) AS pos_given_disease,\n",
" probability_evaluate(d | pos) AS disease_given_pos\n",
"FROM e;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The first number is about 0.9: $\\Pr(\\text{positive} \\mid \\text{disease})$ recovers the test’s sensitivity, as it must. The second is the punchline – $\\Pr(\\text{disease} \\mid \\text{positive})$ is only about **0.15**, not 0.9. Most positive results really are false alarms, exactly as the base-rate fallacy warned. ProvSQL computed $\\Pr(A \\wedge B) / \\Pr(B)$ – [Bayes’ rule](https://en.wikipedia.org/wiki/Bayes%27_theorem) – over the shared circuit, with no arithmetic on your part.\n",
"\n",
"## Problem 2: Correlation That Matters\n",
"\n",
"Risk factors rarely act alone. Obesity, for instance, raises the risk of *both* type 2 diabetes and hypertension. Because the two conditions share that common cause, they are [correlated](https://en.wikipedia.org/wiki/Correlation): a patient who has one is more likely to have the other. Suppose we want the probability that a patient develops *at least one* of the two. Treating them as [independent](https://en.wikipedia.org/wiki/Independence_%28probability_theory%29) overestimates it – let us see by how much.\n",
"\n",
"**Note:**\n",
"\n",
"> This model – the factors, the probabilities, and the “obesity plus one extra trigger” rule for each condition – is invented and deliberately oversimplified to illustrate correlation. It is not medical guidance.\n",
"\n",
"Model three independent base factors: obesity (the shared cause) and one extra trigger for each condition, each present with its own probability. [`add_provenance`](https://provsql.org/doxygen-sql/html/group__table__management.html#ga00f0d0b04b2b693c974e72aaf095cb3b) makes the table provenance-tracked (one independent token per row) and [`set_prob`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga17334fe1d3969d969eaa18e32a93b4e0) reads each row’s probability from its `p` column:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"DROP TABLE IF EXISTS risk CASCADE;\n",
"CREATE TABLE risk(factor text, p float);\n",
"INSERT INTO risk VALUES\n",
" ('obesity', 0.5), -- the shared common cause\n",
" ('insulin_resistance', 0.6), -- extra trigger for diabetes\n",
" ('high_salt', 0.7); -- extra trigger for hypertension\n",
"SELECT add_provenance('risk');\n",
"SELECT factor, set_prob(provenance(), p) FROM risk;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A patient develops **diabetes** when they are *both* obese and insulin-resistant. In SQL “both” is a join – ask for the two facts at once – and ProvSQL records the result’s provenance as the *product* of their tokens (a [`provenance_times`](https://provsql.org/doxygen-sql/html/group__semiring__operations.html#ga78437a737b6eb9bb21772ec9fb7fcd1d), formed for you). Its probability:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT probability_evaluate(provenance())\n",
"FROM risk o, risk x\n",
"WHERE o.factor = 'obesity' AND x.factor = 'insulin_resistance';"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"0.3 (that is $0.5 \\cdot 0.6$). **Hypertension** is the same query with `high_salt` in place of `insulin_resistance` – 0.35. Now “diabetes *or* hypertension”: match a patient who is obese and has *either* trigger, and fold the two matches into one event by grouping on `o.factor`, which is `obesity` in both. The provenance of a set of alternatives is their *sum* (a [`provenance_plus`](https://provsql.org/doxygen-sql/html/group__semiring__operations.html#ga4f3a411041bc2908f33a0c3a932650b4)), so this single query *is* the “at least one condition” event. We do not even need to select anything in particular – the provsql token rides along in every result row, so a constant label is enough to read:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT 'diabetes or hypertension' AS event\n",
"FROM risk o, risk x\n",
"WHERE o.factor = 'obesity'\n",
" AND x.factor IN ('insulin_resistance', 'high_salt')\n",
"GROUP BY o.factor;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Click the token to see its circuit.** `obesity` appears as a *single* gate feeding both branches: the shared cause is the same node everywhere, and that overlap is exactly what an independence assumption ignores. Press `Evaluate` and `Run`: about **0.44**.\n",
"\n",
"Compare that with the independence estimate, plain arithmetic $1 - (1 - \\Pr(\\text{diabetes}))(1 - \\Pr(\\text{hypertension}))$:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT 1 - (1 - 0.3) * (1 - 0.35) AS independence_estimate;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**0.545** – a 24% overstatement. ProvSQL’s 0.44 is correct because the disjunction circuit accounted for the shared obesity on its own. You never had to write the inclusion–exclusion correction $\\Pr(A) + \\Pr(B) - \\Pr(A \\wedge B)$.\n",
"\n",
"The same shared gate also lets us turn the question around and *condition* on it. Problem 1 conditioned one event on another with the **binary** `A | B`; the **unary** `| B` (the prefix form of [`given`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga8fa072c874c0567dffa60cc220dc7d5b)) conditions a *whole query’s rows at once* – a directive in the `SELECT` list that is stripped from the output, leaving each row’s provenance conditioned on `B`. Condition every factor on obesity being present:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT factor,\n",
" round(probability_evaluate(provenance())::numeric, 4) AS p_given_obesity\n",
"FROM (\n",
" SELECT factor, | (SELECT provenance() FROM risk WHERE factor = 'obesity')\n",
" FROM risk\n",
") s\n",
"ORDER BY factor;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`obesity` comes back at **1.0** – an event given itself is certain – while `insulin_resistance` (0.6) and `high_salt` (0.7) are **unchanged** from their priors: they share no gate with obesity, so conditioning on it tells us nothing about them. The same shared circuit that made the disjunction correct makes the conditional correct.\n",
"\n",
"See what conditioning built. Select one conditioned tuple on its own and **click its** `provsql` **token**:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT factor, | (SELECT provenance() FROM risk WHERE factor = 'obesity')\n",
"FROM risk WHERE factor = 'insulin_resistance';"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You stay in Notebook mode; Studio adds a circuit cell below showing a `∣` (*conditioned*) gate with three labelled children – the **target** event (`insulin_resistance`), the **evidence** (`obesity`), and their **joint** `target ⊗ evidence`. Its probability is read straight off that gate as $\\Pr(\\text{target} \\wedge \\text{evidence}) / \\Pr(\\text{evidence})$ – Bayes’ rule over the shared circuit, the same ratio Problem 1 used. The `|` operator, in both its binary and unary forms (with the function spellings [`cond`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga1bad4ab2d5313500c908eda06e2e16d3) and [`given`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga8fa072c874c0567dffa60cc220dc7d5b)), is documented in [the conditioning chapter](https://provsql.org/docs/user/conditioning.html).\n",
"\n",
"## Problem 3: The Right Method, Chosen for You\n",
"\n",
"A probability can be computed from a circuit in many ways – some exact (factoring out independent parts, [inclusion–exclusion](https://en.wikipedia.org/wiki/Inclusion%E2%80%93exclusion_principle), [knowledge compilation](https://en.wikipedia.org/wiki/Knowledge_compilation), [tree decomposition](https://en.wikipedia.org/wiki/Tree_decomposition)), some by random sampling. You rarely choose: the evaluation strip (and [`probability_evaluate`](https://provsql.org/doxygen-sql/html/group__probability.html#gabffa40fef0e37a75d39d4c39c4a1ec0f) under it) picks the cheapest method that *fits the circuit* – and “fits” matters, because not every method works on every circuit.\n",
"\n",
"Re-run the “at least one condition” query from Problem 2 and click its token to bring the circuit back up:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT 'diabetes or hypertension' AS event\n",
"FROM risk o, risk x\n",
"WHERE o.factor = 'obesity'\n",
" AND x.factor IN ('insulin_resistance', 'high_salt')\n",
"GROUP BY o.factor;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Press `Evaluate`, leave the method on `exact`, and `Run`: 0.44, and the strip notes *via possible-worlds* – the exact method the chooser judged cheapest here. Now change the method dropdown in the strip and re-run:\n",
"\n",
"- [Monte-Carlo](https://en.wikipedia.org/wiki/Monte_Carlo_method) works on *any* circuit, but only approximately: it samples at random, so its estimate lands near 0.44, not exactly on it. Give it an accuracy target in the strip’s arguments box – `eps=0.1, delta=0.05` – and it reports the guarantee it met: the interval the true probability lies within, at the requested confidence (95% here).\n",
"- `independent` *refuses* – `Not an independent circuit` – because it assumes no input is shared between the parts it multiplies, and here `obesity` feeds both conditions (the shared gate in that circuit).\n",
"\n",
"That refusal is the point. The chooser never offered `independent` for this circuit; `exact` stays correct as your circuits gain structure, and a method that does not fit is refused, never quietly wrong.\n",
"\n",
"## Problem 4: A Continuous Quantity\n",
"\n",
"Every event so far was discrete – true or false. But a lab [biomarker](https://en.wikipedia.org/wiki/Biomarker) is a *continuous* number, and ProvSQL represents one as a `random_variable`: a value that is not a single number but a whole distribution. Conditioning on it works exactly as `|` did for discrete events, and the result is again a distribution, whose mean, variance, and range you can read off with [`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e), [`variance`](https://provsql.org/doxygen-sql/html/group__probability.html#gab1d52fb442dd262c0ac950d349a87ca3), and [`support`](https://provsql.org/doxygen-sql/html/group__probability.html#ga52dbe84c73d98c50a25784afa173183f).\n",
"\n",
"We want exact, closed-form answers, not sampled ones. In the **Config** panel, set `provsql.rv_mc_samples` to 0: that turns off the Monte-Carlo fallback, so ProvSQL either answers a continuous query in closed form or tells you it cannot, instead of silently sampling. The panel applies the setting to every cell you run (outside Studio, the equivalent is `SET provsql.rv_mc_samples = 0`).\n",
"\n",
"Build a biomarker that follows a [Normal distribution](https://en.wikipedia.org/wiki/Normal_distribution) with mean 20 and standard deviation 5, and look at it:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT normal(20, 5) AS biomarker;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"One row comes back with a `random_variable` token. **Click it** to draw its circuit below the query – a single Normal leaf – and press `Evaluate` on the circuit cell. For a random variable, the evaluation cell starts on `Distribution profile`; run it with its button, and Studio draws the bell curve of Normal(20, 5), centred on 20, with its mean μ and standard deviation σ alongside. The other entries of the same menu give a `Moment`, a `Quantile`, or a `Sample` of the variable.\n",
"\n",
"The circuit cell’s `Circuit mode` button opens the same token in Studio’s Circuit mode, for a closer look: expanding the circuit gate by gate, inspecting each gate, and the full evaluation strip. That leaves the notebook; come back to it through the `Notebook` tab of the mode switcher at the top, and it reopens where you left it.\n",
"\n",
"Its [mean](https://en.wikipedia.org/wiki/Expected_value) is no surprise:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT expected(normal(20, 5));"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now suppose a patient is *referred* – their biomarker came back above a threshold of 25. Conditioning the variable on that event, `x | (x > 25)`, chops off everything below 25 and renormalises; the result is itself a `random_variable`. Its mean is no longer 20:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH r AS (SELECT normal(20, 5) AS x)\n",
"SELECT expected(x | (x > 25)) FROM r;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"About **27.6** – a referred patient’s biomarker is expected well above the threshold, not at the population average. The conditioned variable has a spread and a range too:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH r AS (SELECT normal(20, 5) AS x)\n",
"SELECT variance(x | (x > 25)) AS variance,\n",
" (support(x | (x > 25))).lo AS lowest_value\n",
"FROM r;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The [variance](https://en.wikipedia.org/wiki/Variance) has shrunk to about 5 (from 25 for the unconditioned Normal), and the lowest value the conditioned biomarker can take is exactly 25 – [truncation](https://en.wikipedia.org/wiki/Truncated_normal_distribution) moved the floor up to the threshold.\n",
"\n",
"We took those moments one at a time, but the conditioned biomarker is a single object – a `random_variable` you can hand onward, store, or inspect whole. Print it:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH r AS (SELECT normal(20, 5) AS x)\n",
"SELECT (x | (x > 25)) AS referred_biomarker\n",
"FROM r;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Click its token and evaluate it** as before ( `Evaluate`, then ). Its distribution profile is the *truncated* bell curve – everything below 25 cut away, the rest renormalised – with its support, mean, and standard deviation reported alongside: the same conditioned variable you just took moments of, now seen as a whole distribution.\n",
"\n",
"The same `|` also conditions one *event* on another, so a conditional probability reads directly. Among referred patients (biomarker above 25), how many are in fact severe (above 30)? That is $\\Pr(x > 30 \\mid x > 25)$, written by conditioning the two comparison events. From here on we use [`probability`](https://provsql.org/doxygen-sql/html/group__probability.html#gacaedc4a06bc25caeffe79e444fe9926e), the short form of [`probability_evaluate`](https://provsql.org/doxygen-sql/html/group__probability.html#gabffa40fef0e37a75d39d4c39c4a1ec0f): on a token it is the very same function, and it also takes the event itself, as a Boolean condition, so no detour through a token is needed:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH r AS (SELECT normal(20, 5) AS x)\n",
"SELECT probability((x > 30) | (x > 25)) FROM r;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"About **0.14**: roughly one referral in seven crosses the severe line. Both comparisons read the *same* `x`, so ProvSQL keeps the dependence and returns the correlation-aware $\\Pr(x > 30 \\wedge x > 25) / \\Pr(x > 25) = \\Pr(x > 30) / \\Pr(x > 25)$ (since `{x > 30} ⊂ {x > 25}`), not the product of the two marginals. It is a comparison against a constant on one distribution, so the answer is closed-form – exact even with `provsql.rv_mc_samples = 0`.\n",
"\n",
"The referral threshold of 25 is itself a convention, and labs calibrate it differently. Say it varies between labs as a [logistic distribution](https://en.wikipedia.org/wiki/Logistic_distribution) centred on 25 – the S-shaped curve of the logit link, close to a Normal but with heavier tails:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH r AS (SELECT normal(20, 5) AS x, logistic(25, 1.5) AS threshold)\n",
"SELECT probability(x > threshold) AS p_referred,\n",
" probability(x > 25) AS p_referred_fixed\n",
"FROM r;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"About **0.1886**, against **0.1587** at the fixed threshold. The Normal’s tail thins quickly above 25, so the labs with a lower threshold gain more referrals than the labs with a higher one lose. This compares two random variables of different families, which ProvSQL integrates numerically: the answer is deterministic, to quadrature precision, with no sampling.\n",
"\n",
"## Problem 5: A Probabilistic Total\n",
"\n",
"So far, uncertainty came in two forms: *events*, true or false (Problems 1 to 3), and *random variables*, numbers drawn from a distribution (Problem 4). The third form is an *aggregate*: when the rows you add up are themselves uncertain, their total is a random quantity, and ProvSQL tracks it as an `agg_token` – the aggregate counterpart of a provenance token.\n",
"\n",
"Each row of `cases` reports a day’s case count for a region, present only with some probability:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"DROP TABLE IF EXISTS cases CASCADE;\n",
"CREATE TABLE cases(day int, region text, n int, p float);\n",
"INSERT INTO cases VALUES (1, 'North', 3, 0.5), (1, 'North', 4, 0.5), (1, 'South', 2, 0.8);\n",
"SELECT add_provenance('cases');\n",
"SELECT day, region, n, p, set_prob(provenance(), p) FROM cases;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Sum each region’s counts with an ordinary `GROUP BY`. Because the inputs are uncertain, each total comes out as an `agg_token` rather than a plain number – look at the per-region totals:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"DROP TABLE IF EXISTS casesum CASCADE;\n",
"CREATE TABLE casesum AS SELECT region, sum(n) AS total FROM cases GROUP BY region;\n",
"SELECT * FROM casesum;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The North has two possible contributions, 3 and 4, each present with probability 0.5. Its *expected* total – the average over the ways the days could turn out – is:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT expected(total) FROM casesum WHERE region = 'North';"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"4.67. The region’s row is there only where one of its days is, which leaves three equally likely ways: 3, 4, or both, worth 3, 4 and 7, so $(3 + 4 + 7) / 3$. Now condition on an observation: suppose we *know* the high-count day (`n = 4`) really happened. The expected total *given* that:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT expected(total | (SELECT provenance() FROM cases WHERE n = 4))\n",
"FROM casesum WHERE region = 'North';"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"5.5 – a certain 4 plus the still-uncertain 3 (worth $0.5 \\cdot 3 = 1.5$ on average). The `|` operator conditioned the aggregate exactly as it conditioned the discrete events in Problem 1 and the continuous variable in Problem 4.\n",
"\n",
"Like the conditioned biomarker, the conditioned total is a value in its own right – here an `agg_token`. Select it directly to get its token:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT total | (SELECT provenance() FROM cases WHERE n = 4) AS conditioned_total\n",
"FROM casesum WHERE region = 'North';"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Click that token to see its circuit** – the same `∣` gate as before, now over the aggregate and the evidence – and press `Evaluate`. An aggregate is a value too, so the evaluation cell starts on `Distribution profile`; run it: the total ranges over \\[0, 7\\] and its mean μ is **5.5**, the same answer as above, but the histogram stays empty (*no samples*). Switch the cell to `Moment` (order *k* = 1, `raw`) and run it again: 5.5 once more. A moment of an aggregate is computed *exactly* – by enumerating the rows’ contributions and weighting each by its probability – so it needs no sampling and is unaffected by the `provsql.rv_mc_samples = 0` you set in Problem 4; the histogram and individual samples, by contrast, are drawn by Monte Carlo, and would need sampling switched back on.\n",
"\n",
"## Problem 6: Ruling Worlds Out\n",
"\n",
"[`repair_key`](https://provsql.org/doxygen-sql/html/group__table__management.html#gacfbe67438895e5f153b01bb9352cbc54) in Problem 1 imposed one kind of constraint – *at most one* row of a key group is real. Many real rules are not keys but relations between *pairs* of rows: “no two reported doses of one vaccine fall within 21 days”, “no two outbreak cases at one site are reported within the incubation window”. No key declaration captures these. They are **denial constraints** – a query describing a forbidden pattern – and ProvSQL conditions on their *non-occurrence* with the event-negation operator `!` ([`provenance_not`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga96b4b52dc83f9cfd8d1bcb9da463b2c1)).\n",
"\n",
"An immunisation registry merges dose reports from several sources, so each reported dose is only *probably* a real administration. A data-quality rule says two doses of the same vaccine must be at least 21 days apart. Here are four uncertain dose reports for one patient – the date each was administered and the probability it is genuine. The Mar 14 report sits close to two others: it is within 21 days of both Mar 4 (10 days earlier) and Mar 28 (14 days later)."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"DROP TABLE IF EXISTS doses CASCADE;\n",
"CREATE TABLE doses(id int, administered date, p float);\n",
"INSERT INTO doses VALUES\n",
" (1, '2024-03-04', 0.5),\n",
" (2, '2024-03-14', 0.5),\n",
" (3, '2024-03-28', 0.5),\n",
" (4, '2024-04-30', 0.8);\n",
"SELECT add_provenance('doses');\n",
"SELECT id, administered, p, set_prob(provenance(), p) FROM doses;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The forbidden pattern – “some two doses are fewer than 21 days apart” – is an ordinary self-join. Materialise it, collapsing all the witnessing pairs into a single `DISTINCT` row: that one row’s *provenance* is the violation event `W`, “the record has a too-close pair”. Two pairs qualify – (Mar 4, Mar 14) and (Mar 14, Mar 28) – and they *share* the Mar 14 dose, so `W` is not a simple product of independent pairs; ProvSQL tracks the shared dose and gets the overlap right (the same correlation-awareness as Problem 2). So `provenance()` over the `violation` table is `W`, and `!provenance()` is the complementary “valid record” event:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"DROP TABLE IF EXISTS violation;\n",
"CREATE TEMP TABLE violation AS\n",
" SELECT DISTINCT 1\n",
" FROM doses a JOIN doses b\n",
" ON a.id < b.id AND abs(a.administered - b.administered) < 21;\n",
"\n",
"SELECT probability(provenance()) AS p_violation,\n",
" probability(!provenance()) AS p_valid\n",
"FROM violation;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The clash has probability 0.375, so a valid record (`!provenance()`) has probability 0.625.\n",
"\n",
"Now condition each dose on the record being valid – one row per dose. Prior and posterior are the *same* row token, `provenance()`, evaluated two ways: unconditioned, and conditioned on `!W`. Each row’s own provenance stays the dose itself; the scalar subquery `(SELECT provenance() FROM violation)` names `W` without adding it to the row’s lineage:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT d.id,\n",
" probability(provenance()) AS prior,\n",
" probability(provenance() | !(SELECT provenance() FROM violation)) AS posterior\n",
"FROM doses d\n",
"ORDER BY d.id;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Same prior, different posterior. A valid record is evidence against exactly the doses the constraint could have caught, in proportion to how implicated each is: the Mar 14 dose (dose 2), which would clash with *either* neighbour, drops the furthest – from 0.5 to **0.2** – while doses 1 and 3, each in only one of the two possible violations, drop to **0.4**; dose 4, far from the rest and in no possible clash, is untouched at **0.8**. The constraint here was an arbitrary query: `!` turns any forbidden pattern into evidence, the way [`repair_key`](https://provsql.org/doxygen-sql/html/group__table__management.html#gacfbe67438895e5f153b01bb9352cbc54) turns a key into mutual exclusion, but without being limited to keys.\n",
"\n",
"## Problem 7: A Skewed Waiting Time\n",
"\n",
"Problem 4’s biomarker was symmetric – a Normal. But *durations* – an incubation period, a time to recovery – are not: they cannot go negative and they carry a long right tail. The natural model is a [log-normal](https://en.wikipedia.org/wiki/Log-normal_distribution) distribution, and ProvSQL has it as a constructor. Keep `provsql.rv_mc_samples = 0` from Problem 4 – everything below is closed-form.\n",
"\n",
"Model the incubation period (in days) of our pathogen as `lognormal(1.6, 0.42)` – the two parameters are the mean and standard deviation *of the underlying normal*, not of the duration itself, so the shape is easiest to read through its quantiles:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH m AS (SELECT lognormal(1.6, 0.42) AS incubation)\n",
"SELECT quantile(incubation, 0.5) AS median_days,\n",
" quantile(incubation, 0.95) AS p95_days,\n",
" expected(incubation) AS mean_days\n",
"FROM m;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The median is about **4.95** days, but the mean is higher, **5.41** – the hallmark of a right-skewed distribution, where the long tail pulls the average above the typical case. The 95th percentile sits at **9.88** days: a quarantine window of ten days clears 95% of cases. That is the question quantiles answer directly and moments cannot.\n",
"\n",
"The log-normal earns its name through a transform: the `exp` of a Normal *is* a log-normal. Building the incubation the long way, as `exp` of the underlying normal, gives an identical mean:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT expected(exp(normal(1.6, 0.42))) AS mean_via_exp;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**5.41** again: ProvSQL recognises `exp(normal(μ, σ))` as `lognormal(μ, σ)` and evaluates the closed form, no sampling. The transform functions (`exp`, `ln`, `pow`, `sqrt`) combine with all the other random-variable operations.\n",
"\n",
"Finally, two strains with different incubation profiles – which tends to keep a contact infectious longer? A wild type at `lognormal(1.6, 0.42)` against a slower variant at `lognormal(1.9, 0.42)`:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH s AS (SELECT lognormal(1.6, 0.42) AS wild,\n",
" lognormal(1.9, 0.42) AS variant)\n",
"SELECT probability(wild > variant) AS p_wild_longer FROM s;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"About **0.31**: the wild type outlasts the variant in under a third of pairings, since the variant’s larger location parameter shifts its whole distribution right. The comparison is between two log-normals, which have a registered closed form – exact, no Monte Carlo.\n",
"\n",
"A duration can also be modelled from its mechanism. If symptoms start when a growing viral load first crosses a threshold, the incubation is a *first-passage time*, whose distribution is the [inverse Gaussian](https://en.wikipedia.org/wiki/Inverse_Gaussian_distribution) (also called the Wald distribution). Fit one with the same mean and variance, `inverse_gaussian(5.41, 28)` (its variance is $\\mu^3/\\lambda$), and compare the tails:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH m AS (SELECT lognormal(1.6, 0.42) AS lognormal_model,\n",
" inverse_gaussian(5.41, 28) AS first_passage_model)\n",
"SELECT probability(lognormal_model > 10) AS p_lognormal,\n",
" probability(first_passage_model > 10) AS p_first_passage,\n",
" quantile(first_passage_model, 0.95) AS p95_first_passage\n",
"FROM m;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The two models agree on the first two moments and nearly on the tail: **0.0472** and **0.0485** of cases are still incubating after ten days, and the first-passage model’s 95th percentile is **9.94** days. The quarantine window holds under either model.\n",
"\n",
"## Problem 8: How Many, and How Often\n",
"\n",
"Every continuous variable so far was a *measurement*. But an epidemiology desk also counts things – cases per day, positives in a batch – and counts are discrete. ProvSQL provides the standard discrete families as constructors, and moments, quantiles, and comparisons on them are all exact. Keep `rv_mc_samples = 0`.\n",
"\n",
"Daily case counts in a stable outbreak follow a [Poisson](https://en.wikipedia.org/wiki/Poisson_distribution) law. At an average of 6 cases a day, how often does a day break 10 – the threshold that trips an alert?"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH d AS (SELECT poisson(6) AS cases)\n",
"SELECT expected(cases) AS mean_cases,\n",
" probability(cases > 10) AS p_alert\n",
"FROM d;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The mean is **6** by construction, and an alert fires on about **4.3%** of days – rare, but not negligible. Now a screening batch: of 20 contacts, each independently positive with probability 0.3, how likely are 10 or more positives? That is a [Binomial](https://en.wikipedia.org/wiki/Binomial_distribution):"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH b AS (SELECT binomial(20, 0.3) AS positives)\n",
"SELECT probability(positives >= 10) AS p_ten_plus FROM b;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"About **4.8%**. Both probabilities are computed by enumerating the exact mass, so `>` and `>=` on a count are precise, not sampled.\n",
"\n",
"The count models above assumed a *known* rate. Often the rate is exactly what is uncertain – an attack rate we have only estimated from a handful of cases. A [Beta](https://en.wikipedia.org/wiki/Beta_distribution) distribution is the natural model for an unknown probability, bounded to `[0, 1]`. Suppose 2 of 8 exposed contacts fell ill; a `beta(3, 7)` posterior (a uniform prior, `beta(1, 1)`, updated by 2 successes and 6 failures) summarises the attack rate:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SELECT expected(beta(3, 7)) AS rate_estimate,\n",
" quantile(beta(3, 7), 0.05) AS credible_lo,\n",
" quantile(beta(3, 7), 0.95) AS credible_hi;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The point estimate is **0.3**, with a 90% credible interval from about **0.10** to **0.55** – wide, honestly reflecting only eight observations. The mean is closed-form, and the Beta quantiles are also computed without sampling.\n",
"\n",
"## Problem 9: How Much Did the Data Teach Us?\n",
"\n",
"Problem 8 turned eight observations into a `beta(3, 7)` posterior. A natural follow-up question is *how much information* those observations carried. Information theory has standard answers, and ProvSQL exposes them as readouts (all in [nats](https://en.wikipedia.org/wiki/Nat_(unit)), still with `rv_mc_samples = 0`): [`entropy`](https://provsql.org/doxygen-sql/html/group__probability.html#ga38e278d379c14574700113af69c4b5f5) for the residual uncertainty of a distribution, and the [Kullback-Leibler divergence](https://en.wikipedia.org/wiki/Kullback%E2%80%93Leibler_divergence) [`kl`](https://provsql.org/doxygen-sql/html/group__probability.html#ga9addeeb745bd28dca41ff286c36033cc) for the distance the update moved us:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH m AS (SELECT beta(1, 1) AS prior, beta(3, 7) AS posterior)\n",
"SELECT entropy(prior) AS h_prior,\n",
" entropy(posterior) AS h_posterior,\n",
" kl(posterior, prior) AS information_gain\n",
"FROM m;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The uniform prior has differential entropy **0** (it *is* `uniform(0, 1)`, whose entropy is `ln 1`); the posterior comes out at about **−0.598** – negative, as differential entropies of concentrated densities are – so the update removed about **0.6 nats** of uncertainty. And `kl(posterior, prior)` returns exactly the same **0.598**: against a uniform prior, the divergence *is* the entropy drop ($\\int p \\ln(p/1) = -H(p)$), a textbook identity the calculator reproduces from the defining integral.\n",
"\n",
"KL is honest about impossibility, too: collapse the posterior to its point estimate and ask for the divergence from it – `kl(posterior, as_random(0.3))` – and the answer is `Infinity`. A point mass assigns probability zero to everything but `0.3`, so no finite divergence from it exists (an absolute-continuity failure, reported as such rather than approximated).\n",
"\n",
"## Problem 10: Two Subpopulations, One Measurement\n",
"\n",
"Viral titres in the programme’s cohort are bimodal: about 70% of cases are vaccinated break-throughs centred low, the rest an unvaccinated group centred high. The standard fitted-density shape for this is a [Gaussian mixture](https://en.wikipedia.org/wiki/Mixture_model), and the [`gmm`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga447dc9a6c975298f7d0dcb7d37a11648) constructor loads one directly (here with weights, means, and standard deviations straight from the fit):"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH c AS (SELECT gmm(weights => ARRAY[0.7, 0.3],\n",
" means => ARRAY[15.0, 40.0],\n",
" stddevs => ARRAY[4.0, 6.0]) AS titre)\n",
"SELECT titre,\n",
" expected(titre) AS mean_titre,\n",
" variance(titre) AS var_titre\n",
"FROM c;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The mixture is built from Bernoulli-mixture gates, so the moments are **exact** (`rv_mc_samples = 0`): the mean is $0.7 \\cdot 15 + 0.3 \\cdot 40 = 22.5$ and the variance $0.7(16 + 225) + 0.3(36 + 1600) - 22.5^2 = 153.25$. The query also returns `titre` itself: **click its token** to see those gates in its circuit cell – a Bernoulli selector choosing between the two Normal components. A tail probability – how many cases exceed the reporting threshold of 30 – is estimated by Monte Carlo over the same gates:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SET provsql.rv_mc_samples = 100000;\n",
"WITH c AS (SELECT gmm(ARRAY[0.7, 0.3], ARRAY[15.0, 40.0],\n",
" ARRAY[4.0, 6.0]) AS titre)\n",
"SELECT probability(titre > 30) AS p_report FROM c;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"about **0.286**: essentially the unvaccinated component’s mass above 30 ($0.3\\\\,\\Phi(10/6) \\approx 0.286$), with a vanishing contribution from the low mode.\n",
"\n",
"## Problem 11: Borrowed Posteriors and Forecast Tables\n",
"\n",
"Not every distribution on the desk was born in SQL. A modelling team hands over an MCMC posterior for the reproduction number `R` as a bundle of draws; a simulation report tabulates days-to-recovery as a CDF. Both load directly, and everything below is exact again – turn the sampler back off after Problem 10’s excursion:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SET provsql.rv_mc_samples = 0;\n",
"WITH p AS (SELECT empirical_samples(\n",
" ARRAY[0.8, 0.9, 0.9, 1.0, 1.1, 1.1, 1.2, 1.4]) AS r)\n",
"SELECT expected(r) AS r_mean,\n",
" probability(r > 1) AS p_epidemic_grows,\n",
" quantile(r, 0.5) AS r_median\n",
"FROM p;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[`empirical_samples`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#gad7c2e27e23bf47181b6fde3e84456c0b) loads the draws as the empirical distribution (mass `1/8` per draw, duplicates merging), so the answers are the *sample* statistics, exactly: mean **1.05**, `P(R > 1)` is the fraction of draws strictly above 1 – **0.5** – decided analytically, and the median is the exact empirical quantile **1.0**."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH f AS (SELECT empirical_cdf(\n",
" grid => ARRAY[5.0, 10.0, 15.0, 25.0],\n",
" cdf => ARRAY[0.1, 0.5, 0.8, 1.0]) AS days)\n",
"SELECT expected(days) AS mean_days,\n",
" (support(days)).lo AS lo,\n",
" (support(days)).hi AS hi\n",
"FROM f;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[`empirical_cdf`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga1def7ed4f3d9a3796d6af3fcf210ec40) reads the table as a piecewise-linear CDF – an atom of mass 0.1 at 5 days, then mass spread uniformly over each grid interval – built from the same mixture gates as Problem 10’s GMM. The mean is exact: $0.1 \\cdot 5 + 0.4 \\cdot 7.5 + 0.3 \\cdot 12.5 + 0.2 \\cdot 20 = 11.25$ days, on the support `[5, 25]`.\n",
"\n",
"## Problem 12: A Parameter That Is Itself Uncertain\n",
"\n",
"Every distribution so far had *fixed* parameters. But the assay’s baseline drifts batch to batch: the true zero-point of this batch is not `20` exactly, it is itself uncertain, say $\\mu \\sim \\mathrm{Normal}(20, 5)$. A single reading is then `normal(mu, 2)` – a Normal *whose mean is another random variable*. ProvSQL’s distribution constructors accept a `random_variable` where a parameter is expected, so the model is written as one nested call:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SET provsql.monte_carlo_seed = 1;\n",
"SET provsql.rv_mc_samples = 200000;\n",
"WITH m AS (SELECT normal(normal(20, 5), 2) AS reading)\n",
"SELECT reading,\n",
" expected(reading) AS mean_reading,\n",
" variance(reading) AS var_reading\n",
"FROM m;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The inner `normal(20, 5)` is a *latent* leaf shared by the outer Normal, so the reading marginalises over it. Its mean is still **20**, but the variance is the [law of total variance](https://en.wikipedia.org/wiki/Law_of_total_variance) at work – the measurement noise *plus* the uncertainty in the baseline, $2^2 + 5^2 = 29$ – and the query returns about **20** and **29**. A latent parameter breaks the closed-form moment recursion (the outer moment is an integral over the inner distribution), so this is the first problem that *needs* the sampler: the moments are Monte-Carlo estimates over draws of $\\mu$, which is why the sampler is switched back on here.\n",
"\n",
"The query also returns the `reading` itself. **Click its token** to open the circuit: the outer `normal` gate draws its mean from the shared inner `normal(20, 5)` *latent* leaf and its standard deviation from the fixed `2`, so the compound (hierarchical) structure is visible at a glance – one random leaf feeding the mean wire, a constant feeding the spread.\n",
"\n",
"The spread can be uncertain too. If the assay’s noise variance is not known to be 4 but drawn from an [inverse-gamma](https://en.wikipedia.org/wiki/Inverse-gamma_distribution) distribution, the usual prior for a variance, the standard deviation is its square root:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH m AS (SELECT normal(20, sqrt(inverse_gamma(3, 8))) AS reading)\n",
"SELECT expected(inverse_gamma(3, 8)) AS mean_noise_variance,\n",
" expected(reading) AS mean_reading,\n",
" variance(reading) AS var_reading\n",
"FROM m;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`inverse_gamma(3, 8)` has mean $8/(3-1) = 4$, so on average the noise is the same as before, and the reading’s variance is again about **4** (a Monte-Carlo estimate): by the law of total variance, with the mean fixed at 20, the reading’s variance is the expected noise variance. The uncertainty in the spread shows in the shape instead: the reading has heavier tails than a `normal(20, 2)`, the more so as the prior on the variance is vaguer.\n",
"\n",
"## Problem 13: Learning That Parameter From Data\n",
"\n",
"A latent parameter is a prior; the point of a prior is to update it. Three control readings come back for this batch – `23`, `24`, `22` – each a noisy measurement `normal(mu, 2)` of the *same* unknown baseline `mu`. Conditioning on an observed value uses the same `|` operator, now with an equality event: `| (normal(mu, 2) = d)` reads “given that this reading equalled `d`” and contributes that observation’s likelihood (equivalently, [`observe`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga909d5429f74a962262eb790c0ed6c06b)). [`and_agg`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga92aa95852d53bcb64d905bb25db11b0d) folds the per-row events into one evidence token – surfaced here as `ev.e` – which the moment readouts take as their conditioning argument, giving the posterior of `mu` given the data:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH g AS (SELECT normal(20, 5) AS mu),\n",
" ev AS (SELECT and_agg(| (normal(g.mu, 2) = d)) AS e\n",
" FROM g CROSS JOIN (VALUES (23.0), (24.0), (22.0)) AS t(d))\n",
"SELECT expected(g.mu) AS prior_mean,\n",
" expected(g.mu, ev.e) AS posterior_mean,\n",
" variance(g.mu, ev.e) AS posterior_var,\n",
" evidence(ev.e) AS marginal_likelihood,\n",
" ev.e AS evidence_token\n",
"FROM g, ev;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Every reading shares the one `mu` leaf – the materialised CTE `g` is a single node – so the three evidence factors condition the same latent. Click the `evidence_token` UUID in `studio` to open its circuit: the evidence is a conjunction of the three observations, each an `=` event reading the *same* `mu` leaf; that one shared node is what couples the readings into a single posterior rather than three independent updates. The prior mean is **20**; the posterior mean pulls toward the data at **22.8481**, and the posterior variance collapses to **1.2658** – and these numbers are *exact*, not sampling estimates. This model is the conjugate [Normal-Normal](https://en.wikipedia.org/wiki/Conjugate_prior) shape, which ProvSQL recognises and computes in closed form: deterministic, to full float precision, and independent of any sample budget (it still answers under `provsql.rv_mc_samples = 0`). Precision is the reciprocal of variance, $\\tau = 1/\\sigma^2$, and precisions add. The prior variance $\\sigma_0^2 = 5^2 = 25$ gives prior precision $\\tau_0 = 1/25$; each reading has variance $2^2 = 4$, precision $1/4$, so the three together add $3/4$. The posterior precision is therefore $\\tau_0 + 3/4 = 1/25 + 3/4 = 0.79$, and inverting it back gives the posterior variance $1/0.79 = 1.2658$. The posterior mean is the precision-weighted average $(20/25 + 69/4)/0.79 = 22.8481$. [`evidence`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga58b0fdca589baadd656773631f7c25e1) returns the marginal likelihood $P(\\text{data})$, here **0.001172** – exactly the multivariate-Normal prior predictive, and the quantity you would compare across competing models. The same closed-form recognition covers the other textbook pairs (a Gamma prior observed through Poisson counts or Exponential gaps, a Beta prior through Binomial counts…). A model *outside* these shapes – say the latent reaching the reading through arithmetic – is answered by importance sampling instead: each `| (normal(mu, 2) = d)` reweights the prior draws of `mu` by the observation’s density, giving the same posterior to Monte Carlo tolerance under the sample budget.\n",
"\n",
"Which reading moved the posterior most? [`shapley_observe`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga458e8ee340827c6fe07c6aa77199ea54) splits the shift of the posterior mean, from the prior’s 20 to 22.8481, between the observations, by their Shapley values. Each observation records the value observed, which [`get_extra`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga12ce1bcd1609204d091d18d8912b840b) reads back:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH g AS (SELECT normal(20, 5) AS mu),\n",
" ev AS (SELECT and_agg(| (normal(g.mu, 2) = d)) AS e\n",
" FROM g CROSS JOIN (VALUES (23.0), (24.0), (22.0)) AS t(d))\n",
"SELECT get_extra(s.observation) AS reading,\n",
" round(s.value::numeric, 4) AS shift\n",
"FROM g, ev, shapley_observe(g.mu::uuid, ev.e) s\n",
"ORDER BY reading;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The reading of 24, farthest from the prior, contributes **1.6119**, the 23 **0.9494**, the 22 **0.2869**; together they make the whole shift, 2.848. The second argument of [`shapley_observe`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga458e8ee340827c6fe07c6aa77199ea54) can be `'variance'`, to attribute the shrinking of the posterior variance instead.\n",
"\n",
"Likelihood weighting has a weak point: data far from the prior. Suppose another batch is read on an analyser with a known bias of +1, and its readings, `38`, `39`, `37`, sit more than three prior standard deviations above 20. The bias puts the latent behind arithmetic, outside the conjugate shapes:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"SET provsql.rv_mc_samples = 10000;\n",
"WITH g AS (SELECT normal(20, 5) AS mu),\n",
" ev AS (SELECT and_agg(| (normal(g.mu + 1, 2) = d)) AS e\n",
" FROM g CROSS JOIN (VALUES (38.0), (39.0), (37.0)) AS t(d))\n",
"SELECT expected(g.mu, ev.e) AS posterior_mean FROM g, ev;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Almost no prior draw of `mu` comes near the data, so a few draws carry all the weight. ProvSQL answers about **36.0**, with a `WARNING` that the *effective sample size* is low (around 14 of the 10,000 draws): the estimate rests on a handful of them. The warning fires when the effective sample size falls below `provsql.ess_warn_fraction` of the draws, 10% by default (`0` silences it). Here the cure is to give the model back its conjugate shape, moving the bias to the data side:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"provsql": {}
},
"source": [
"WITH g AS (SELECT normal(20, 5) AS mu),\n",
" ev AS (SELECT and_agg(| (normal(g.mu, 2) = d - 1)) AS e\n",
" FROM g CROSS JOIN (VALUES (38.0), (39.0), (37.0)) AS t(d))\n",
"SELECT expected(g.mu, ev.e) AS posterior_mean FROM g, ev;\n",
"RESET provsql.rv_mc_samples;"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The exact posterior mean is **36.1392**: the sampled answer was close, but only the closed form shows that it was.\n",
"\n",
"## Recap\n",
"\n",
"One operator, `|`, carried the first six problems with a single meaning throughout – conditional probability, $\\Pr(A \\mid B) = \\Pr(A \\wedge B) / \\Pr(B)$ – over three kinds of value: discrete events (Problems 1–3 and 6), a continuous `random_variable` (Problem 4), and a probabilistic aggregate `agg_token` (Problem 5). Problems 7 and 8 widened the random-variable vocabulary (the log-normal and its transforms, the discrete counts, the Beta rate, quantiles throughout), and Problems 9–11 layered onto the same surface the information-theoretic readouts ([`entropy`](https://provsql.org/doxygen-sql/html/group__probability.html#ga38e278d379c14574700113af69c4b5f5), [`kl`](https://provsql.org/doxygen-sql/html/group__probability.html#ga9addeeb745bd28dca41ff286c36033cc)) and the data-driven constructors ([`gmm`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga447dc9a6c975298f7d0dcb7d37a11648), [`empirical_samples`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#gad7c2e27e23bf47181b6fde3e84456c0b) / [`empirical_cdf`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga1def7ed4f3d9a3796d6af3fcf210ec40)). Problems 12 and 13 closed the loop into full Bayesian inference: a distribution parameter is itself a `random_variable` (a latent prior). Conditioning on the data with `|` – the per-row events folded by [`and_agg`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga92aa95852d53bcb64d905bb25db11b0d), their marginal likelihood read by [`evidence`](https://provsql.org/doxygen-sql/html/group__random__variable__type.html#ga58b0fdca589baadd656773631f7c25e1) – updates it into the posterior, read straight back with the same [`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e) / [`variance`](https://provsql.org/doxygen-sql/html/group__probability.html#gab1d52fb442dd262c0ac950d349a87ca3) readouts: in closed form (exact) for the recognised conjugate shapes, by likelihood weighting otherwise. A few mechanics recurred:\n",
"\n",
"- Each model was built in the database. [`add_provenance`](https://provsql.org/doxygen-sql/html/group__table__management.html#ga00f0d0b04b2b693c974e72aaf095cb3b) registers a table for tuple-independent tracking and [`set_prob`](https://provsql.org/doxygen-sql/html/group__gate__manipulation.html#ga17334fe1d3969d969eaa18e32a93b4e0) attaches a probability to each row; [`repair_key`](https://provsql.org/doxygen-sql/html/group__table__management.html#gacfbe67438895e5f153b01bb9352cbc54) is the alternative registration, making the rows of a key group mutually exclusive outcomes, and the negation operator `!` conditions on the non-occurrence of an arbitrary forbidden pattern (Problem 6) – a denial constraint beyond keys. The model is ordinary SQL data: it persists, and is queried and updated, across sessions.\n",
"- Provenance is recorded per result row as a circuit of gates, and equal sub-expressions are the same gate. A base event shared between two queries is therefore one node, so joint, disjoint, and conditional probabilities come out consistent without assuming independence. That is the gap between ProvSQL’s 0.44 and the independence estimate 0.545 in Problem 2.\n",
"- [`probability_evaluate`](https://provsql.org/doxygen-sql/html/group__probability.html#gabffa40fef0e37a75d39d4c39c4a1ec0f) (or its short form [`probability`](https://provsql.org/doxygen-sql/html/group__probability.html#gacaedc4a06bc25caeffe79e444fe9926e)) returns an exact probability and selects the evaluation method itself; Studio’s evaluation strip reports which one ran. Sampling appears only where no exact method applies (the full distribution profiles in Problems 4 and 5, the mixture tail in Problem 10) or where you request it (Problem 3’s Monte-Carlo run), and then under an explicit sample budget or error guarantee.\n",
"\n",
"None of this required leaving SQL: the questions were ordinary queries, and the probabilistic answers were functions applied to their provenance."
]
}
]
}