Q (sonnet): Which patients have a TP53 G199V mutation? Which are somatic vs germline?

▶ list_guides
{}

◀ result
{"result":[{"uri":"cbioportal://mutation-frequency-guide","description":"Comprehensive guide for calculating gene mutation frequencies with gene-specific profiling denominators"},{"uri":"cbioportal://clinical-data-guide","description":"Guide for querying clinical data including patient vs sample level considerations"},{"uri":"cbioportal://sample-filtering-guide","description":"Guide for filtering samples and studies in cBioPortal queries"},{"uri":"cbioportal://common-pitfalls","description":"Guide to avoid common mistakes when querying cBioPortal data. If you already know which numbered pitfall applies, fetch just that section via read_guide(\"cbioportal://common-pitfalls#<number>\") (e.g. #16) instead of the full guide"},{"uri":"cbioportal://treatment-guide","description":"Guide for querying treatment/clinical event data including drug agents, timelines, and linking to genomic data"},{"uri":"cbioportal://faq-guide","description":"General cBioPortal FAQ: history, how to cite, data types, reference genome, abbreviations, GISTIC thresholds, API access"},{"uri":"cbioportal://statistical-tests-guide","description":"Statistical test selection guide — decision matrix for choosing Fisher's exact, Wilcoxon, chi-squared, t-test, ANOVA, etc. based on data type and group count"},{"uri":"cbioportal://gene-expression-guide","description":"Gene expression / copy-number / methylation analysis. Covers genetic_alteration_derived, profile_type discovery, and the gene_pair_coexpression view for Spearman correlation between two genes"},{"uri":"cbioportal://external-resources-guide","description":"Guide for finding external linked resources such as imaging, pathology, Minerva, HTAN, or other resource_* table links before declaring data unavailable"},{"uri":"cbioportal://gene-resolution-guide","description":"Guide for resolving ambiguous gene symbols, aliases, gene families, and shorthand such as CD3 before querying expression or alteration data"},{"uri":"cbioportal://study-resolution-guide","description":"Guide for resolving requested studies, avoiding silent substitute cohorts, and redirecting to known external cBioPortal instances when data is not in this deployment"},{"uri":"cbioportal://germline-guide","description":"Guide for querying germline variant data — storage columns, study discovery, query patterns, and somatic vs germline considerations"},{"uri":"cbioportal://study-guide/{study_id}","description":"Dynamic study-specific guide - use get_study_guide(study_id) tool to generate"}]}

▶ read_guide
{
 "uri": "cbioportal://germline-guide"
}

◀ result
Germline Variant Guide
======================

Overview
--------
cBioPortal stores both somatic AND germline variant data. Many cBioPortal features work identically for both variant types. This guide explains how to query germline variants and what to be aware of.

How Germline Data is Stored
---------------------------

### mutation_status is free text — always compare case-insensitively
Each study loads its own spelling. Germline calls appear as `'Germline'`, `'GERMLINE'` and `'germline'`; somatic calls as `'Somatic'`, `'SOMATIC'` and `'somatic'`; unannotated mutations as `'NA'`, `'.'`, `'Unknown'`, `'UNKNOWN'`, `'__UNKNOWN__'` and others. Matching one spelling silently drops whole studies (`mutation_status = 'Germline'` misses `all_stjude_2013`, `aml_stjude_2024` and `pog570_bcgsc_2020`).

- **Germline:** `upper(mutation_status) = 'GERMLINE'`
- **Somatic:** `upper(mutation_status) = 'SOMATIC'` — but only when the user asks for somatic-only. Many studies label their somatic calls `'NA'` or `'UNKNOWN'`, so for ordinary mutation questions follow common-pitfalls #3 and exclude only `'UNCALLED'`.
- When unsure, list the values first: `SELECT mutation_status, count() FROM genomic_event_derived WHERE cancer_study_identifier = '{study_id}' AND variant_type = 'mutation' GROUP BY mutation_status`

### Where the column lives
- `genomic_event_derived.mutation_status` (preferred): mutations, and structural variants (from `sv_status`: `'SOMATIC'`, `'Somatic'`, `'GERMLINE'`)
- `mutation_derived.mutationStatus`: the same values for mutations

Identifying Studies with Germline Data
--------------------------------------
Not all studies include germline data. Always check before querying:

```sql
-- Find studies containing germline mutations
SELECT cancer_study_identifier, COUNT(*) as germline_count
FROM genomic_event_derived
WHERE variant_type = 'mutation'
  AND upper(mutation_status) = 'GERMLINE'
GROUP BY cancer_study_identifier
ORDER BY germline_count DESC
```

Common Query Patterns
---------------------

### Count germline vs somatic mutations per gene in a study
```sql
SELECT hugo_gene_symbol, upper(mutation_status) AS status, COUNT(*) as count
FROM genomic_event_derived
WHERE cancer_study_identifier = '{study_id}'
  AND variant_type = 'mutation'
  AND upper(mutation_status) IN ('GERMLINE', 'SOMATIC')
GROUP BY hugo_gene_symbol, status
ORDER BY count DESC
LIMIT 20
```

### Find patients with germline mutations in a specific gene
```sql
SELECT DISTINCT patient_unique_id, sample_unique_id, mutation_variant, mutation_type
FROM genomic_event_derived
WHERE hugo_gene_symbol = '{GENE}'
  AND upper(mutation_status) = 'GERMLINE'
  AND cancer_study_identifier = '{study_id}'
  AND variant_type = 'mutation'
```

### Germline mutation frequency
The numerator is samples with a germline mutation in the gene; the denominator is samples **profiled** for the gene — not samples that happen to have a mutation in it. Take the denominator from mutation-frequency-guide Step 2 (or … (2222 more chars)

▶ clickhouse_list_table_columns
{
 "table": "genomic_event_derived"
}

◀ result
{"columns":[{"name":"sample_unique_id","type":"String"},{"name":"hugo_gene_symbol","type":"String","comment":"HUGO gene symbol (e.g., TP53, KRAS, BRAF). Use for gene-specific queries."},{"name":"entrez_gene_id","type":"Int32"},{"name":"gene_panel_stable_id","type":"LowCardinality(String)"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"genetic_profile_stable_id","type":"LowCardinality(String)"},{"name":"variant_type","type":"LowCardinality(String)","comment":"Type of genomic event: \"mutation\" for SNVs/indels, \"cna\" for copy number alterations, \"structural_variant\" for SVs. Always filter by this."},{"name":"mutation_variant","type":"String","comment":"Protein change notation (e.g., p.V600E, p.R175H). Use for specific variant queries. \"NA\" for non-mutation events."},{"name":"mutation_type","type":"LowCardinality(String)"},{"name":"mutation_status","type":"LowCardinality(String)","comment":"For mutations: Somatic, Germline, UNKNOWN, or UNCALLED. Filter mutation_status != \"UNCALLED\" to exclude uncertain calls. Include all other statuses."},{"name":"driver_filter","type":"LowCardinality(String)"},{"name":"driver_filter_annotation","type":"String"},{"name":"driver_tiers_filter","type":"LowCardinality(String)"},{"name":"driver_tiers_filter_annotation","type":"String"},{"name":"cna_alteration","type":"Nullable(Int8)","comment":"Copy number alteration: only 2 = amplification (AMP) and -2 = deep deletion (HOMDEL) are stored. Shallow deletion (-1), diploid (0) and gain (1) are not in this table; query genetic_alteration_derived WHERE profile_type = 'gistic' (alteration_value is a String, e.g. '-1'). NULL for non-CNA events."},{"name":"cna_cytoband","type":"String"},{"name":"sv_event_info","type":"String"},{"name":"patient_unique_id","type":"String"},{"name":"off_panel","type":"Bool","comment":"Boolean: 1 = mutation outside gene panel coverage (off-panel), 0 = within panel (on-panel). Filter off_panel = 0 for reliable frequency calculations."}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT patient_unique_id, sample_unique_id, cancer_study_identifier, mutation_variant, mutation_type, upper(mutation_status) AS status, off_panel
FROM genomic_event_derived
WHERE hugo_gene_symbol = 'TP53'
  AND variant_type = 'mutation'
  AND mutation_variant IN ('p.G199V', 'G199V')
ORDER BY cancer_study_identifier, patient_unique_id"
}

◀ result
{"rows":[{"patient_unique_id":"alk_msk_2026_C-RPRH27","sample_unique_id":"alk_msk_2026_s_C_RPRH27_X001_d01","cancer_study_identifier":"alk_msk_2026","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"NA","off_panel":false},{"patient_unique_id":"blca_tcga_gdc_TCGA-XF-A9ST","sample_unique_id":"blca_tcga_gdc_TCGA-XF-A9ST-01","cancer_study_identifier":"blca_tcga_gdc","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient_unique_id":"blca_tcga_pan_can_atlas_2018_TCGA-XF-A9ST","sample_unique_id":"blca_tcga_pan_can_atlas_2018_TCGA-XF-A9ST-01","cancer_study_identifier":"blca_tcga_pan_can_atlas_2018","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":".","off_panel":false},{"patient_unique_id":"blca_tcga_pub_2017_TCGA-XF-A9ST","sample_unique_id":"blca_tcga_pub_2017_TCGA-XF-A9ST-01","cancer_study_identifier":"blca_tcga_pub_2017","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient_unique_id":"bm_nsclc_mskcc_2023_P-0049559","sample_unique_id":"bm_nsclc_mskcc_2023_P-0049559-T01-IM6","cancer_study_identifier":"bm_nsclc_mskcc_2023","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient_unique_id":"bowel_colitis_msk_2022_TRF117586","sample_unique_id":"bowel_colitis_msk_2022_TRF117586","cancer_study_identifier":"bowel_colitis_msk_2022","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"NA","off_panel":false},{"patient_unique_id":"braf_msk_impact_2024_P-0046745","sample_unique_id":"braf_msk_impact_2024_P-0046745-T01-IM6","cancer_study_identifier":"braf_msk_impact_2024","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient_unique_id":"brca_metabric_MB-2632","sample_unique_id":"brca_metabric_MB-2632","cancer_study_identifier":"brca_metabric","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"NA","off_panel":false},{"patient_unique_id":"brca_tcga_TCGA-A2-A0SX","sample_unique_id":"brca_tcga_TCGA-A2-A0SX-01","cancer_study_identifier":"brca_tcga","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient_unique_id":"brca_tcga_TCGA-C8-A27A","sample_unique_id":"brca_tcga_TCGA-C8-A27A-01","cancer_study_identifier":"brca_tcga","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient_unique_id":"brca_tcga_gdc_TCGA-A2-A0SX","sample_unique_id":"brca_tcga_gdc_TCGA-A2-A0SX-01","cancer_study_identifier":"brca_tcga_gdc","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient_unique_id":"brca_tcga_gdc_TCGA-C8-A27A","sample_unique_id":"brca_tcga_gdc_TCGA-C8-A27A-01","cancer_study_identifier":"brca_tcga_gdc","mutation_variant":"G199V","mutation_type":"Missense_Mutation","status":"SOMATIC","off_panel":false},{"patient … (22426 more chars)

▶ clickhouse_run_select_query
{
 "query": "SELECT upper(mutation_status) AS status, COUNT(*) AS n, COUNT(DISTINCT patient_unique_id) AS distinct_patients
FROM genomic_event_derived
WHERE hugo_gene_symbol = 'TP53'
  AND variant_type = 'mutation'
  AND mutation_variant = 'G199V'
GROUP BY status
ORDER BY n DESC"
}

◀ result
{"rows":[{"status":"SOMATIC","n":156,"distinct_patients":144},{"status":"NA","n":32,"distinct_patients":31},{"status":".","n":5,"distinct_patients":5},{"status":"UNKNOWN","n":1,"distinct_patients":1},{"status":"GERMLINE","n":1,"distinct_patients":1}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT patient_unique_id, sample_unique_id, cancer_study_identifier, mutation_type, off_panel
FROM genomic_event_derived
WHERE hugo_gene_symbol = 'TP53'
  AND variant_type = 'mutation'
  AND mutation_variant = 'G199V'
  AND upper(mutation_status) = 'GERMLINE'"
}

◀ result
{"rows":[{"patient_unique_id":"brca_tcga_pub_TCGA-A2-A0SX","sample_unique_id":"brca_tcga_pub_TCGA-A2-A0SX-01","cancer_study_identifier":"brca_tcga_pub","mutation_type":"Missense_Mutation","off_panel":false}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT
  replaceRegexpOne(patient_unique_id, '^[a-z0-9_]+_(TCGA|P-|C3|C3N|C3L|MB-|BCM|CRC|S-)', '\\\\1') AS raw_id,
  patient_unique_id,
  cancer_study_identifier,
  upper(mutation_status) AS status
FROM genomic_event_derived
WHERE hugo_gene_symbol = 'TP53'
  AND variant_type = 'mutation'
  AND mutation_variant = 'G199V'
  AND cancer_study_identifier IN ('brca_tcga_pub','brca_tcga','brca_tcga_gdc','brca_tcga_pan_can_atlas_2018','brca_tcga_pub2015')
ORDER BY raw_id"
}

◀ result
{"rows":[{"raw_id":"TCGA-A2-A0SX","patient_unique_id":"brca_tcga_gdc_TCGA-A2-A0SX","cancer_study_identifier":"brca_tcga_gdc","status":"SOMATIC"},{"raw_id":"TCGA-A2-A0SX","patient_unique_id":"brca_tcga_TCGA-A2-A0SX","cancer_study_identifier":"brca_tcga","status":"SOMATIC"},{"raw_id":"TCGA-A2-A0SX","patient_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A0SX","cancer_study_identifier":"brca_tcga_pan_can_atlas_2018","status":"."},{"raw_id":"TCGA-A2-A0SX","patient_unique_id":"brca_tcga_pub2015_TCGA-A2-A0SX","cancer_study_identifier":"brca_tcga_pub2015","status":"NA"},{"raw_id":"TCGA-A2-A0SX","patient_unique_id":"brca_tcga_pub_TCGA-A2-A0SX","cancer_study_identifier":"brca_tcga_pub","status":"GERMLINE"},{"raw_id":"TCGA-C8-A27A","patient_unique_id":"brca_tcga_gdc_TCGA-C8-A27A","cancer_study_identifier":"brca_tcga_gdc","status":"SOMATIC"},{"raw_id":"TCGA-C8-A27A","patient_unique_id":"brca_tcga_TCGA-C8-A27A","cancer_study_identifier":"brca_tcga","status":"SOMATIC"},{"raw_id":"TCGA-C8-A27A","patient_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-C8-A27A","cancer_study_identifier":"brca_tcga_pan_can_atlas_2018","status":"."},{"raw_id":"TCGA-C8-A27A","patient_unique_id":"brca_tcga_pub2015_TCGA-C8-A27A","cancer_study_identifier":"brca_tcga_pub2015","status":"NA"}]}

▶ resolve_and_route
{
 "studyIds": [
  "msk_chord_2024",
  "brca_tcga_pub"
 ]
}

◀ result
{"success":true,"message":"Found 2 matching studies. Pick the best match (prefer TCGA → prefer PanCancer Atlas), use its metadata to call the appropriate navigation tool(s) and generate URLs immediately. Present other studies as alternatives.","data":{"totalCount":2,"studiesWithMetadata":[{"studyId":"msk_chord_2024","name":"MSK-CHORD (MSK, Nature 2024)","sampleCount":25040,"studyViewUrl":"https://www.cbioportal.org/study?id=msk_chord_2024","metadata":{"clinicalAttributeIds":["ADRENAL_GLANDS","BONE","CANCER_TYPE","CANCER_TYPE_DETAILED","CLINICAL_GROUP","CLINICAL_SUMMARY","CNS_BRAIN","CURRENT_AGE_DEID","DIAGNOSIS_DESCRIPTION","ETHNICITY","FRACTION_GENOME_ALTERED","GENDER","GENE_PANEL","GLEASON_FIRST_REPORTED","GLEASON_HIGHEST_REPORTED","GLEASON_SAMPLE_LEVEL","HER2","HISTORY_OF_PDL1","HR","ICD_O_HISTOLOGY_DESCRIPTION","INTRA_ABDOMINAL","LIVER","LUNG","LYMPH_NODES","METASTATIC_SITE","MSI_COMMENT","MSI_SCORE","MSI_TYPE","MUTATION_COUNT","NUM_ICDO_DX","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER","PATHOLOGICAL_GROUP","PDL1_POSITIVE","PLEURA","PRIMARY_SITE","PRIOR_MED_TO_MSK","RACE","REPRODUCTIVE_ORGANS","SAMPLE_CLASS","SAMPLE_COUNT","SAMPLE_COVERAGE","SAMPLE_TYPE","SMOKING_PREDICTIONS_3_CLASSES","SOMATIC_STATUS","STAGE_HIGHEST_RECORDED","TMB_NONSYNONYMOUS","TUMOR_PURITY"],"molecularProfileIds":["msk_chord_2024_cna","msk_chord_2024_mutations","msk_chord_2024_structural_variants"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations"]}},{"studyId":"brca_tcga_pub","name":"Breast Invasive Carcinoma (TCGA, Nature 2012)","sampleCount":825,"studyViewUrl":"https://www.cbioportal.org/study?id=brca_tcga_pub","metadata":{"clinicalAttributeIds":["AGE","CANCER_TYPE","CANCER_TYPE_DETAILED","CN_CLUSTER","CONVERTED_STAGE","ER_STATUS","FRACTION_GENOME_ALTERED","HER2_STATUS","INTEGRATED_CLUSTERS_NO_EXP","INTEGRATED_CLUSTERS_UNSUP_EXP","INTEGRATED_CLUSTERS_WITH_PAM50","METASTASIS","METASTASIS_CODED","METHYLATION_CLUSTER","MIRNA_CLUSTER","MUTATION_COUNT","NODES","NODE_CODED","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","PAM50_SUBTYPE","PR_STATUS","RPPA_CLUSTER","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SIGCLUST_INTRINSIC_MRNA","SIGCLUST_UNSUPERVISED_MRNA","SOMATIC_STATUS","SURVIVAL_DATA_FORM","TMB_NONSYNONYMOUS","TUMOR_STAGE","TUMOR_T1_CODED"],"molecularProfileIds":["brca_tcga_pub_gistic","brca_tcga_pub_linear_CNA","brca_tcga_pub_methylation_hm27","brca_tcga_pub_mirna","brca_tcga_pub_mirna_median_Zscores","brca_tcga_pub_mrna","brca_tcga_pub_mrna_median_Zscores","brca_tcga_pub_mrna_median_all_sample_Zscores","brca_tcga_pub_mrna_merged_median_Zscores","brca_tcga_pub_mutations","brca_tcga_pub_rppa","brca_tcga_pub_rppa_Zscores"],"heatmapProfileIds":["brca_tcga_pub_methylation_hm27","brca_tcga_pub_mrna_median_Zscores","brca_tcga_pub_mrna_median_all_sample_Zscores","brca_tcga_pub_mrna_merged_median_Zscores","brca_tcga_pub_rppa_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","protein","dna_methylation"]} … (4 more chars)

▶ navigate_to_results_view
{
 "studyIds": [
  "msk_chord_2024"
 ],
 "genes": [
  "TP53: G199V"
 ],
 "tab": "mutations"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/results/mutations?cancer_study_list=msk_chord_2024&gene_list=TP53%3A+G199V&case_set_id=msk_chord_2024_all","url":"https://www.cbioportal.org/results/mutations?cancer_study_list=msk_chord_2024&gene_list=TP53%3A+G199V&case_set_id=msk_chord_2024_all","data":{"studyIds":["msk_chord_2024"],"studies":[{"studyId":"msk_chord_2024","name":"MSK-CHORD (MSK, Nature 2024)","sampleCount":25040}],"genes":["TP53"],"caseSetId":"msk_chord_2024_all","pageDescription":"Detailed mutation table with lollipop plot showing mutation positions, types, and frequencies for queried genes."}}

▶ navigate_to_patient_view
{
 "studyIds": [
  "brca_tcga_pub"
 ],
 "patientId": "TCGA-A2-A0SX"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/patient/summary?studyId=brca_tcga_pub&caseId=TCGA-A2-A0SX","url":"https://www.cbioportal.org/patient/summary?studyId=brca_tcga_pub&caseId=TCGA-A2-A0SX","data":{"patientUrls":[{"studyId":"brca_tcga_pub","studyName":"Breast Invasive Carcinoma (TCGA, Nature 2012)","url":"https://www.cbioportal.org/patient/summary?studyId=brca_tcga_pub&caseId=TCGA-A2-A0SX"}],"patientId":"TCGA-A2-A0SX","tab":"summary","hasMultipleUrls":false,"pageDescription":"Patient overview including patient history timeline (if available) and tables of mutations, structural variants, and copy number alterations."}}

═ answer (success)
**[View TP53 G199V Mutations in MSK-CHORD 2024](https://www.cbioportal.org/results/mutations?cancer_study_list=msk_chord_2024&gene_list=TP53%3A+G199V&case_set_id=msk_chord_2024_all)** — Detailed mutation table with lollipop plot; MSK-CHORD (25,040 samples) is the largest and most current cohort carrying this variant, with dozens of somatic hits.

**[View Patient TCGA-A2-A0SX (BRCA-TCGA, 2012)](https://www.cbioportal.org/patient/summary?studyId=brca_tcga_pub&caseId=TCGA-A2-A0SX)** — the germline TP53 G199V patient; patient summary with mutation table.

### Caveat
Because TP53 G199V is present across ~50 largely overlapping studies (MSK-IMPACT/CHORD/50k supersets; TCGA legacy/GDC/PanCanAtlas/pub reprocessings of the same cases), the raw record count (156 somatic, 144 distinct patient-IDs) overstates unique real people. The germline result is a single, isolated annotation on one historical TCGA record that is called *somatic* in that same patient's more recently reprocessed data — treat the germline classification as uncertain/version-dependent rather than a confirmed inherited variant, and note that cBioPortal's mutation-status annotation is not equivalent to clinical germline testing confirmation.