Data Docs

Query Cookbook

Ready-made SQL for common workflows. All queries follow the conventions: versioned tables are filtered to is_current = TRUE, and year_rank = 1 selects each entity’s most recent year. Replace the example identifiers with your own.

Map my portfolio to the Net Purpose universe

WITH portfolio AS (
    SELECT column1 AS isin
    FROM VALUES ('US0378331005'), ('GB0007980591'), ('NL0000009165')
)
SELECT p.isin,
       e.entity_id,
       e.name,
       c.has_outcomes,
       c.has_sdi_revenue
FROM portfolio p
LEFT JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.primary_isin = p.isin AND e.is_current = TRUE
LEFT JOIN NET_PURPOSE.ENTITIES.ENTITIES_DATA_COVERAGE c
  ON c.entity_id = e.entity_id AND c.is_current = TRUE;

Rows with a NULL entity_id are outside the universe. From here on, keep the resolved entity_id list in a temporary table or CTE.

What data exists for my portfolio?

entities_data_coverage summarises, per entity, which datasets have something to offer before you query them:

SELECT e.name,
       c.has_outcomes,
       c.has_research,
       c.has_estimated_data,
       c.has_revenue,
       c.has_sdi_revenue
FROM NET_PURPOSE.ENTITIES.ENTITIES_DATA_COVERAGE c
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.entity_id = c.entity_id AND e.is_current = TRUE
WHERE c.is_current = TRUE
  AND c.entity_id IN (12345, 23456, 34567);

Latest value of one question for my portfolio

SELECT e.name,
       ad.year,
       ad.value,
       ad.data_type,
       ad.is_not_found
FROM NET_PURPOSE.OUTCOMES.ALL_DATA ad
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.entity_id = ad.entity_id AND e.is_current = TRUE
WHERE ad.is_current = TRUE
  AND ad.canonical_question_id = 'co2_total_1_2_and_3'
  AND ad.year_rank = 1
  AND ad.entity_id IN (12345, 23456, 34567);

Time series for one company and question

SELECT year, value, yoy_delta, yoy_perc, data_type
FROM NET_PURPOSE.OUTCOMES.ALL_DATA
WHERE is_current = TRUE
  AND entity_id = 12345
  AND canonical_question_id = 'co2_total_1_2_and_3'
ORDER BY year;

Disclosure rate: how many companies report a question?

all_data records researched-but-not-disclosed answers explicitly (is_not_found = TRUE), so disclosure rates are a single aggregation:

SELECT q.name,
       COUNT(*)                                          AS researched,
       COUNT_IF(NOT ad.is_not_found)                     AS disclosed,
       ROUND(100 * COUNT_IF(NOT ad.is_not_found)
                 / COUNT(*), 1)                          AS disclosure_pct
FROM NET_PURPOSE.OUTCOMES.ALL_DATA ad
JOIN NET_PURPOSE.REFERENCE.QUESTIONS q ON q.question_id = ad.question_id
WHERE ad.is_current = TRUE
  AND ad.year_rank = 1
GROUP BY q.name
ORDER BY disclosure_pct DESC;

Reported-only view (exclude estimates)

SELECT *
FROM NET_PURPOSE.OUTCOMES.ALL_DATA
WHERE is_current = TRUE
  AND data_origin_class_id = 1;   -- 1 = Reported, 2 = Derived, 3 = Estimated

How much of my data is estimated?

The same data_origin_class_id label supports a per-entity mix, useful for understanding how much of an analysis rests on modelled values:

SELECT e.name,
       COUNT(*)                                             AS datapoints,
       ROUND(100 * COUNT_IF(ad.data_origin_class_id = 1)
                 / COUNT(*), 1)                             AS reported_pct,
       ROUND(100 * COUNT_IF(ad.data_origin_class_id = 3)
                 / COUNT(*), 1)                             AS estimated_pct
FROM NET_PURPOSE.OUTCOMES.ALL_DATA ad
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.entity_id = ad.entity_id AND e.is_current = TRUE
WHERE ad.is_current = TRUE
  AND ad.year_rank = 1
  AND ad.is_not_found = FALSE
  AND ad.entity_id IN (12345, 23456, 34567)
GROUP BY e.name;

Benchmark a company against its sector

Rank every company in the target’s sector on one question and read off the percentile:

WITH sector_values AS (
    SELECT ad.entity_id,
           e.name,
           ad.value
    FROM NET_PURPOSE.OUTCOMES.ALL_DATA ad
    JOIN NET_PURPOSE.ENTITIES.ENTITIES e
      ON e.entity_id = ad.entity_id AND e.is_current = TRUE
    WHERE ad.is_current = TRUE
      AND ad.canonical_question_id = 'co2_total_1_2_and_3'
      AND ad.year_rank = 1
      AND ad.value IS NOT NULL
      AND e.sector = (
          SELECT sector FROM NET_PURPOSE.ENTITIES.ENTITIES
          WHERE entity_id = 12345 AND is_current = TRUE
      )
)
SELECT name,
       value,
       ROUND(100 * PERCENT_RANK() OVER (ORDER BY value), 1) AS percentile
FROM sector_values
QUALIFY entity_id = 12345;  -- remove to list the whole sector

The percentile is computed across the whole sector before the QUALIFY filter narrows the output to the company of interest.

Portfolio-weighted intensity

Combine holding weights with the pre-computed intensity columns on all_data (evic_per_million, revenue_per_million):

WITH holdings AS (
    SELECT column1 AS entity_id, column2 AS weight
    FROM VALUES (12345, 0.40), (23456, 0.35), (34567, 0.25)
)
SELECT SUM(h.weight * ad.evic_per_million) AS weighted_intensity_per_musd_evic
FROM NET_PURPOSE.OUTCOMES.ALL_DATA ad
JOIN holdings h ON h.entity_id = ad.entity_id
WHERE ad.is_current = TRUE
  AND ad.canonical_question_id = 'co2_total_1_2_and_3'
  AND ad.year_rank = 1
  AND ad.is_not_found = FALSE;

SDG-aligned revenue for a company

Join taxonomy revenue to each taxon’s primary SDG assignment in taxonomy_sdgs and roll up to goals:

SELECT ts.goal            AS sdg_goal,
       SUM(etr.revenue_amount) AS revenue_usd,
       SUM(etr.revenue_pct)    AS revenue_pct
FROM NET_PURPOSE_REVENUE.REVENUE.ENTITY_TAXONOMY_REVENUE etr
JOIN NET_PURPOSE.REFERENCE.TAXONOMY_SDGS ts
  ON ts.taxon_id = etr.taxon_id
 AND ts.is_primary = TRUE
WHERE etr.is_current = TRUE
  AND etr.entity_id = 12345
  AND etr.year_rank = 1
GROUP BY ts.goal
ORDER BY revenue_pct DESC;

Drop the is_primary filter to include secondary SDG contributions as well (a taxon can then count towards several goals).

Revenue exposure to one taxonomy branch, across a portfolio

The path column on taxonomy makes it easy to select a whole branch of the tree — a theme and everything underneath it:

SELECT e.name,
       etr.year,
       SUM(etr.revenue_amount) AS branch_revenue_usd,
       SUM(etr.revenue_pct)    AS branch_revenue_pct
FROM NET_PURPOSE_REVENUE.REVENUE.ENTITY_TAXONOMY_REVENUE etr
JOIN NET_PURPOSE.REFERENCE.TAXONOMY t
  ON t.taxon_id = etr.taxon_id
 AND t.path LIKE (SELECT path FROM NET_PURPOSE.REFERENCE.TAXONOMY WHERE taxon_id = 42) || '%'
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.entity_id = etr.entity_id AND e.is_current = TRUE
WHERE etr.is_current = TRUE
  AND etr.year_rank = 1
  AND etr.entity_id IN (12345, 23456, 34567)
GROUP BY e.name, etr.year
ORDER BY branch_revenue_pct DESC;

SDI screen for a portfolio

SELECT e.name,
       e.primary_isin,
       sc.year,
       sc.sdi_classification,
       sc.is_sdi_decisive
FROM NET_PURPOSE_REVENUE.REVENUE.SDI_CLASSIFICATIONS sc
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.entity_id = sc.entity_id AND e.is_current = TRUE
WHERE sc.is_current = TRUE
  AND sc.entity_id IN (12345, 23456, 34567)
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY sc.entity_id ORDER BY sc.year DESC
) = 1;

Audit a number back to its source document

Start from any all_data row and follow the origins:

SELECT ad.canonical_question_id,
       ad.year,
       ad.value,
       sf.recorded_value,
       src.name       AS source_document,
       src.url,
       sf.page_num
FROM NET_PURPOSE.OUTCOMES.ALL_DATA ad
JOIN NET_PURPOSE.OUTCOMES.ALL_DATA_ORIGINS ao
  ON ao.all_data_id = ad.all_data_id AND ao.is_current = TRUE
JOIN NET_PURPOSE.FOUNDATIONS.OUTCOMES_FACTS f
  ON f.fact_id = ao.fact_id AND f.is_current = TRUE
JOIN NET_PURPOSE.FOUNDATIONS.SUPPORTING_FACTS sf
  ON sf.fact_id = f.fact_id AND sf.is_current = TRUE
LEFT JOIN NET_PURPOSE.REFERENCE.SOURCES src
  ON src.source_id = sf.source_id
WHERE ad.is_current = TRUE
  AND ad.entity_id = 12345
  AND ad.canonical_question_id = 'co2e_avoided'
  AND ad.year = 2024;

For calculated values, follow ao.kpi_result_id into kpi_results and kpi_calculation_trees instead; for estimated values, query estimation_lifecycle for the same entity, taxon and year.

Unpack a calculated KPI into its formula and inputs

SELECT kr.canonical_question_id,
       kr.year,
       kr.value,
       kr.unit,
       kf.formula,
       kf.formula_description,
       ct.calculation_tree
FROM NET_PURPOSE.FOUNDATIONS.KPI_RESULTS kr
JOIN NET_PURPOSE.REFERENCE.KPI_FORMULAE kf
  ON kf.question_id = kr.question_id
JOIN NET_PURPOSE.FOUNDATIONS.KPI_CALCULATION_TREES ct
  ON ct.kpi_result_id = kr.kpi_result_id AND ct.is_current = TRUE
WHERE kr.is_current = TRUE
  AND kr.entity_id = 12345
  AND kr.canonical_question_id = 'co2_total_1_2_and_3'
ORDER BY kr.year;

Has this number been restated?

Because tables are append-only, the full version history of a value is one query away — just drop the is_current filter:

SELECT year,
       value,
       data_type,
       valid_from,
       valid_to,
       is_current
FROM NET_PURPOSE.OUTCOMES.ALL_DATA
WHERE entity_id = 12345
  AND canonical_question_id = 'co2_total_1_2_and_3'
  AND year = 2023
ORDER BY valid_from;

More than one row means the value changed after first publication — for example a company restatement or a research revision.

How fresh is the data for my portfolio?

SELECT e.name,
       MAX(ad.year)        AS latest_year,
       MAX(ad.end_date)    AS latest_period_end
FROM NET_PURPOSE.OUTCOMES.ALL_DATA ad
JOIN NET_PURPOSE.ENTITIES.ENTITIES e
  ON e.entity_id = ad.entity_id AND e.is_current = TRUE
WHERE ad.is_current = TRUE
  AND ad.is_not_found = FALSE
  AND ad.entity_id IN (12345, 23456, 34567)
GROUP BY e.name
ORDER BY latest_year;

What did the data look like on a past date?

Replace the is_current filter with a validity window (see Conventions):

SELECT year, value
FROM NET_PURPOSE.OUTCOMES.ALL_DATA
WHERE entity_id = 12345
  AND canonical_question_id = 'co2_total_1_2_and_3'
  AND valid_from <= '2025-06-30'::TIMESTAMP_TZ
  AND valid_to    >  '2025-06-30'::TIMESTAMP_TZ
ORDER BY year;