What's in the dataset

SELECT f.filing_date,
  f.report_date,
  fa.numeric_value / 1e9 AS revenue_billions
FROM filing f
JOIN references r ON f.entity_id = r.cik
JOIN fact fa      ON fa.accession_id = f.accession_id
WHERE r.symbol = 'AAPL'
  AND r.is_active = TRUE
  AND fa.standard_concept = 'Revenues'
  AND f.form_type = '10-K'
ORDER BY f.filing_date DESC
LIMIT 10;

SELECT
  r.symbol,
  r.name                                               AS company_name,
  r.sector,
  f.filing_date                                        AS annual_filing_date,
  debt.numeric_value / 1e9                             AS long_term_debt_billions,
  equity.numeric_value / 1e9                           AS equity_billions,
  debt.numeric_value / NULLIF(equity.numeric_value, 0) AS debt_to_equity
FROM references r
JOIN index_membership im ON im.cik = r.cik
JOIN LATERAL (
  SELECT accession_id, filing_date
  FROM filing
  WHERE entity_id = r.cik AND form_type = '10-K'
  ORDER BY filing_date DESC
  LIMIT 1
) f ON true
JOIN fact debt   ON debt.accession_id = f.accession_id
  AND debt.standard_concept = 'LongTermDebt'
JOIN fact equity ON equity.accession_id = f.accession_id
  AND equity.standard_concept = 'StockholdersEquity'
WHERE im.index_name = 'SP500'
  AND im.removal_date IS NULL
  AND r.is_active = TRUE
  AND r.sector ILIKE '%technology%'
ORDER BY debt_to_equity DESC NULLS LAST;

SELECT
  r.symbol,
  r.name                                                              AS company_name,
  r.sector,
  f.filing_date                                                       AS latest_quarter,
  COALESCE(ocf.derived_quarterly_value, ocf.numeric_value) / 1e6     AS ocf_millions,
  ABS(COALESCE(capex.derived_quarterly_value, capex.numeric_value)) / 1e6 AS capex_millions,
  (COALESCE(ocf.derived_quarterly_value, ocf.numeric_value)
    - ABS(COALESCE(capex.derived_quarterly_value, capex.numeric_value))) / 1e6 AS fcf_millions
FROM references r
JOIN index_membership im ON im.cik = r.cik
JOIN LATERAL (
  SELECT accession_id, filing_date
  FROM filing
  WHERE entity_id = r.cik AND form_type = '10-Q'
  ORDER BY filing_date DESC
  LIMIT 1
) f ON true
JOIN fact ocf   ON ocf.accession_id = f.accession_id
  AND ocf.standard_concept = 'NetCashProvidedByUsedInOperatingActivities'
JOIN fact capex ON capex.accession_id = f.accession_id
  AND capex.standard_concept = 'PaymentsToAcquirePropertyPlantAndEquipment'
WHERE im.index_name = 'SP500'
  AND im.removal_date IS NULL
  AND r.is_active = TRUE
ORDER BY fcf_millions DESC NULLS LAST
LIMIT 20;

SELECT
  r.symbol,
  r.name                   AS company_name,
  r.sector,
  v.valuation_date,
  v.model_type,
  v.per_share_value,
  v.current_price,
  v.margin_of_safety,
  v.valuation_label
FROM valuation v
JOIN references r ON v.entity_id = r.cik
JOIN index_membership im ON im.cik = r.cik
WHERE im.index_name = 'SP500'
  AND im.removal_date IS NULL
  AND r.is_active = TRUE
  AND v.model_type = 'dcf'
  AND v.valuation_date = (
    SELECT MAX(valuation_date) FROM valuation WHERE entity_id = v.entity_id
  )
  AND v.per_share_value IS NOT NULL
ORDER BY v.margin_of_safety DESC NULLS LAST
LIMIT 20;