Datasets

-- 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

-- Annual revenue for a company across fiscal years
SELECT ticker, fy, fp, val AS revenue, filed
FROM   edgar
WHERE  ticker = 'AAPL'
  AND  concept = 'RevenueFromContractWithCustomerExcludingAssessedTax'
  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

-- Average gas price and block utilisation over the last 7 days
SELECT DATE(timestamp) AS day,
       AVG(base_fee_per_gas) / 1e9 AS avg_base_fee_gwei,
       AVG(gas_used * 100.0 / gas_limit) AS avg_utilisation_pct
FROM   blocks
WHERE  timestamp >= '2026-04-06'
GROUP BY day ORDER BY day

-- Lookup a contract: who deployed it and when?
SELECT deployer_address, block_timestamp, transaction_hash,
       is_erc20, is_erc721
FROM   contracts
WHERE  address = '0xdac17f958d2ee523a2206206994597c13d831ec7'

-- Top 10 gas spenders yesterday (total ETH paid in fees)
SELECT from_address,
       COUNT(*) AS tx_count,
       SUM(receipt_gas_used * receipt_effective_gas_price) / 1e18 AS eth_fees
FROM   transactions
WHERE  block_timestamp >= '2026-04-12'
  AND  block_timestamp <  '2026-04-13'
GROUP BY from_address
ORDER BY eth_fees DESC
LIMIT 10

-- Daily swap count and net USDC flow on the USDC/WETH 0.05% pool
-- amount0 is in USDC raw units (6 dec); positive = USDC into pool (ETH bought)
SELECT partition_date,
       COUNT(*) AS swap_count,
       SUM(amount0) / 1e6 AS net_usdc_flow
FROM   dex_swaps
WHERE  pool_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
  AND  block_timestamp >= '2026-04-07'
  AND  block_timestamp <  '2026-04-14'
GROUP BY partition_date ORDER BY partition_date LIMIT 10

-- Daily USDC supply volume by protocol (Aave v3, Compound v3)
SELECT partition_date,
       protocol,
       COUNT(*) AS supply_events,
       SUM(amount) / 1e6 AS volume_usdc
FROM   lending
WHERE  event_type = 'supply'
  AND  asset = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
  AND  block_timestamp >= '2026-04-07'
  AND  block_timestamp <  '2026-04-14'
GROUP BY partition_date, protocol ORDER BY partition_date LIMIT 20

-- Net liquidity minted vs burned in USDC/WETH 0.05% pool (last 7 days)
SELECT event_type,
       COUNT(*) AS events,
       SUM(amount0) / 1e6  AS usdc_total,
       SUM(amount1) / 1e18 AS weth_total
FROM   lp_events
WHERE  pool_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
  AND  block_timestamp >= '2026-04-07'
GROUP BY event_type

-- Relay market share — block count and MEV earned (last 30 days)
SELECT relay,
       COUNT(*) AS blocks,
       TRUNC(AVG(value_eth)*1000000)/1000000  AS avg_mev_eth,
       TRUNC(SUM(value_eth)*10000)/10000      AS total_mev_eth
FROM   mev
WHERE  date >= `2026-03-16T00:00:00Z`
GROUP BY relay
ORDER BY blocks DESC
LIMIT 10

-- Top 10 highest-value MEV blocks all time
SELECT date, block_number, relay, value_eth, num_txs
FROM   mev
ORDER BY value_eth DESC
LIMIT 10

-- Recent USDC transfers on Base over $50 (USDC = 6 decimals)
-- Update partition_date to a recent date within the last 30 days
SELECT transaction_hash, from_address, to_address, "value"
FROM   transfers
WHERE  token_address = '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913'
  AND  CAST("value" AS FLOAT) > 50000000
  AND  partition_date IN ('2026-04-12', '2026-04-13')
LIMIT 20

-- Average base fee and block utilisation over the last 7 days on Base
SELECT DATE(timestamp) AS day,
       AVG(base_fee_per_gas) / 1e9 AS avg_base_fee_gwei,
       AVG(gas_used * 100.0 / gas_limit) AS avg_utilisation_pct
FROM   blocks
WHERE  partition_date >= '2026-04-06'
GROUP BY day ORDER BY day

-- Top gas spenders on Base by total L1 + L2 fees paid
SELECT from_address,
       COUNT(*) AS tx_count,
       SUM(l2_fee + COALESCE(l1_fee, 0)) / 1e18 AS total_eth_fees
FROM   transactions
WHERE  partition_date >= '2026-04-06'
GROUP BY from_address
ORDER BY total_eth_fees DESC
LIMIT 10

-- Bitcoin difficulty trend over the last 90 days
SELECT partition_date,
       AVG(difficulty) AS avg_difficulty,
       SUM(transaction_count) AS daily_txns
FROM   blocks
WHERE  timestamp >= `2026-01-13T00:00:00Z`
GROUP BY partition_date ORDER BY partition_date

-- Total BTC received by an address over the last 2 years
SELECT SUM("value") / 1e8    AS total_btc,
       COUNT(*)               AS output_count
FROM   outputs
WHERE  address = 'bc1qxy2kgdygjrsqtzq2n0yrf2493p83kkfjhx0wlh'

-- Top 20 protocols by TVL
SELECT name, category, chain,
       TRUNC(tvl/1e9*1000)/1000  AS tvl_billions
FROM   protocols
ORDER BY tvl DESC
LIMIT 20

-- TVL by category (all protocols with TVL > 0)
SELECT category,
       COUNT(*) AS protocols,
       TRUNC(SUM(tvl)/1e9*100)/100  AS total_tvl_billions
FROM   protocols
WHERE  tvl > 0
GROUP BY category
ORDER BY total_tvl_billions DESC
LIMIT 20

-- Total DeFi TVL trend — last 30 days
SELECT date,
       TRUNC(SUM(tvl)/1e9*100)/100  AS total_tvl_billions,
       COUNT(*)                        AS protocol_count
FROM   tvl
WHERE  date >= `2026-03-16T00:00:00Z`
GROUP BY date
ORDER BY date
LIMIT 30

-- Aave V3 TVL history (last 90 days; each version has its own slug)
SELECT date,
       TRUNC(SUM(tvl)/1e9*1000)/1000  AS tvl_billions
FROM   tvl
WHERE  slug = 'aave-v3'
  AND  date >= `2026-01-15T00:00:00Z`
GROUP BY date
ORDER BY date
LIMIT 90

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

-- All papers by a specific author (any position in author list)
-- Author is an array: use comma in FROM to expand one row per author
SELECT DISTINCT
       p.MedlineCitation.PMID.text                  AS pmid,
       p.MedlineCitation.Article.ArticleTitle       AS title,
       p.MedlineCitation.Article.Journal.Title      AS journal,
       p.pub_year
FROM   baseline AS p,
       p.MedlineCitation.Article.AuthorList.Author AS a
WHERE  p.pub_year = 2023
  AND  a.LastName = 'Smith'
LIMIT 20

-- Papers co-authored by ALL of the listed authors (AND semantics)
-- IN() matches any name; HAVING matched = N enforces all-must-match
SELECT p.MedlineCitation.PMID.text             AS pmid,
       COUNT(DISTINCT a.LastName)              AS matched
FROM   baseline AS p,
       p.MedlineCitation.Article.AuthorList.Author AS a
WHERE  p.pub_year = 2023
  AND  a.LastName IN ('Smith', 'Jones', 'Brown')
GROUP BY pmid
HAVING matched = 3

-- Trials mentioning GLP-1 (regex search on title)
SELECT nct_id, brief_title, overall_status
FROM   studies
WHERE  brief_title ~ '(?i)GLP-?1'
LIMIT 20

-- Most common reactions for a drug (regex matches brand + generic names)
SELECT reaction, COUNT(*) AS n
FROM   faers, UNNEST(reactions) AS reaction
WHERE  drugname ~ '(?i)ozempic|semaglutide'
  AND  drug_role = 'PS'
GROUP BY reaction
ORDER BY n DESC
LIMIT 20

-- Fatal cases by drug class (regex on drug name, filter outcome = death)
SELECT drugname,
       COUNT(*) AS fatal_reports
FROM   faers
WHERE  drugname ~ '(?i)fentanyl|oxycodone|hydrocodone'
  AND  ARRAY_CONTAINS(outcomes, 'DE')
GROUP BY drugname
ORDER BY fatal_reports DESC
LIMIT 15

-- Top employers of donors to a committee (2024 cycle)
SELECT   employer, COUNT(*) AS n, SUM(transaction_amt) AS total_usd
FROM     contributions
WHERE    cycle = 2024
  AND    cmte_name ~ '(?i)actblue|winred'
  AND    employer != ''
GROUP BY employer
ORDER BY total_usd DESC
LIMIT    20

-- Political exposure check: donations by employees of a company (2024 cycle)
SELECT   name, cycle, cmte_name, cmte_party, transaction_amt, transaction_dt
FROM     contributions
WHERE    employer ~ '(?i)goldman sachs'
  AND    cycle = 2024
  AND    transaction_amt >= 2500
ORDER BY transaction_amt DESC
LIMIT    25

-- Top recipients of Google employee donations, 2024 cycle
SELECT   cmte_name, cmte_party,
         SUM(transaction_amt) AS total_usd,
         COUNT(*) AS n_contributions
FROM     contributions
WHERE    cycle = 2024
  AND    employer ~ '(?i)google'
GROUP BY cmte_name, cmte_party
ORDER BY total_usd DESC
LIMIT    10

-- All approved generics for a drug (AB-rated = substitutable)
SELECT application_number, sponsor_name, brand_name, te_code
FROM   orangebook
WHERE  ARRAY_CONTAINS(active_ingredients[0].name, 'METFORMIN')
  AND  te_code = 'AB'
ORDER BY sponsor_name
LIMIT 20

-- All products by a given sponsor
SELECT application_number, brand_name, dosage_form,
       route, te_code
FROM   orangebook
WHERE  sponsor_name ~ '(?i)pfizer'
  AND  appl_type = 'N'
ORDER BY brand_name
LIMIT 25

-- US unemployment rate since 2020
SELECT obs_date, "value"
FROM   series
WHERE  series_id = 'UNRATE'
  AND  obs_year >= 2020
ORDER BY obs_date
LIMIT 100

-- Compare CPI and core PCE year-over-year
SELECT series_id, title, obs_date, "value"
FROM   series
WHERE  series_id IN ('CPIAUCSL', 'PCEPILFE')
  AND  obs_year >= 2021
ORDER BY obs_date, series_id
LIMIT 200

-- All available series IDs with titles
SELECT DISTINCT series_id, category, title, units, frequency
FROM   series
ORDER BY category, series_id
LIMIT 200

-- Is Python 3.8 still supported?
SELECT product, cycle, "eol", lts, "latest"
FROM   cycles
WHERE  product = 'python'
ORDER BY cycle DESC
LIMIT 10

-- All products reaching EOL in 2025
SELECT product, cycle, "eol", "latest"
FROM   cycles
WHERE  "eol" LIKE '2025%'
ORDER BY "eol"
LIMIT 50

-- Recent ML papers mentioning transformers
SELECT id, submitted, title, primary_cat
FROM   papers
WHERE  primary_cat = 'cs.LG'
  AND  submitted >= '2024-01-01'
  AND  title ~ '(?i)transformer'
ORDER BY submitted DESC
LIMIT 25

-- Papers by an author across all categories
SELECT id, submitted, title, categories
FROM   papers, UNNEST(authors) AS a
WHERE  a.name ~ '(?i)lecun'
ORDER BY submitted DESC
LIMIT 20

-- Category submission volume by year (quantitative finance)
SELECT DATE_TRUNC('year', CAST(submitted AS TIMESTAMP)) AS yr,
       COUNT(*) AS papers
FROM   papers
WHERE  primary_cat = 'q-fin.TR'
GROUP BY yr
ORDER BY yr
LIMIT 50

-- Top associations for Type 2 Diabetes
SELECT rsid, snp_risk_allele, mapped_gene, p_value, or_beta, risk_allele_freq
FROM   associations
WHERE  disease_trait ~ '(?i)type 2 diabetes'
ORDER BY pvalue_mlog DESC
LIMIT 25

-- Join GWAS hits with ClinVar pathogenic variants
SELECT g.rsid, g.disease_trait, g.mapped_gene, g.p_value,
       c.significance, c.gene_symbol
FROM   gwas.associations AS g
JOIN   clinvar.variants   AS c
  ON   g.rsid = c.dbsnp_id
  AND  c.assembly = 'GRCh38' AND c.sig_simple = 1
WHERE  g.pvalue_mlog > 50
ORDER BY g.pvalue_mlog DESC
LIMIT 20

-- Most-studied traits by association count
SELECT disease_trait, COUNT(*) AS n_associations
FROM   associations
GROUP BY disease_trait
ORDER BY n_associations DESC
LIMIT 20

-- Pathogenic variants in BRCA1 (GRCh38)
SELECT allele_id, name, significance, chromosome, start, dbsnp_id
FROM   variants
WHERE  gene_symbol = 'BRCA1'
  AND  assembly    = 'GRCh38'
  AND  sig_simple  = 1
ORDER BY start
LIMIT 50

-- Variants associated with a phenotype (by name)
SELECT gene_symbol, significance, name, review_status, n_submitters
FROM   variants, UNNEST(phenotypes) AS ph
WHERE  assembly = 'GRCh38'
  AND  ph ~ '(?i)breast cancer'
  AND  sig_simple = 1
ORDER BY n_submitters DESC
LIMIT 25

-- Pathogenic variant count per gene (well-reviewed only)
SELECT gene_symbol, COUNT(*) AS pathogenic_variants
FROM   variants
WHERE  assembly      = 'GRCh38'
  AND  sig_simple    = 1
  AND  n_submitters >= 2
GROUP BY gene_symbol
ORDER BY pathogenic_variants DESC
LIMIT 20

-- Population frequency comparison for a known variant
SELECT rsid, ref, alt,
       af_tot, af_eur, af_afr_am, af_afr, af_eas, af_sas
FROM   variants
WHERE  chrom = '17'
  AND  rsid  = 'rs80357713'

-- Variants with large African American vs European frequency gap
-- (population-enriched variants, clinically under-studied)
SELECT rsid, chrom, pos, ref, alt,
       af_afr_am, af_eur,
       af_afr_am - af_eur AS delta
FROM   variants
WHERE  chrom     = '13'
  AND  an_afr_am >= 100
  AND  an_eur    >= 100
  AND  af_afr_am - af_eur > 0.3
ORDER BY delta DESC
LIMIT 20

-- Approved drugs targeting a gene (prior art / competitor check)
SELECT drug_name, interaction_type, interaction_score, source_db_name
FROM   interactions
WHERE  gene_name = 'EGFR'
  AND  approved  = TRUE
ORDER BY interaction_score DESC
LIMIT 20

-- Anti-cancer drugs for a gene, with adverse-event cross-check via FAERS
-- Step 1: get approved anti-neoplastic drugs targeting the gene
SELECT drug_name
FROM   dgidb.interactions
WHERE  gene_name       = 'BRAF'
  AND  approved        = TRUE
  AND  anti_neoplastic = TRUE

-- Step 2: feed drug_name list into FAERS for adverse event profile
SELECT reaction_meddra_pt, COUNT(*) AS reports
FROM   fda.faers
WHERE  drug_name IN ('VEMURAFENIB', 'DABRAFENIB', 'ENCORAFENIB')
GROUP BY reaction_meddra_pt
ORDER BY reports DESC
LIMIT 15

-- Look up an entity by name (partial match)
SELECT name, type, source, programs, addresses
FROM   entities
WHERE  name ~ '(?i)vtb bank'
LIMIT 10

-- All individuals sanctioned under Russia programs
SELECT name, type, start_date, alt_names
FROM   entities, UNNEST(programs) AS prog
WHERE  prog  ~ '(?i)russia'
  AND  type = 'Individual'
ORDER BY start_date DESC
LIMIT 25

-- Count entities per source list
SELECT source, COUNT(*) AS total
FROM   entities
GROUP BY source
ORDER BY total DESC

-- All samples for a malware family
SELECT sha256_hash, file_name, file_type_guess, first_seen_utc, vtpercent
FROM   samples
WHERE  signature ~ '(?i)emotet'
ORDER BY first_seen_utc DESC
LIMIT 25

-- Most prevalent malware families (last 6 months)
SELECT signature, COUNT(*) AS samples, AVG(vtpercent) AS avg_vt
FROM   samples
WHERE  first_seen_utc >= '2025-03-01'
  AND  signature != ''
GROUP BY signature
ORDER BY samples DESC
LIMIT 20

-- Lookup by hash (any of the three types)
SELECT sha256_hash, file_name, signature, first_seen_utc, vtpercent
FROM   samples
WHERE  md5_hash = 'd41d8cd98f00b204e9800998ecf8427e'
LIMIT 5

-- Lowest-sugar breakfast cereals with Nutri-Score A or B
SELECT product_name, brands, sugars_100g, nutrition_grade, countries
FROM   products
WHERE  categories ~ '(?i)cereal'
  AND  nutrition_grade IN ('a', 'b')
  AND  sugars_100g IS NOT MISSING
ORDER BY sugars_100g
LIMIT 20

-- Average macros and Nutri-Score by food category
SELECT main_category,
       COUNT(*) AS products,
       AVG(energy_100g) AS avg_kcal,
       AVG(sugars_100g) AS avg_sugars,
       AVG(proteins_100g) AS avg_protein
FROM   products
WHERE  main_category != ''
GROUP BY main_category
ORDER BY products DESC
LIMIT 20

-- Top stories of 2024 by score
SELECT title, url, score, descendants
FROM   items
WHERE  year = 2024
ORDER BY score DESC
LIMIT 20

-- Show HN posts per year — measure of builder activity over time
SELECT year,
       COUNT(*) AS show_hn_posts,
       AVG(score) AS avg_score
FROM   items
WHERE  title ~ '(?i)^show hn'
GROUP BY year
ORDER BY year DESC

-- Current acquisition cost for metformin generics
SELECT ndc_description, nadac_per_unit, pricing_unit, as_of_date
FROM   prices
WHERE  ndc_description ~ '(?i)metformin'
  AND  classification = 'Generic'
  AND  as_of_date >= '2025-01-01'
ORDER BY as_of_date DESC, ndc_description
LIMIT 20

-- Brand vs generic price gap for a drug class
SELECT classification,
       AVG(CAST(nadac_per_unit AS float)) AS avg_cost,
       COUNT(DISTINCT ndc) AS ndcs
FROM   prices
WHERE  ndc_description ~ '(?i)atorvastatin'
  AND  as_of_date >= '2025-01-01'
GROUP BY classification

-- Brent crude and natural gas price history since 2020
SELECT period, crude_oil_brent, crude_oil_wti, natural_gas_us
FROM   commodity_prices
WHERE  year >= 2020
ORDER BY year, month

-- Gold vs copper ratio over time (risk-off indicator)
SELECT period, gold, copper,
       gold / NULLIF(copper, 0) AS gold_copper_ratio
FROM   commodity_prices
WHERE  year >= 2010
ORDER BY year, month

-- National quarterly HPI since 2000 (purchase-only, seasonally adjusted)
SELECT yr, period, index_sa, index_nsa
FROM   hpi
WHERE  level      = 'USA'
  AND  hpi_type  = 'purchase-only'
  AND  frequency = 'quarterly'
  AND  yr >= 2000
ORDER BY yr, period

-- State-level HPI comparison (most recent quarter)
SELECT place_name, index_nsa, index_sa
FROM   hpi
WHERE  level      = 'state'
  AND  hpi_type  = 'purchase-only'
  AND  frequency = 'quarterly'
  AND  yr = 2025 AND period = 4
ORDER BY index_nsa DESC

-- Highest-evidence (1A) variant–drug pairs
SELECT variant, gene, type, chemicals, phenotypes
FROM   clinical_variants
WHERE  level_of_evidence = '1A'
ORDER BY gene

-- All CPIC guidelines with dosing recommendations
SELECT name, chemicals, genes, alleles
FROM   guidelines
WHERE  source = 'CPIC'
  AND  recommendation = true
ORDER BY name

-- FDA labels requiring genetic testing
SELECT name, chemicals, genes, testing_level
FROM   drug_labels
WHERE  source = 'FDA'
  AND  testing_level = 'Testing Required'
ORDER BY latest_history_date DESC
LIMIT 20

-- All confirmed gene–drug associations for CYP2D6
SELECT entity2_name AS drug, association, evidence, pmids
FROM   relationships
WHERE  entity1_name = 'CYP2D6'
  AND  entity1_type = 'Gene'
  AND  entity2_type = 'Chemical'
  AND  association  = 'associated'
ORDER BY entity2_name

-- All probes on chromosome 22 in gda_pgx
SELECT name, position, alleles, source, ref_strand
FROM   gda_pgx
WHERE  chr = '22'
ORDER BY position
LIMIT 20

-- CNV probes in gsa_pgx
SELECT name, chr, position, probe_seq_a
FROM   gsa_pgx
WHERE  source = 'CNV'
ORDER BY chr, position
LIMIT 20