{
 "nbformat": 4,
 "nbformat_minor": 5,
 "metadata": {
  "kernelspec": {
   "name": "provsql-studio",
   "display_name": "ProvSQL (SQL)",
   "language": "sql"
  },
  "language_info": {
   "name": "sql"
  },
  "provsql": {
   "scheme": "semiring",
   "database": "cs9",
   "generated_from": "doc/source/user/casestudy9.rst"
  }
 },
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Case Study: A Sales Forecast Dashboard\n",
    "\n",
    "This case study runs the queries of an ordinary sales dashboard over a pipeline of deals that may or may not close. Every figure on the dashboard then depends on which deals close, and ProvSQL answers the same SQL with the distribution of that figure. It demonstrates aggregates and their expected values, `ROLLUP` subtotals, aggregates over windows, shares of a total, ranking and top-k over aggregates, comparisons with a target, arithmetic between aggregates, Boolean aggregates and `EXISTS` as values, percentages with a `NULLIF` divisor, aggregates of aggregates, grouping by an aggregate’s value, `DISTINCT ON`, [`plain`](https://provsql.org/doxygen-sql/html/group__aggregate__provenance.html#gadd4022a0b0f1e64975b8d1a9613edf17) for a value meant without provenance, and the purposes an answer may serve under the customers’ consent.\n",
    "\n",
    "## The Scenario\n",
    "\n",
    "A sales team tracks twelve open deals in three regions over two quarters. The CRM gives each deal a *win probability* from its stage in the pipeline; deals close independently of each other. Management wants the usual dashboard: revenue per region and quarter, each region’s share, which region leads, whether each region meets its target, the typical deal size.\n",
    "\n",
    "Your tasks:\n",
    "\n",
    "- forecast revenue per region, counting or not the regions where no deal is made,\n",
    "- follow revenue through the quarters, and each deal’s share of it,\n",
    "- find which region is likely to lead, and each region’s likely rank,\n",
    "- check each region against its target, and its growth from Q1 to Q2,\n",
    "- describe the deals that close: whether a big one does, their share of big deals, their spread, the largest one per region,\n",
    "- use each answer only for the purposes its customers consented to.\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 consent CASCADE;\n",
    "DROP TABLE IF EXISTS deal CASCADE;\n",
    "DROP TABLE IF EXISTS region CASCADE;\n",
    "DROP TYPE IF EXISTS purpose CASCADE;"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "CREATE TABLE region (\n",
    "    name   text PRIMARY KEY,\n",
    "    target integer NOT NULL     -- revenue target, thousands of euros\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "CREATE TABLE deal (\n",
    "    id       integer PRIMARY KEY,\n",
    "    customer text NOT NULL,\n",
    "    region   text NOT NULL REFERENCES region(name),\n",
    "    quarter  text NOT NULL,\n",
    "    amount   integer NOT NULL,          -- thousands of euros\n",
    "    win_prob double precision NOT NULL  -- probability that the deal closes\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "INSERT INTO region (name, target) VALUES\n",
    "    ('North', 150),\n",
    "    ('South', 120),\n",
    "    ('West',  100);"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "INSERT INTO deal (id, customer, region, quarter, amount, win_prob) VALUES\n",
    "    ( 1, 'Arctis',    'North', 'Q1',  80, 0.9),\n",
    "    ( 2, 'Borealis',  'North', 'Q1',  45, 0.5),\n",
    "    ( 3, 'Fjordline', 'North', 'Q2', 120, 0.3),\n",
    "    ( 4, 'Glacier',   'North', 'Q2',  30, 0.8),\n",
    "    ( 5, 'Meridian',  'South', 'Q1',  60, 0.7),\n",
    "    ( 6, 'Solstice',  'South', 'Q1',  25, 0.9),\n",
    "    ( 7, 'Tropica',   'South', 'Q2',  90, 0.4),\n",
    "    ( 8, 'Zenith',    'South', 'Q2',  40, 0.6),\n",
    "    ( 9, 'Canyon',    'West',  'Q1',  70, 0.5),\n",
    "    (10, 'Horizon',   'West',  'Q1',  35, 0.8),\n",
    "    (11, 'Mesa',      'West',  'Q2',  55, 0.6),\n",
    "    (12, 'Sierra',    'West',  'Q2', 150, 0.2);"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "-- The purposes each customer consented to the use of their data for\n",
    "CREATE TYPE purpose AS ENUM ('forecasting', 'analytics', 'marketing');"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "CREATE TABLE consent (\n",
    "    customer text PRIMARY KEY,\n",
    "    purposes purpose[] NOT NULL\n",
    ");"
   ],
   "outputs": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "INSERT INTO consent (customer, purposes) VALUES\n",
    "    ('Arctis',    '{forecasting,analytics,marketing}'),\n",
    "    ('Borealis',  '{forecasting,analytics}'),\n",
    "    ('Fjordline', '{forecasting}'),\n",
    "    ('Glacier',   '{forecasting,analytics,marketing}'),\n",
    "    ('Meridian',  '{forecasting,analytics,marketing}'),\n",
    "    ('Solstice',  '{forecasting}'),\n",
    "    ('Tropica',   '{forecasting,analytics,marketing}'),\n",
    "    ('Zenith',    '{forecasting,analytics,marketing}'),\n",
    "    ('Canyon',    '{forecasting,analytics,marketing}'),\n",
    "    ('Horizon',   '{forecasting,analytics}'),\n",
    "    ('Mesa',      '{forecasting,analytics,marketing}'),\n",
    "    ('Sierra',    '{forecasting,analytics,marketing}');"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This creates three tables:\n",
    "\n",
    "- `region` – the three regions (North, South, West) and each one’s revenue target, in thousands of euros\n",
    "- `deal` – twelve open deals, each with a customer, a region, a quarter, an amount in thousands of euros, and its win probability `win_prob`\n",
    "- `consent` – the purposes (`forecasting`, `analytics`, `marketing`) for which each customer allows their data to be used\n",
    "\n",
    "## Step 1: The Pipeline\n",
    "\n",
    "Look at the regions and the deals:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT * FROM region ORDER BY name;\n",
    "SELECT * FROM deal ORDER BY region, quarter, id;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Track the deals, and give each one its win probability:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT add_provenance('deal');\n",
    "SELECT set_prob(provenance(), win_prob) FROM deal;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each set of deals that close is now a possible world. Its probability is the product of the probabilities of its deals closing and of the other deals not closing. `region` is not tracked: its targets are certain.\n",
    "\n",
    "## Step 2: Expected Revenue per Region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region,\n",
    "       sum(amount) AS pipeline,\n",
    "       round(expected(sum(amount))::numeric, 2) AS if_any_deal,\n",
    "       round(expected(coalesce(sum(amount), 0))::numeric, 2) AS forecast,\n",
    "       round(avg(amount), 1) AS avg_deal\n",
    "FROM deal\n",
    "GROUP BY region\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pipeline` is the revenue if every deal closes: 275 for North. The marker `(*)` shows an aggregate result, whose value depends on the world. `round(avg(amount), 1)` rounds the average in each world, not only its value on the data as it is.\n",
    "\n",
    "The two expectations answer two questions. In a world where no deal is made in a region, its group is empty and `sum(amount)` is NULL, as SQL’s `sum` is over no rows. [`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e) averages over the worlds where its argument has a value, so `expected(sum(amount))` is the expected revenue *given that some deal is made in the region*: 155.59 for North. `coalesce` turns that NULL into 0, and `expected` then averages over every world: the forecast, 154.50 for North, which is `80 × 0.9 + 45 × 0.5 + 120 × 0.3 + 30 × 0.8`, each amount times the probability of its deal closing. The two are consistent. North’s row only exists in the worlds where some deal is made in North, which have probability `probability_evaluate(provenance())` = 0.9930, and 155.59 × 0.9930 = 154.50.\n",
    "\n",
    "A dashboard counts a region, or a quarter, where no deal is made as 0. Most of the following steps read the revenue from two views that do so. `quarter_revenue` has a row for each region and quarter: it crosses `region` with the list of quarters and joins the deals with a `LEFT JOIN`, so that each pair keeps its row even when no deal is made in it, and `coalesce` turns the revenue of such a row into 0. `region_revenue` adds up the two quarters of each region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "CREATE OR REPLACE VIEW quarter_revenue AS\n",
    "  SELECT r.name AS region, r.target, q.quarter,\n",
    "         coalesce(sum(d.amount), 0) AS revenue\n",
    "  FROM region r CROSS JOIN (VALUES ('Q1'), ('Q2')) AS q(quarter)\n",
    "       LEFT JOIN deal d ON d.region = r.name AND d.quarter = q.quarter\n",
    "  GROUP BY r.name, q.quarter;\n",
    "\n",
    "CREATE OR REPLACE VIEW region_revenue AS\n",
    "  SELECT region, target, sum(revenue) AS revenue\n",
    "  FROM quarter_revenue\n",
    "  GROUP BY region, target;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3: Subtotals with `ROLLUP`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region, quarter,\n",
    "       round(expected(sum(revenue))::numeric, 2) AS forecast\n",
    "FROM quarter_revenue\n",
    "GROUP BY ROLLUP (region, quarter)\n",
    "ORDER BY region, quarter;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each subtotal is a sum in its own right, and the forecasts add up: North’s Q1 (94.50) and Q2 (60.00) make 154.50, and the three regions 405.00, the grand total.\n",
    "\n",
    "## Step 4: Revenue to Date and Each Deal’s Share\n",
    "\n",
    "An aggregate used as a window function is computed in each world, over the rows of its frame that exist there. Each deal gets the revenue of its region up to its quarter, and its share of its region’s revenue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT customer, region, quarter, amount,\n",
    "       sum(amount) OVER (PARTITION BY region ORDER BY quarter) AS to_date,\n",
    "       round(expected(sum(amount) OVER (PARTITION BY region\n",
    "                                        ORDER BY quarter),\n",
    "                      provenance())::numeric, 2) AS expected_to_date,\n",
    "       round(expected(100.0 * amount\n",
    "                      / sum(amount) OVER (PARTITION BY region),\n",
    "                      provenance())::numeric, 2) AS pct_of_region\n",
    "FROM deal\n",
    "ORDER BY region, quarter, id;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`to_date` is the value if every deal closes, 275 for Fjordline: all of North’s deals, Q1 and Q2. Each row has the provenance of its deal, so a value computed for that row only makes sense in the worlds where the deal closes: the second argument of [`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e) restricts the average to those worlds. Given that Fjordline closes, North’s revenue to date is `120 + 80 × 0.9 + 45 × 0.5 + 30 × 0.8 = 238.50`, and Sierra makes 63.39% of West’s revenue on average.\n",
    "\n",
    "Without that argument, [`expected`](https://provsql.org/doxygen-sql/html/group__probability.html#ga7124b41224adc29ff5405d5ad6db277e) also averages over the worlds where Sierra does not close: Sierra’s share would come out as 166.87%, its 150 divided by revenues that do not include it. Studio adds this condition itself when it evaluates such a value.\n",
    "\n",
    "## Step 5: Each Region’s Share"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region,\n",
    "       round(expected(revenue::numeric\n",
    "                      / (SELECT sum(amount) FROM deal))::numeric, 4)\n",
    "         AS share\n",
    "FROM region_revenue\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The share is a ratio of two sums that vary together: in each world, a region’s revenue over the total revenue of that same world. Its expected value (0.3863 for North) is not the ratio of the forecasts (154.50 / 405.00 = 0.3815): in a world where North closes a large deal, the total is larger too.\n",
    "\n",
    "## Step 6: Which Region Leads?\n",
    "\n",
    "The top region is the first row of a sort by revenue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region,\n",
    "       round(probability_evaluate(provenance())::numeric, 4) AS p_top\n",
    "FROM (SELECT region\n",
    "      FROM region_revenue\n",
    "      ORDER BY revenue DESC\n",
    "      FETCH FIRST 1 ROW WITH TIES) t\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`FETCH FIRST 1 ROW` is the SQL-standard way of writing `LIMIT 1`; its `WITH TIES` option, which `LIMIT` lacks, also keeps the rows that tie with the last one, here a region with the same revenue as the leader.\n",
    "\n",
    "Each region comes out with the probability that it leads: North 0.4516, South 0.2790, West 0.2960. If every deal closes, West leads with 310, but that lead rests on Sierra, a deal of 150 that closes with probability 0.2.\n",
    "\n",
    "`rank()` gives each region its rank in each world:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region, rk, round(expected(rk)::numeric, 4) AS expected_rank\n",
    "FROM (SELECT region, rank() OVER (ORDER BY revenue DESC) AS rk\n",
    "      FROM region_revenue) t\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "North’s expected rank, 1.7483, is the best of the three.\n",
    "\n",
    "## Step 7: On Target?\n",
    "\n",
    "Does each region meet its revenue target? Compare its revenue with the target in `region`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region, revenue >= target AS on_target,\n",
    "       round(probability_evaluate(provenance())::numeric, 4) AS p\n",
    "FROM region_revenue\n",
    "ORDER BY region, on_target;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The comparison is true in some worlds and false in others, so each region comes out once per truth value, with its probability: North meets its target of 150 with probability 0.5490 and misses it with probability 0.4510, including the worlds where no deal is made in North.\n",
    "\n",
    "## Step 8: Quarter-over-Quarter Growth\n",
    "\n",
    "A region’s growth compares its two rows in `quarter_revenue`, one per quarter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT q1.region,\n",
    "       round(expected(q2.revenue - q1.revenue)::numeric, 2) AS growth\n",
    "FROM quarter_revenue q1\n",
    "     JOIN quarter_revenue q2 ON q2.region = q1.region\n",
    "WHERE q1.quarter = 'Q1' AND q2.quarter = 'Q2'\n",
    "ORDER BY q1.region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The difference is computed in each world, so its expected value is the difference of the two forecasts: North shrinks by 34.50 on average (`(120 × 0.3 + 30 × 0.8) - (80 × 0.9 + 45 × 0.5)`), and West, 0.00, is flat.\n",
    "\n",
    "The regions whose Q2 revenue beats their Q1 revenue:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT q1.region,\n",
    "       round(probability_evaluate(provenance())::numeric, 4) AS p_growth\n",
    "FROM quarter_revenue q1\n",
    "     JOIN quarter_revenue q2 ON q2.region = q1.region\n",
    "WHERE q1.quarter = 'Q1' AND q2.quarter = 'Q2'\n",
    "  AND q2.revenue > q1.revenue\n",
    "ORDER BY q1.region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each region comes out with the probability that its Q2 revenue beats its Q1 revenue: West 0.4400, although its expected growth is zero, and South, the likeliest to grow, 0.5080.\n",
    "\n",
    "## Step 9: The Share of Big Deals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region,\n",
    "       round(expected(100.0 * count(*) FILTER (WHERE amount >= 60)\n",
    "                      / NULLIF(count(*), 0))::numeric, 2) AS pct_big\n",
    "FROM deal\n",
    "GROUP BY region\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The percentage of closed deals worth at least 60 is computed in each world, `FILTER` and `NULLIF` included: 49.35% on average for North.\n",
    "\n",
    "## Step 10: Any Big Deal Closed?\n",
    "\n",
    "A Boolean aggregate is true in some worlds and false in others. Cast to an integer, it is 1 or 0, and its expected value is the probability that it is true. An `EXISTS` in the `SELECT` list is a Boolean value too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region, bool_or(amount >= 100) AS big_deal,\n",
    "       round(expected(coalesce(bool_or(amount >= 100), false)::int)::numeric, 4)\n",
    "         AS p_big\n",
    "FROM deal\n",
    "GROUP BY region\n",
    "ORDER BY region;\n",
    "\n",
    "SELECT r.name,\n",
    "       EXISTS (SELECT * FROM deal d\n",
    "               WHERE d.region = r.name AND d.amount >= 100) AS big_deal,\n",
    "       round(probability_evaluate(provenance())::numeric, 4) AS p\n",
    "FROM region r\n",
    "ORDER BY r.name, big_deal;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both queries give North a big deal with probability 0.3000, the probability that Fjordline closes. In the first, `coalesce` makes `bool_or` false in the worlds where no deal is made in the region (Step 2). The second reads `region`, which is not tracked: each region exists in every world and comes out once per truth value. South has no big deal in any world.\n",
    "\n",
    "## Step 11: The Best Region and the Average Region\n",
    "\n",
    "An aggregate over the regions’ revenues is computed in each world too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT round(expected(max(revenue))::numeric, 2) AS best_region,\n",
    "       round(expected(avg(revenue))::numeric, 2) AS average_region\n",
    "FROM region_revenue;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The best region’s revenue is 194.73 on average, well above any single region’s forecast: in each world, it is the revenue of the luckiest region. The average over the regions, 135.00, is the mean of the three forecasts of Step 2.\n",
    "\n",
    "## Step 12: How Many Deals Does a Region Close?\n",
    "\n",
    "Grouping by an aggregate’s value groups the regions by the number of deals they close, in each world:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT n AS deals_closed,\n",
    "       round(probability_evaluate(provenance())::numeric, 4) AS p\n",
    "FROM (SELECT region, count(*) AS n FROM deal GROUP BY region) t\n",
    "GROUP BY n\n",
    "ORDER BY n;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The row for `n` exists in the worlds where *some* region closes exactly `n` deals: most likely 2 (0.7629) or 3 (0.7458). Several values can hold at once, one per region, so the probabilities add up to more than 1.\n",
    "\n",
    "## Step 13: The Largest Deal of Each Region\n",
    "\n",
    "`DISTINCT ON` keeps the first row of each region in the given order:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region, customer, amount,\n",
    "       round(probability_evaluate(provenance())::numeric, 4) AS p\n",
    "FROM (SELECT DISTINCT ON (region) region, customer, amount\n",
    "      FROM deal\n",
    "      ORDER BY region, amount DESC) t\n",
    "ORDER BY region, amount DESC;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each deal is the largest of its region in the worlds where it closes and no larger one does. Arctis (80) is North’s largest with probability `0.9 × (1 - 0.3) = 0.63`: it closes, and Fjordline (120) does not.\n",
    "\n",
    "## Step 14: The Spread of Deal Sizes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region,\n",
    "       round(stddev(amount), 1) AS sd,\n",
    "       round(expected(stddev(amount))::numeric, 2) AS expected_sd\n",
    "FROM deal\n",
    "GROUP BY region\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A standard deviation needs at least two deals, so `expected` averages it over the worlds where the region closes at least two: 33.31 for North, against 40.1 if every deal closes.\n",
    "\n",
    "## Step 15: A Value Meant Without Provenance\n",
    "\n",
    "A dashboard label turns the revenue into text:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region, sum(amount)::text || ' k€' AS label\n",
    "FROM deal\n",
    "GROUP BY region\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A text cannot change from world to world, so the label is computed on the data as it is, without provenance, and a `WARNING` says so, naming what read the value (`reader: operator`). When that value is what you want, wrap it in [`plain`](https://provsql.org/doxygen-sql/html/group__aggregate__provenance.html#gadd4022a0b0f1e64975b8d1a9613edf17) to say so, and the warning goes away:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SELECT region, plain(sum(amount))::text || ' k€' AS label\n",
    "FROM deal\n",
    "GROUP BY region\n",
    "ORDER BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Where reading a value without provenance would be a mistake, set `provsql.implicit_freeze` to `'error'`: the first query is then refused instead of warned about, and the one with [`plain`](https://provsql.org/doxygen-sql/html/group__aggregate__provenance.html#gadd4022a0b0f1e64975b8d1a9613edf17) is still accepted:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "SET provsql.implicit_freeze = 'error';\n",
    "SELECT region, sum(amount)::text || ' k€' AS label\n",
    "FROM deal\n",
    "GROUP BY region;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The error carries the same `DETAIL` line as the warning. Restore the default:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "RESET provsql.implicit_freeze;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See `aggregation` for the aggregates and their values in every world, and `plain-sql` for [`plain`](https://provsql.org/doxygen-sql/html/group__aggregate__provenance.html#gadd4022a0b0f1e64975b8d1a9613edf17) and provsql.implicit_freeze.\n",
    "\n",
    "## Step 16: Purposes a Result May Serve\n",
    "\n",
    "Marketing plans a campaign in the regions with no big deal (100 k€ or more). Each customer allows their data to be used for some purposes only, and an answer may serve a purpose only if all the deals it reads may. Map each deal to its customer’s purposes, and evaluate with [`sr_consent`](https://provsql.org/doxygen-sql/html/group__compiled__semirings.html#ga011ddecbec45c7f5ed71d069878927eb):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "provsql": {}
   },
   "source": [
    "DROP TABLE IF EXISTS deal_consent;\n",
    "CREATE TABLE deal_consent AS\n",
    "  SELECT d.provsql AS provenance, c.purposes AS value\n",
    "  FROM deal d JOIN consent c USING (customer);\n",
    "\n",
    "SELECT r.name,\n",
    "       (sr_consent(provenance(), 'deal_consent',\n",
    "                   NULL::purpose)).*,\n",
    "       consented_for(provenance(), 'deal_consent', 'marketing')\n",
    "         AS for_marketing,\n",
    "       consent_conflicts(provenance(), 'deal_consent',\n",
    "                         NULL::purpose) AS conflicts\n",
    "FROM region r\n",
    "WHERE NOT EXISTS (SELECT * FROM deal d\n",
    "                  WHERE d.region = r.name AND d.amount >= 100)\n",
    "ORDER BY r.name;"
   ],
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two columns say which purposes may use each row:\n",
    "\n",
    "- `purposes`: the purposes for which the region has no big deal, when the query reads only the deals allowed for that purpose;\n",
    "- `unrestricted`: whether the region has no big deal when the query reads all the deals.\n",
    "\n",
    "Region by region:\n",
    "\n",
    "- South has no big deal at all, so every purpose may use it.\n",
    "- West has one, Sierra’s, and every purpose may read it: no purpose finds West free of big deals, so `purposes` is empty. West still has a row because it has no big deal in the worlds where Sierra does not close.\n",
    "- North has one too, Fjordline’s, but Fjordline allows forecasting only. Analytics and marketing do not see it, so North looks free of big deals to them, which is wrong.\n",
    "\n",
    "[`consented_for`](https://provsql.org/doxygen-sql/html/group__compiled__semirings.html#gaf45d5f0208404beb1ef86f4c7dad89e5) keeps a region for a purpose only if the purpose is in `purposes` and `unrestricted` is true, so the campaign gets South only. [`consent_conflicts`](https://provsql.org/doxygen-sql/html/group__compiled__semirings.html#gaa7b6409ac00b48bdd313936dfe06ff57) lists the purposes that would get a wrong answer: analytics and marketing, for North. [`consent_purposes`](https://provsql.org/doxygen-sql/html/group__compiled__semirings.html#gaea0292de8e5309fb31aad30fcce757c7) lists the purposes a row may be used for."
   ]
  }
 ]
}