Build a sector dataset in DuckDB from filings and your own notes

-- Stop at the first error instead of running the rest of the script.
.bail on
-- Don't print the results of the setup statements.
.mode trash

-- Stop with a clear message when the API key isn't set.
SELECT CASE WHEN coalesce(getenv('DATAVILLE_API_KEY'), '') = ''
    THEN error('Set DATAVILLE_API_KEY to your Dataville API key first.')
END;

-- Send your API key with every request to Dataville. It comes from the
-- DATAVILLE_API_KEY environment variable, so it never appears in a URL.
CREATE SECRET dataville (
    TYPE http,
    BEARER_TOKEN getenv('DATAVILLE_API_KEY'),
    SCOPE 'https://api.dataville.com'
);

-- Fetch each file in one request instead of reading it in parts. Every
-- request counts toward your limits, and the edgar export is small enough
-- to fetch whole.
SET force_download = true;

-- Look up each ticker in sector.csv in the edgar source. Each lookup returns
-- the company's CIK and adds its recent filings to Dataville's edgar export.
SET VARIABLE lookup_urls = (
    SELECT list('https://api.dataville.com/edgar/' || ticker)
    FROM read_csv('sector.csv')
);
CREATE TABLE companies AS
SELECT
    regexp_extract(filename, '[^/]+$') AS ticker,
    data.metadata.cik AS cik,
    data.metadata.company_name AS company_name
FROM read_json(getvariable('lookup_urls'), filename = true, union_by_name = true);

-- Match each company to its filings in the edgar export by CIK, and keep the
-- filings from the last 12 months.
CREATE TABLE sector_filings AS
SELECT
    sector.ticker,
    sector.segment,
    companies.company_name,
    filings.form_type,
    filings.filing_date,
    -- An 8-K's items say what it reports, such as 2.03 for new debt.
    nullif(regexp_extract(filings.body, 'Items: ([^\n]*)', 1), '') AS items,
    filings.document_url
FROM read_csv('sector.csv') AS sector
JOIN companies ON companies.ticker = sector.ticker
JOIN read_parquet('https://api.dataville.com/api/v1/export/edgar?format=parquet') AS filings
    ON filings.cik = companies.cik
-- filing_date is text, so cast it to compare dates.
WHERE CAST(filings.filing_date AS DATE) >= current_date - INTERVAL 1 YEAR
ORDER BY sector.ticker, filings.filing_date DESC;

-- Save the dataset for your report.
COPY sector_filings TO 'sector-filings.csv' (HEADER);

-- Print how many filings each company has in the dataset.
.mode duckbox
SELECT ticker, count(*) AS filings
FROM sector_filings
GROUP BY ticker
ORDER BY ticker;