Sources

`sources`

Our library of source documents. This includes the public filings from which reported data is extracted, the scientific literature used to support lifecycle estimation, and proprietary Net Purpose research reports.

Column	Type	Description
`source_id`	NUMBER	Primary key
`checksum`	VARCHAR	Hash computed for the source document. Used as the stem for the file name. Unique hashes indicate unique documents.
`entity_id`	NUMBER	The entity the document belongs to
`name`	VARCHAR	Document title
`url`	VARCHAR	Where the document can be retrieved from
`original_url`	VARCHAR	Where the document was originally found
`report_type`	VARCHAR	Kind of document, e.g. annual report, sustainability report
`file_type`	VARCHAR	File format, e.g. `pdf`
`start`	DATE	Start date of the period the document covers
`end`	DATE	End date of the period the document covers
`publishing_date`	DATE	When the document was published

-- Documents evidence was collected from for an entity, newest first
SELECT DISTINCT s.source_id, s.name, s.report_type, s."end" AS period_end, s.url
FROM NET_PURPOSE.REFERENCE.SOURCES s
JOIN NET_PURPOSE.FOUNDATIONS.SUPPORTING_FACTS sf
    ON sf.source_id = s.source_id
   AND sf.is_current = TRUE
WHERE s.entity_id = 12345
ORDER BY period_end DESC;

-- All research write-ups and their download links
SELECT source_id, checksum, name, url
FROM NET_PURPOSE.REFERENCE.SOURCES
WHERE report_type = 'Write Up';

-- Documents shared by multiple entities (same checksum = same file)
SELECT checksum, ARRAY_AGG(entity_id) AS entities
FROM NET_PURPOSE.REFERENCE.SOURCES
GROUP BY checksum
HAVING COUNT(entity_id) > 1;