.. nb:name: cs2
.. nb:database: cs2

Case Study: Open Science Database
=================================

This case study introduces a broader set of ProvSQL features through a
realistic scientific literature analysis scenario.

The Scenario
------------

.. warning::

   All studies, findings, and reliability scores in this case study are
   **entirely fictional** and created solely to illustrate ProvSQL features.
   They do not correspond to real published research and convey no medical
   or scientific knowledge.

You are building an evidence-synthesis tool for biomedical research. You have
a small database of published studies, each with a study type
(case report, observational, RCT, or meta-analysis) and a reliability score.
Each study reports one or more *findings*: an exposure (e.g., Coffee, Exercise),
an outcome (e.g., Cardiovascular Disease), and an observed effect (beneficial,
harmful, or neutral).

Your tasks:

* identify single-source vs. replicated claims,
* detect and handle contradictory findings,
* rank findings by the strength of evidence behind them,
* compute the probability that a claim is supported by the available studies.


Setup
-----

.. nb:skip
.. tip::

   **Prefer not to install? Use the Playground.** You can skip the manual
   setup below: open this case study `as a runnable notebook in the ProvSQL
   Playground <https://provsql.org/playground/?nb=cs2>`_ (every query is a
   cell, and the opening cells set up the database for you). If you would
   rather keep reading this page, open the bare `cs2 database
   <https://provsql.org/playground/?db=cs2>`_ instead and run the queries as
   you read (the notebook shows the exercise solutions inline, while this
   page keeps them folded). See the :ref:`Playground note
   <playground-note>`.

.. nb:omit-begin

This case study assumes a working ProvSQL installation (see
:doc:`getting-provsql`). Download :download:`setup.sql <../../casestudy2/setup.sql>`
and load it into a fresh PostgreSQL database:

.. code-block:: bash

    psql -d mydb -f setup.sql

The setup script calls :sqlfunc:`setup_search_path`, which adds
``provsql`` to the database's ``search_path``: every new session calls
ProvSQL's functions without the ``provsql.`` prefix.

.. nb:omit-end

.. nb:setup: ../../casestudy2/setup.sql

This creates four tables:

* ``study`` -- 8 published studies with type and reliability score
* ``exposure`` -- 7 exposures (Coffee, Exercise, Red Meat…)
* ``outcome`` -- 5 health outcomes (Cardiovascular Disease…)
* ``finding`` -- 25 study findings linking exposures to outcomes

Step 1: Explore the Database
-----------------------------

Familiarise yourself with the data. The ``study_type`` column uses a PostgreSQL
``ENUM`` ordered by evidence quality:
``no_evidence < case_report < observational < rct < meta_analysis < perfect_evidence``,
where ``no_evidence`` is the semiring 𝟘 (no derivation possible) and
``perfect_evidence`` is the semiring 𝟙 (neutral for ⊗ = MIN).

Step 2: Enable Provenance and Join with Lookup Tables
------------------------------------------------------

Enable provenance tracking on ``finding``, the base fact table. Each row
in ``finding`` receives a unique UUID token, carried through any
downstream query.

.. code-block:: postgresql

    SELECT add_provenance('finding');

Now build a view ``f`` by joining ``finding`` with the three
lookup tables. Every row in ``f`` carries the provenance token of the
``finding`` row it came from. Define ``f`` using the following
columns: ``study`` (the study title), ``study_type``, ``reliability``,
``exposure`` (the exposure name), ``outcome`` (the outcome name),
and ``effect``.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    CREATE OR REPLACE VIEW f AS
      SELECT study.title    AS study,
             study.study_type,
             study.reliability,
             exposure.name  AS exposure,
             outcome.name   AS outcome,
             finding.effect
      FROM finding
        JOIN study    ON finding.study_id    = study.id
        JOIN exposure ON finding.exposure_id = exposure.id
        JOIN outcome  ON finding.outcome_id  = outcome.id;

.. raw:: html

   </details>

.. note::

   A query on a view over a provenance-enabled table carries that table's
   provenance: any query on ``f`` has full provenance, although provenance
   was never added to ``f`` itself.

Step 3: Create a Provenance Mapping
-------------------------------------

Create a mapping from provenance tokens (which trace back to ``finding`` rows)
to study titles, using the ``study`` column of ``f``:

.. code-block:: postgresql

    DROP TABLE IF EXISTS study_mapping;
    SELECT create_provenance_mapping('study_mapping', 'f', 'study');

Step 4: Identify Single-Source Claims
---------------------------------------

Some claims rest on a single study. Use :sqlfunc:`sr_formula` with
``study_mapping`` to display the symbolic provenance formula for a few
findings of interest: the (Coffee, Cardiovascular Disease, harmful),
(Alcohol, Cardiovascular Disease, beneficial), and
(Exercise, Inflammation, beneficial) triples.

.. note::

   Use ``GROUP BY`` (not ``SELECT DISTINCT``) when applying a semiring
   evaluation function. With ``SELECT DISTINCT``, the computed formula
   becomes part of the distinct key, so rows with the same
   (exposure, outcome, effect) but different single-study formulas are
   never collapsed. With ``GROUP BY``, ProvSQL ⊕-combines all provenance
   tokens in the group, and :sqlfunc:`sr_formula` applies to the combined
   token.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SELECT exposure, outcome, effect,
        sr_formula(provenance(), 'study_mapping') AS formula
    FROM f
    WHERE (exposure = 'Coffee'   AND outcome = 'Cardiovascular Disease' AND effect = 'harmful')
       OR (exposure = 'Alcohol'  AND outcome = 'Cardiovascular Disease' AND effect = 'beneficial')
       OR (exposure = 'Exercise' AND outcome = 'Inflammation'           AND effect = 'beneficial')
    GROUP BY exposure, outcome, effect
    ORDER BY exposure, outcome, effect;

.. raw:: html

   </details>

Observe that each formula is just a single study name: these findings have
only one source.

Step 5: Why- and Which-Provenance for Replicated Findings
---------------------------------------------------------

For replicated findings, the *why-provenance* returns a set of *witness
sets*. Each witness set is a collection of studies that together (⊗)
suffice to derive the finding; the outer set collects all such alternatives
(⊕). Why-provenance does not keep only the minimal ones: ⊕ is a plain union,
and a witness set that contains another one stays. Use :sqlfunc:`sr_why`, and
:sqlfunc:`sr_which` for comparison, on ``f`` with ``GROUP BY`` for the
(Exercise, Cardiovascular Disease, beneficial) and (Aspirin, Cardiovascular
Disease, beneficial) pairs.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SELECT exposure, outcome, effect,
        sr_why(provenance(), 'study_mapping') AS witnesses,
        sr_which(provenance(), 'study_mapping') AS lineage
    FROM f
    WHERE (exposure = 'Exercise' AND outcome = 'Cardiovascular Disease')
       OR (exposure = 'Aspirin'  AND outcome = 'Cardiovascular Disease')
    GROUP BY exposure, outcome, effect
    ORDER BY exposure, outcome, effect;

.. raw:: html

   </details>

Each inner set in ``witnesses`` is a group of studies that together (⊗)
derive the finding; the multiple inner sets are alternatives (⊕): any one of
them alone suffices. Here each holds a single study, so they look minimal;
that is a property of this query, in which each derivation reads one study,
not of why-provenance.

The *which-provenance* of :sqlfunc:`sr_which`, in ``lineage``, flattens
them into the single set of studies the finding draws on, here
``{Smith2018,Williams2021}`` for Aspirin: it lists the studies to check,
but not that either one alone suffices.

Step 6: Evidence Grade Semiring
--------------------------------

Define a custom *evidence grade* semiring over ``study_quality``
(see :ref:`the section on custom semirings <custom-semirings>` for a
full description of the mechanism):

* **⊕ = MAX** (best quality among alternative derivations)
* **⊗ = MIN** (weakest quality in a chain of derivations)

This answers: *"What is the best study type supporting this finding?"*

Implement this semiring using PostgreSQL aggregate functions and
:sqlfunc:`provenance_evaluate`, create a ``quality_mapping`` from ``f``
over the ``study_type`` column, and compute the evidence grade for every
(exposure, outcome, effect) triple.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    CREATE OR REPLACE FUNCTION quality_plus_state(state study_quality, q study_quality)
      RETURNS study_quality AS $$
        SELECT GREATEST(state, q)
    $$ LANGUAGE SQL IMMUTABLE;

    CREATE OR REPLACE FUNCTION quality_times_state(state study_quality, q study_quality)
      RETURNS study_quality AS $$
        SELECT LEAST(state, q)
    $$ LANGUAGE SQL IMMUTABLE;

    DROP AGGREGATE IF EXISTS quality_plus(study_quality);
    CREATE AGGREGATE quality_plus(study_quality) (
      sfunc = quality_plus_state, stype = study_quality, initcond = 'no_evidence'
    );
    DROP AGGREGATE IF EXISTS quality_times(study_quality);
    CREATE AGGREGATE quality_times(study_quality) (
      sfunc = quality_times_state, stype = study_quality, initcond = 'perfect_evidence'
    );

    CREATE OR REPLACE FUNCTION evidence_grade(token UUID, token2value regclass)
      RETURNS study_quality AS $$
    BEGIN
      RETURN provenance_evaluate(
        token, token2value,
        'perfect_evidence'::study_quality,
        'quality_plus', 'quality_times'
      );
    END
    $$ LANGUAGE plpgsql;

    DROP TABLE IF EXISTS quality_mapping;
    SELECT create_provenance_mapping('quality_mapping', 'f', 'study_type');

    SELECT exposure, outcome, effect,
        evidence_grade(provenance(), 'quality_mapping') AS grade
    FROM f
    GROUP BY exposure, outcome, effect
    ORDER BY exposure, outcome, effect;

.. raw:: html

   </details>

.. note::

   As in Step 4, use ``GROUP BY``, not ``SELECT DISTINCT``: ``GROUP BY``
   collapses each group into a single provenance token (via ⊕), whereas
   ``SELECT DISTINCT`` includes the computed value in the distinct key and
   produces spurious duplicates.

Step 7: Where-Provenance
--------------------------

Where-provenance tracks which *column* of which *table* each value in a
result came from. Enable it and query ``f`` for the Smith2018/Exercise/CVD
finding:

.. code-block:: postgresql

    SET provsql.provenance = 'where';

    SELECT study, study_type, exposure, outcome, effect,
        where_provenance(provenance()) AS source
    FROM f
    WHERE exposure = 'Exercise' AND outcome = 'Cardiovascular Disease'
      AND study = 'Smith2018';

    SET provsql.provenance = 'semiring';

Each entry in ``source`` takes the form ``[table:token:column]``, where
``token`` is the provenance UUID of the source row and ``column`` is its
position in the table. Only ``effect`` is tracked, appearing as
``[finding:〈token〉:5]``; the remaining columns (``study``, ``study_type``,
``exposure``, ``outcome``) appear as empty ``[]``: they originate from
``study``, ``exposure``, and ``outcome`` tables that have no provenance
enabled.

Step 8: Where-Provenance on the Base Table
--------------------------------------------

To see full column-level tracking, query ``finding`` directly. Selecting
``finding``-column references ensures every output value originates from the
provenance-enabled table.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SET provsql.provenance = 'where';

    SELECT finding.study_id, finding.exposure_id, finding.outcome_id, finding.effect,
        where_provenance(provenance()) AS source
    FROM finding
    JOIN study    ON finding.study_id    = study.id    AND study.title    = 'Smith2018'
    JOIN exposure ON finding.exposure_id = exposure.id AND exposure.name  = 'Exercise'
    JOIN outcome  ON finding.outcome_id  = outcome.id  AND outcome.name   = 'Cardiovascular Disease'
    WHERE finding.effect = 'beneficial';

    SET provsql.provenance = 'semiring';

.. raw:: html

   </details>

Now every output column traces back to its source column in ``finding``:
``study_id`` → ``[finding:〈token〉:2]``, ``exposure_id`` → ``[finding:〈token〉:3]``,
``outcome_id`` → ``[finding:〈token〉:4]``, ``effect`` → ``[finding:〈token〉:5]``.
The trailing ``[]`` is the untracked ``source`` column itself.

Step 9: Assign Probabilities
------------------------------

Assign each row of ``f`` its study's reliability score as a probability:

.. code-block:: postgresql

    SELECT set_prob(provenance(), reliability) FROM f;

Now compute the probability that at least one study supports each of the
following three findings: (Exercise, Cardiovascular Disease, beneficial),
(Coffee, Cardiovascular Disease, harmful), and
(Aspirin, Cognitive Decline, beneficial).

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SELECT exposure, outcome, effect,
        ROUND(probability_evaluate(provenance())::numeric, 4) AS prob
    FROM f
    WHERE (exposure = 'Exercise' AND outcome = 'Cardiovascular Disease' AND effect = 'beneficial')
       OR (exposure = 'Coffee'   AND outcome = 'Cardiovascular Disease' AND effect = 'harmful')
       OR (exposure = 'Aspirin'  AND outcome = 'Cognitive Decline'      AND effect = 'beneficial')
    GROUP BY exposure, outcome, effect
    ORDER BY exposure, outcome, effect;

.. raw:: html

   </details>

Exercise→CVD→beneficial achieves 0.9998 (three independent studies with high
reliability). Aspirin→Cognitive Decline→beneficial scores only 0.8500 (one
study with reliability 0.85).

Step 10: Build the Replication View
-------------------------------------

A finding is considered *replicated* if at least two independent studies
report it. Define the ``f_replicated`` view, which groups findings by
(exposure, outcome, effect) and applies the replication threshold via
``HAVING COUNT(*) >= 2``.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    CREATE OR REPLACE VIEW f_replicated AS
    SELECT exposure, outcome, effect FROM f
    GROUP BY exposure, outcome, effect
    HAVING COUNT(*) >= 2;

.. raw:: html

   </details>

.. note::

   With ProvSQL, whether a group passes the ``HAVING`` threshold depends
   on which of its rows are present, and the group's provenance token
   records this. A group may therefore appear in the output with a
   provenance that evaluates to the semiring zero ``𝟘`` wherever the
   threshold fails. A group that passes in no world at all -- here, a
   finding backed by a single study -- is either left out of the output
   or appears with a provenance evaluating to ``𝟘``. The two are
   equivalent: a row whose provenance is ``𝟘`` is the same as an absent
   row.

Step 11: Inspect Replication with :sqlfunc:`sr_counting`
---------------------------------------------------------

To inspect the provenance semantics of ``f_replicated``, first add an
integer column ``cnt`` to ``finding`` (all values ``1``) and create a
``count_mapping``:

.. code-block:: postgresql

    ALTER TABLE finding ADD COLUMN IF NOT EXISTS cnt int DEFAULT 1;
    DROP TABLE IF EXISTS count_mapping;
    SELECT create_provenance_mapping('count_mapping', 'finding', 'cnt');

Now query ``f_replicated`` using :sqlfunc:`sr_counting` to display what
the counting semiring gives each (exposure, outcome, effect) triple of the
view. It is not the number of ways the triple is derived: through the
``HAVING`` threshold, a group counts once if it passes it, whatever its
number of rows.

.. note::

   :sqlfunc:`sr_counting` evaluates the provenance circuit in the counting
   semiring, giving each base finding its value from ``count_mapping``
   (``1`` per row). It is unrelated to the SQL ``COUNT(*)`` aggregate,
   which drives the ``HAVING`` threshold.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SELECT exposure, outcome, effect,
        sr_counting(provenance(), 'count_mapping') AS replicated
    FROM f_replicated
    ORDER BY exposure, outcome, effect;

.. raw:: html

   </details>

Observe that single-study findings (Aspirin→Cognitive Decline, etc.) are
absent from the view, as explained in the note of Step 10, while findings
supported by two or more studies receive ``1``: the threshold holds, in
one way.

Step 12: Probability of Replication
-------------------------------------

Now use ``f_replicated`` to compute, for each (exposure, outcome, effect)
triple, the probability that the finding is *replicated*, i.e., supported by
at least two independent studies.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SELECT exposure, outcome, effect,
        ROUND(probability_evaluate(provenance())::numeric, 4) AS prob
    FROM f_replicated
    ORDER BY exposure, outcome, effect;

.. raw:: html

   </details>

Single-study findings (Aspirin→Cognitive Decline, etc.) are not listed:
their probability of being replicated is 0.
Exercise→CVD→beneficial drops from 0.9998 to 0.9868, reflecting that now
*at least two* of the three studies must agree.

.. _step-13-shapley:

Step 13: Shapley Values
------------------------

Shapley values measure each study's marginal contribution to the *replication*
probability of a finding. Because probabilities are set on the input tuples,
:sqlfunc:`shapley` computes *expected* Shapley values in the probabilistic
sense (see :doc:`the chapter on Shapley values <shapley>`). Expected
Shapley values sum to the probability of the query result, here the
replication probability computed in Step 12.

Compute expected Shapley values for Exercise→CVD→beneficial, using
``f_replicated`` as the target and individual ``f`` rows as the variables.

.. hint::

   :sqlfunc:`shapley` takes two provenance tokens: the combined token of
   the query result (the "target", from ``f_replicated``) and the individual
   token of each input row (the "variable", from ``f``).
   Capture both in subqueries and cross-join them.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SELECT fin.study,
           ROUND(shapley(target.prov, fin.prov)::numeric, 4) AS sv
    FROM (
      SELECT provenance() AS prov
      FROM f_replicated
      WHERE exposure = 'Exercise' AND outcome = 'Cardiovascular Disease'
        AND effect = 'beneficial'
    ) target,
    (
      SELECT study, provenance() AS prov
      FROM f
      WHERE exposure = 'Exercise' AND outcome = 'Cardiovascular Disease'
        AND effect = 'beneficial'
    ) fin
    ORDER BY sv DESC, study;

.. raw:: html

   </details>

Johnson2020 (meta-analysis, reliability 0.98) has the highest Shapley value
(0.3531), reflecting its dominant contribution to the replication probability.
Smith2018 (RCT, 0.92) scores 0.3267 and Williams2021 (RCT, 0.88) scores 0.3071.
The three values sum to 0.9869, matching (up to rounding) the replication
probability 0.9868 obtained in Step 12, as guaranteed by the efficiency axiom
for expected Shapley values.

Step 14: Banzhaf Values
------------------------

*Expected Banzhaf values* offer an alternative game-theoretic measure of each
study's contribution. Unlike Shapley values, which average marginal
contributions over all orderings of the players, Banzhaf values add them up
over all *subsets* of the other players, without dividing by their number.
This makes the computation simpler (no ordering weights) and often faster in
practice. Like Shapley values, they are
computed in the probabilistic sense (see :doc:`the chapter on Shapley
values <shapley>`), but they do not
satisfy the efficiency axiom: they are not constrained to sum to the
probability of the query result.

.. hint::

   :sqlfunc:`banzhaf` takes the same two arguments as :sqlfunc:`shapley`: the
   combined provenance token of the query result (the "target", from
   ``f_replicated``) and the individual provenance token of each input row
   (the "variable", from ``f``). Reuse the same cross-join structure from
   :ref:`Step 13 <step-13-shapley>`.

Compute Banzhaf values for Exercise→CVD→beneficial and compare them with the
Shapley values from Step 13.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    SELECT fin.study,
           ROUND(banzhaf(target.prov, fin.prov)::numeric, 4) AS bv
    FROM (
      SELECT provenance() AS prov
      FROM f_replicated
      WHERE exposure = 'Exercise' AND outcome = 'Cardiovascular Disease'
        AND effect = 'beneficial'
    ) target,
    (
      SELECT study, provenance() AS prov
      FROM f
      WHERE exposure = 'Exercise' AND outcome = 'Cardiovascular Disease'
        AND effect = 'beneficial'
    ) fin
    ORDER BY bv DESC, study;

.. raw:: html

   </details>

The relative ranking of Johnson2020, Smith2018, and Williams2021 is the same as
with Shapley values, confirming that both measures agree on who contributes most
to the replication probability. The absolute magnitudes are larger (around 1.7
per study, a sum over subsets rather than an average) and their sum does not
equal the replication probability.

Step 15: Bulk Shapley/Banzhaf with ``shapley_all_vars`` and ``banzhaf_all_vars``
---------------------------------------------------------------------------------

The cross-join pattern from :ref:`Step 13 <step-13-shapley>` is convenient when
you only want a value for a few specific variables, but is inefficient when
many input variables are involved. :sqlfunc:`shapley_all_vars` and
:sqlfunc:`banzhaf_all_vars` take a single target token and return one row per
input variable.

.. raw:: html

   <details>
   <summary>Solution</summary>

.. code-block:: postgresql

    CREATE TEMP TABLE target_token AS
    SELECT provenance() AS prov
    FROM f_replicated
    WHERE exposure = 'Exercise' AND outcome = 'Cardiovascular Disease'
      AND effect = 'beneficial';

    SELECT remove_provenance('target_token');

    SELECT sm.value AS study,
           ROUND(sav.value::numeric, 4) AS sv
    FROM target_token, shapley_all_vars(prov) sav
    JOIN study_mapping sm ON sm.provenance = sav.variable
    ORDER BY sv DESC, study;

.. raw:: html

   </details>

The output is identical to Step 13: Johnson2020, Smith2018, and Williams2021
with the same Shapley values. The difference is in the calling convention
(no enumeration of variables) and in efficiency, especially when there are
many input variables. Replace ``shapley_all_vars`` with
``banzhaf_all_vars`` to reproduce Step 14 in the same way.

.. note::

   ``shapley_all_vars`` returns one row per *input variable* of the circuit,
   keyed by its UUID token. The ``study_mapping`` join maps these tokens
   back to study titles for display.

Seeing it in Studio: Contributions mode
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The same bulk computation is one click away in :ref:`ProvSQL Studio's
Contributions mode <studio-contributions-mode>`. Run the replication
query for the finding, set :guilabel:`Labels` to ``study_mapping``, and
click the result row's ``provsql`` cell: Studio computes
``shapley_all_vars`` for that tuple and draws one ranked bar per study,
the interactive twin of Steps 13–15.

.. code-block:: postgresql

    SELECT exposure, outcome, effect FROM f_replicated
     WHERE exposure = 'Exercise' AND outcome = 'Cardiovascular Disease'
       AND effect = 'beneficial';

.. figure:: /_static/studio/contributions-mode.png
   :alt: Studio Contributions mode showing per-study Shapley bars for the
         Exercise to Cardiovascular Disease beneficial finding.

   Contributions mode: Johnson2020, Smith2018, and Williams2021 ranked
   by Shapley value -- the same numbers as Step 13, read off a heat-map.
   The :guilabel:`Measure` picker switches to Banzhaf (Step 14).

Step 16: Arithmetic on Aggregate Results
------------------------------------------

SQL aggregates such as ``COUNT`` and ``SUM`` return *aggregate tokens*
(``agg_token``) that keep the provenance of their inputs. Arithmetic
(``*``, ``+``…) on them returns an aggregate token as well, combining
the provenance of its operands.

Compute a composite *evidence weight* per (exposure, outcome, effect) triple
combining how many studies report it (``COUNT(*)``) with the highest
reliability among them (``MAX(reliability)``):

.. code-block:: postgresql

    SELECT exposure, outcome, effect,
           COUNT(*)           AS n_studies,
           MAX(reliability)   AS top_reliability,
           COUNT(*) * MAX(reliability) AS evidence_weight,
           plain(COUNT(*) * MAX(reliability)) AS evidence_weight_value
    FROM f
    GROUP BY exposure, outcome, effect
    ORDER BY evidence_weight_value DESC, exposure, outcome, effect;

.. note::

   ``evidence_weight`` is an aggregate token, which can be inspected and
   evaluated like any provenance circuit; a cast to ``NUMERIC`` keeps it
   one. To get its *value* as a plain number, wrap it in :sqlfunc:`plain`
   as in ``evidence_weight_value``: the value in the database as it is,
   as ordinary SQL data (usable in ``ORDER BY``, comparisons, further
   computation). The group provenance of each (exposure, outcome, effect)
   triple is unaffected and remains available for
   :sqlfunc:`probability_evaluate`, :sqlfunc:`shapley`, etc.


Step 17: Richer Aggregates -- ``DISTINCT``, ``string_agg``, ``FILTER``
----------------------------------------------------------------------

Provenance tracks through the full range of SQL aggregates, not just
``COUNT(*)``. For each (exposure, outcome) pair, count the studies and
their *distinct* designs, list the studies, and count how many of the
findings are beneficial:

.. code-block:: postgresql

    SELECT exposure, outcome,
           COUNT(*) AS n_studies,
           COUNT(DISTINCT study_type) AS n_designs,
           string_agg(study, ', ' ORDER BY study) AS studies,
           COUNT(*) FILTER (WHERE effect = 'beneficial') AS n_beneficial
    FROM f
    WHERE exposure IN ('Coffee', 'Exercise')
    GROUP BY exposure, outcome
    ORDER BY exposure, outcome;

Two RCTs back Exercise→CVD, so its three studies have two designs. Each
aggregate value comes back as an ``agg_token`` (shown as ``value (*)``):
the counts, the concatenated list, and the filtered count all carry the provenance of the findings they summarise, and the
group's ``provenance()`` still feeds :sqlfunc:`probability_evaluate` or
:sqlfunc:`sr_formula` as in the earlier steps:

.. code-block:: text

    exposure | outcome                | n_studies | n_designs | studies                                  | n_beneficial
    ---------+------------------------+-----------+-----------+------------------------------------------+-------------
    Coffee   | Cardiovascular Disease | 3 (*)     | 2 (*)     | Brown2022, Chen2019, Garcia2017 (*)      | 1 (*)
    Coffee   | Cognitive Decline      | 2 (*)     | 2 (*)     | Brown2022, Park2021 (*)                  | 2 (*)
    Exercise | Cardiovascular Disease | 3 (*)     | 2 (*)     | Johnson2020, Smith2018, Williams2021 (*) | 3 (*)
    Exercise | Inflammation           | 1 (*)     | 1 (*)     | Smith2018 (*)                            | 1 (*)