SQL Cookbook

DuckDB + Parquet recipes for Valuein

7 query patterns for the questions analysts and quants ask first. Each one runs as written on the free sample, through the Python SDK or in plain DuckDB. Change the ticker and run.

Start with references Latest value per company Pivot concepts QUALIFY ROW_NUMBER()Quarter-only cash flow Point-in-time screen Defensive ratio math

Before you start

The recipes use table names (fact, "references", …). With the Python SDK those tables are already views, so a recipe runs unchanged in client.run_query(). In plain DuckDB, download the files from the Bulk Data API and create one view per table.

with_sdk.pypython

from valuein_sdk import ValueinClient sql = """SELECT symbol, name FROM "references" WHERE is_active LIMIT 5;"""  # or any recipe below with ValueinClient() as client:  # no key: the free sample    print(client.run_query(sql))  # every table is already a view: no setup

setup.sqlsql

1-- Plain DuckDB: download each table once. The sample tier needs no token:2--   curl -o fact.parquet https://data.valuein.biz/v1/sample/fact3-- then give each file a view named after its table, so the recipes run as written.4CREATE VIEW fact             AS SELECT * FROM read_parquet('fact.parquet');5CREATE VIEW "references"     AS SELECT * FROM read_parquet('references.parquet');6CREATE VIEW index_membership AS SELECT * FROM read_parquet('index_membership.parquet');

Start every cross-company query with `references`

`references` is entity joined to security: one row per listing with `cik`, `symbol`, `name`, `sector`. Filter `is_active AND is_primary_ticker` for one row per company today.

Why this works ↓

`references` saves you joining `entity` and `security` yourself. A company can have several rows (share classes, retired tickers), so `is_active` keeps tickers in use today and `is_primary_ticker` keeps one per company. Index membership lives in `index_membership`, keyed on `cik`; there is no `is_sp500` flag. `references` is a reserved word in DuckDB, so always quote it.

sql

1SELECT2  r.symbol,3  r.name,4  r.sector,5  r.industry6FROM "references" r7JOIN index_membership im8  ON im.cik = r.cik9WHERE im.index_name = 'SP500'10  AND im.removal_date IS NULL  -- current member11  AND r.is_active               -- a ticker in use today12  AND r.is_primary_ticker       -- one row per company13ORDER BY r.sector, r.symbol;

Note · Use this as the spine of any screen, peer search or universe.

Latest annual value per company with LATERAL

A LATERAL subquery picks one row per company. `fact` keys on `entity_id`, which equals `references.cik`; it has no ticker column.

Why this works ↓

Pulling every row and deduplicating in pandas is slow and easy to get wrong. LATERAL runs the inner query once per company and stops at LIMIT 1. Sorting by `period_end` then `accepted_at` takes the latest period and, within it, the latest filed value.

sql

1WITH members AS (2  SELECT r.cik, r.symbol, r.sector3  FROM "references" r4  JOIN index_membership im5    ON im.cik = r.cik6  WHERE im.index_name = 'SP500'7    AND im.removal_date IS NULL8    AND r.is_active9    AND r.is_primary_ticker10)11SELECT12  m.symbol,13  m.sector,14  f.period_end,15  f.numeric_value AS revenue16FROM members m,17LATERAL (18  SELECT f0.period_end, f0.numeric_value19  FROM fact f020  WHERE f0.entity_id        = m.cik21    AND f0.standard_concept = 'TotalRevenue'22    AND f0.fiscal_period    = 'FY'23  ORDER BY f0.period_end DESC, f0.accepted_at DESC24  LIMIT 125) f26ORDER BY revenue DESC NULLS LAST27LIMIT 25;

Note · LATERAL is much cheaper than a self-join on `fact`.

Pivot several concepts in one pass over `fact`

Deduplicate vintages first, then one MAX(CASE) pivot turns revenue, net income, EPS and operating cash flow into columns. Group by `period_end`.

Why this works ↓

A 10-K repeats two or three earlier years as comparatives, so one period can appear in several filings. QUALIFY keeps the latest-filed value per (company, concept, period); without it MAX() would pick whichever vintage is largest. The pivot then reads `fact` once instead of once per concept.

sql

1WITH ids AS (2  SELECT cik, symbol3  FROM "references"4  WHERE symbol IN ('AAPL', 'MSFT', 'GOOGL', 'NVDA')5    AND is_active6    AND is_primary_ticker7),8latest AS (9  -- A 10-K repeats earlier years as comparatives: keep the10  -- latest-filed value for each (company, concept, period).11  SELECT fa.entity_id, fa.period_end, fa.standard_concept, fa.numeric_value12  FROM fact fa13  JOIN ids ON ids.cik = fa.entity_id14  WHERE fa.fiscal_period = 'FY'15    AND fa.standard_concept IN ('TotalRevenue', 'NetIncome', 'EPSDiluted', 'OperatingCashFlow')16  QUALIFY ROW_NUMBER() OVER (17    PARTITION BY fa.entity_id, fa.standard_concept, fa.period_end18    ORDER BY fa.accepted_at DESC19  ) = 120)21SELECT22  ids.symbol,23  l.period_end,24  MAX(CASE WHEN l.standard_concept = 'TotalRevenue'      THEN l.numeric_value END) AS revenue,25  MAX(CASE WHEN l.standard_concept = 'NetIncome'         THEN l.numeric_value END) AS net_income,26  MAX(CASE WHEN l.standard_concept = 'EPSDiluted'        THEN l.numeric_value END) AS eps_diluted,27  MAX(CASE WHEN l.standard_concept = 'OperatingCashFlow' THEN l.numeric_value END) AS operating_cash_flow28FROM latest l29JOIN ids ON ids.cik = l.entity_id30GROUP BY ids.symbol, l.period_end31ORDER BY ids.symbol, l.period_end;

Note · Each extra concept costs one more CASE, not one more scan.

Keep the latest vintage with QUALIFY ROW_NUMBER()

QUALIFY ROW_NUMBER() keeps one row per period: the most recently accepted value. `value_as_filed` and `restated` show what changed since the first filing.

Why this works ↓

Every filing that reports a period adds a row; nothing is overwritten. QUALIFY is the cleanest way to keep the latest one. `numeric_value` is the value in that filing, `value_as_filed` the value first reported for the period, and `restated` is true when they differ.

sql

1WITH tsla AS (2  SELECT cik FROM "references"3  WHERE symbol = 'TSLA' AND is_active AND is_primary_ticker4)5SELECT6  fa.period_end,7  fa.fiscal_period,8  fa.numeric_value,9  fa.value_as_filed,10  fa.restated,11  fa.accepted_at12FROM fact fa13JOIN tsla ON tsla.cik = fa.entity_id14WHERE fa.standard_concept = 'NetIncome'15QUALIFY ROW_NUMBER() OVER (16  PARTITION BY fa.entity_id, fa.period_end, fa.fiscal_period17  ORDER BY fa.accepted_at DESC18) = 119ORDER BY fa.period_end DESC, fa.fiscal_period;

As reported vs restatedFY2020, $M. A point-in-time query returns the figure as it was first reported.

Metric	as reported	restated
Revenue	10,388	10,388
Operating income	2,114	1,902
Net income	1,640	1,431
Diluted EPS	3.05	2.66

verify_fact_lineage() → both versions retained · each keyed to its filing

Note · Drop the QUALIFY clause to see every vintage of each period.

Quarter-only cash flow, Q4 included

Most Q2 and Q3 cash-flow rows are year-to-date as filed. `derived_quarterly_value` holds the quarter-only figure; on the FY row it is Q4.

Why this works ↓

Companies usually report cash flow year-to-date in their 10-Qs, and there is no Q4 10-Q at all. The pipeline stores the quarter-only figure in `derived_quarterly_value`: equal to the filed value for a standalone quarter, the year-to-date difference otherwise, and on the FY row, Q4 (the year minus the first three quarters). `is_cumulative` tells you which rows were filed year-to-date.

sql

1WITH aapl AS (2  SELECT cik FROM "references"3  WHERE symbol = 'AAPL' AND is_active AND is_primary_ticker4)5SELECT6  fa.period_end,7  fa.fiscal_period,8  fa.is_cumulative,                                      -- TRUE = year-to-date as filed9  fa.numeric_value                                       AS as_filed,10  COALESCE(fa.derived_quarterly_value, fa.numeric_value) AS quarter_only11FROM fact fa12JOIN aapl ON aapl.cik = fa.entity_id13WHERE fa.standard_concept = 'OperatingCashFlow'14  AND fa.fiscal_period IN ('Q1', 'Q2', 'Q3', 'FY')       -- FY row carries Q415  AND (fa.fiscal_period <> 'FY' OR fa.derived_quarterly_value IS NOT NULL)16QUALIFY ROW_NUMBER() OVER (17  PARTITION BY fa.period_end, fa.fiscal_period18  ORDER BY fa.accepted_at DESC19) = 120ORDER BY fa.period_end;

Note · COALESCE falls back to `numeric_value` for a standalone quarter. An FY row is kept only when it carries Q4, so a full-year total never poses as a quarter.

Point-in-time screen: the universe and the data on a date

Filter `index_membership` by its effective and removal dates and `fact` by `accepted_at`, so both the company list and its numbers are what was known on that date.

Why this works ↓

A backtest needs the index as it stood on the trade date, not today's members, and only the numbers already filed. Membership windows are half-open: a company removed on a date is not a member on it. With the Python SDK you can also pass `ValueinClient(as_of=...)`, which applies the `accepted_at` filter to every point-in-time table for you.

sql

1WITH universe_at AS (2  -- S&P 500 members on 2024-01-02. Removal is exclusive:3  -- a company removed on that date is no longer a member.4  SELECT DISTINCT im.cik5  FROM index_membership im6  WHERE im.index_name = 'SP500'7    AND im.effective_date <= DATE '2024-01-02'8    AND (im.removal_date IS NULL OR im.removal_date > DATE '2024-01-02')9),10known AS (11  -- Fiscal years that ended in 2022, using only values the SEC had12  -- accepted before 2024-01-02 (later restatements are ignored).13  SELECT fa.entity_id, fa.standard_concept, fa.numeric_value14  FROM fact fa15  WHERE fa.accepted_at < TIMESTAMP '2024-01-02 00:00:00'16    AND fa.fiscal_period = 'FY'17    AND fa.period_end BETWEEN DATE '2022-01-01' AND DATE '2022-12-31'18    AND fa.standard_concept IN ('TotalRevenue', 'NetIncome')19  QUALIFY ROW_NUMBER() OVER (20    PARTITION BY fa.entity_id, fa.standard_concept21    ORDER BY fa.accepted_at DESC22  ) = 123)24SELECT25  u.cik,26  MAX(CASE WHEN k.standard_concept = 'TotalRevenue' THEN k.numeric_value END) AS revenue,27  MAX(CASE WHEN k.standard_concept = 'NetIncome'    THEN k.numeric_value END) AS net_income,28  net_income / NULLIF(revenue, 0)                                              AS net_margin29FROM universe_at u30JOIN known k ON k.entity_id = u.cik31GROUP BY u.cik32ORDER BY revenue DESC NULLS LAST33LIMIT 50;

Delisted companies stay inBankrupt, acquired, failed or taken private: kept on Pro and Institutional.

Enron Corp
ENRNQ
bankrupt2001
Lehman Brothers
LEH
bankrupt2008
Bear Stearns
BSC
acquired2008
Countrywide Financial
CFC
acquired2008
Blockbuster Inc
BLOAQ
bankrupt2010
Whole Foods
WFM
acquired2017
Sears Holdings
SHLD
bankrupt2018
Twitter Inc
TWTR
taken private2022
SVB Financial
SIVB
failed2023

get_pit_universe(date) returns the market as it stood on that date, not as it survived.

Note · The membership filter prevents survivorship bias; the accepted_at filter prevents look-ahead bias. Join `references` on `cik` for today's ticker.

Defensive ratio math: ABS and NULLIF

Wrap capex in ABS (filers disagree on its sign) and every divisor in NULLIF (a zero divisor becomes NULL instead of an error).

Why this works ↓

Some filers report capex as a negative cash flow and some as a positive amount, and some periods have zero revenue. ABS and NULLIF make the formula safe for all of them.

sql

1WITH ids AS (2  SELECT cik, symbol3  FROM "references"4  WHERE symbol IN ('AAPL', 'MSFT', 'GOOGL', 'META', 'WMT', 'COST')5    AND is_active6    AND is_primary_ticker7),8latest AS (9  SELECT fa.entity_id, fa.standard_concept, fa.numeric_value10  FROM fact fa11  JOIN ids ON ids.cik = fa.entity_id12  WHERE fa.fiscal_period = 'FY'13    AND fa.standard_concept IN ('TotalRevenue', 'OperatingCashFlow', 'CAPEX')14  QUALIFY ROW_NUMBER() OVER (15    PARTITION BY fa.entity_id, fa.standard_concept16    ORDER BY fa.period_end DESC, fa.accepted_at DESC17  ) = 118),19pivoted AS (20  SELECT21    ids.symbol,22    MAX(CASE WHEN l.standard_concept = 'TotalRevenue'      THEN l.numeric_value END) AS revenue,23    MAX(CASE WHEN l.standard_concept = 'OperatingCashFlow' THEN l.numeric_value END) AS ocf,24    MAX(CASE WHEN l.standard_concept = 'CAPEX'             THEN l.numeric_value END) AS capex25  FROM latest l26  JOIN ids ON ids.cik = l.entity_id27  GROUP BY ids.symbol28)29SELECT30  symbol,31  ocf,32  capex,33  -- ABS: filers disagree on the sign of capex.34  -- NULLIF: a zero divisor returns NULL instead of failing the row.35  ocf - ABS(capex)                        AS free_cash_flow,36  (ocf - ABS(capex)) / NULLIF(revenue, 0) AS fcf_margin37FROM pivoted38ORDER BY fcf_margin DESC NULLS LAST;

Note · A missing input yields NULL rather than a wrong number; check the inputs when a ratio is NULL.

More patterns?

The Python SDK ships named SQL templates for common workflows: fundamentals by ticker, margins, free cash flow, factor screens, peer comparisons, filing links. client.list_templates() lists them all.

Python SDK guide Parquet schema reference