-- Critical CVEs published in 2024
SELECT id, published,
       metrics.cvssMetricV31[0].cvssData.baseScore AS score,
       descriptions[0].value AS description
FROM   cve
WHERE  metrics.cvssMetricV31[0].cvssData.baseSeverity = 'CRITICAL'
  AND  published >= `2024-01-01T00:00:00Z`
ORDER BY score DESC
LIMIT 20

-- All advisories for a specific npm package
SELECT id, introduced, fixed, summary
FROM   advisories
WHERE  ecosystem = 'npm' AND package_name = 'lodash'

-- Unfixed critical advisories in PyPI (CVSS network-reachable, high impact)
SELECT id, package_name, summary
FROM   advisories
WHERE  ecosystem = 'PyPI'
  AND  severity_score ~ 'AV:N.*C:H'
  AND  fixed IS MISSING
LIMIT 50

-- Advisory counts by ecosystem
SELECT ecosystem, COUNT(*) AS n
FROM   advisories
GROUP BY ecosystem
ORDER BY n DESC

-- Recent articles matching a topic (regex title search)
SELECT MedlineCitation.PMID,
       MedlineCitation.Article.ArticleTitle        AS title,
       MedlineCitation.Article.Journal.Title        AS journal,
       MedlineCitation.DateCompleted.Year           AS year
FROM   baseline
WHERE  MedlineCitation.Article.ArticleTitle ~ '(?i)CRISPR'
  AND  MedlineCitation.DateCompleted.Year >= '2023'
LIMIT 20

-- Annual revenue for a company across fiscal years
SELECT ticker, fy, fp, val AS revenue, filed
FROM   edgar
WHERE  ticker = 'AAPL'
  AND  concept = 'Revenues'
  AND  form = '10-K'
ORDER BY fy DESC
LIMIT 10

-- Compare net income across S&P 500 for latest fiscal year
SELECT ticker, company, val AS net_income, fy
FROM   edgar
WHERE  concept = 'NetIncomeLoss'
  AND  form = '10-K' AND fy = '2024' AND unit = 'USD'
ORDER BY net_income DESC
LIMIT 20

-- Balance sheet: total assets vs total liabilities for one company
SELECT ticker, fy, fp, concept, val, filed
FROM   edgar
WHERE  ticker = 'MSFT'
  AND  concept IN ('Assets', 'Liabilities')
  AND  form = '10-K'
ORDER BY fy DESC
LIMIT 10

-- Discover which concepts a company has reported (useful before writing queries)
SELECT concept, label, unit, COUNT(*) AS filings
FROM   edgar
WHERE  ticker = 'TSLA'
GROUP BY concept, label, unit
ORDER BY filings DESC
LIMIT 50

-- Quarterly earnings per share trend
SELECT ticker, fy, fp, val AS eps, filed
FROM   edgar
WHERE  ticker = 'NVDA'
  AND  concept = 'EarningsPerShareBasic'
  AND  form = '10-Q'
ORDER BY filed DESC
LIMIT 12

-- Recent large USDC transfers (USDC = 6 decimals, $1M = 1e12)
SELECT transaction_hash, from_address, to_address, value
FROM   transfers
WHERE  token_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
  AND  CAST(value AS FLOAT) > 1000000000000
  AND  block_timestamp >= '2025-01-01'
LIMIT 20

-- All events from a specific contract (e.g. Uniswap v2 router)
SELECT block_timestamp, transaction_hash,
       topics[0] AS event_sig, data
FROM   logs
WHERE  address = '0x7a250d5630b4cf539739df2c5dacb4c659f2488d'
  AND  block_timestamp >= '2025-01-01'
LIMIT 20