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

▶ read_guide
{
 "uri": "cbioportal://mutation-frequency-guide"
}

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

◀ result
# Mutation Frequency Analysis Guide

## IMPORTANT: Reporting Mutation Frequencies
- **ALWAYS report frequencies as percentages**, not raw counts: `frequency = (altered_samples / total_profiled_samples) × 100`
- For quick frequency lookups, **prefer the TCGA Pan-Cancer Atlas study first**, then offer to expand to other studies
- When reporting across multiple studies, show **ranges** (e.g., "TP53 is mutated in 30–60% of samples") rather than a single average
- **NEVER** sum mutation events across studies to compute an aggregate frequency — this can exceed 100% due to double-counting
- Warn users that samples may overlap across cohorts (e.g., MSK studies may share patients)
- **Choose and state the counting unit**: use patient-level frequencies for prevalence/rate questions unless the user explicitly asks for samples; use sample-level frequencies when the user asks about samples.
- **For "across cancer types" questions**, jump to the [Cross-Cancer-Type Mutation Frequency](#cross-cancer-type-mutation-frequency) section below — there is one correct recipe and several common wrong ones.

## Counting Unit: Samples vs Patients

Before answering any mutation count or frequency question, decide whether the unit is samples or patients and state that choice in the answer.

| User wording | Counting unit |
|--------------|---------------|
| "prevalence", "rate", "fraction of patients", "patients with", "how common is" | Patient-level: `COUNT(DISTINCT patient_unique_id)` |
| "samples", "specimens", "biopsies", sample-level cohort composition | Sample-level: `COUNT(DISTINCT sample_unique_id)` |
| Ambiguous | Ask, or default to patient-level for prevalence/rate language and say so |

### Cross-study sample-count caveat

When an answer touches more than one study and reports a sample count, prepend a one-line caveat:

> Sample IDs are unique within cBioPortal study prefixes, not guaranteed biological-sample identifiers across studies; overlapping cohorts can count the same patient/sample more than once.

Prefer one of these safer approaches:

- Use a shipped `cancer_study_query_preferences` cohort such as `pan_cancer_tcga` or `all_studies_non_redundant`.
- Restrict to one named study.
- Aggregate by `patient_unique_id` when the biological question is patient prevalence.

## STOP rule: a frequency above 100% means your query is wrong

If your query returns a frequency over 100%, **do not try to debug or explain the data inconsistency to the user**. The cause is always one of these query bugs:

- Summing mutation events instead of `COUNT(DISTINCT sample_unique_id)` for the numerator
- Using a study-wide sample count as the denominator instead of the gene-specific profiled count
- Cross-study aggregation where the same biological sample appears under multiple `sample_unique_id` values (e.g., MSK-IMPACT and MSK-CHORD share patients)
- **Joining the profiled CTE through `gene_panel` / `gene_panel_list` without a WES branch.** `gene_panel_id = 'WES'` is *not* a row in … (36467 more chars)

◀ 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 cancer_study_identifier, patient_unique_id, sample_unique_id, mutation_variant, mutation_type, mutation_status
FROM genomic_event_derived
WHERE hugo_gene_symbol='TP53' AND variant_type='mutation' AND mutation_status!='UNCALLED'
AND replaceRegexpOne(mutation_variant,'^p\\\\.','')='G199V'
ORDER BY cancer_study_identifier, patient_unique_id",
 "max_rows": 500
}

◀ result
{"rows":[{"cancer_study_identifier":"alk_msk_2026","patient_unique_id":"alk_msk_2026_C-RPRH27","sample_unique_id":"alk_msk_2026_s_C_RPRH27_X001_d01","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"NA"},{"cancer_study_identifier":"blca_tcga_gdc","patient_unique_id":"blca_tcga_gdc_TCGA-XF-A9ST","sample_unique_id":"blca_tcga_gdc_TCGA-XF-A9ST-01","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"Somatic"},{"cancer_study_identifier":"blca_tcga_pan_can_atlas_2018","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","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"."},{"cancer_study_identifier":"blca_tcga_pub_2017","patient_unique_id":"blca_tcga_pub_2017_TCGA-XF-A9ST","sample_unique_id":"blca_tcga_pub_2017_TCGA-XF-A9ST-01","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"Somatic"},{"cancer_study_identifier":"bm_nsclc_mskcc_2023","patient_unique_id":"bm_nsclc_mskcc_2023_P-0049559","sample_unique_id":"bm_nsclc_mskcc_2023_P-0049559-T01-IM6","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"SOMATIC"},{"cancer_study_identifier":"bowel_colitis_msk_2022","patient_unique_id":"bowel_colitis_msk_2022_TRF117586","sample_unique_id":"bowel_colitis_msk_2022_TRF117586","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"NA"},{"cancer_study_identifier":"braf_msk_impact_2024","patient_unique_id":"braf_msk_impact_2024_P-0046745","sample_unique_id":"braf_msk_impact_2024_P-0046745-T01-IM6","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"SOMATIC"},{"cancer_study_identifier":"brca_metabric","patient_unique_id":"brca_metabric_MB-2632","sample_unique_id":"brca_metabric_MB-2632","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"NA"},{"cancer_study_identifier":"brca_tcga","patient_unique_id":"brca_tcga_TCGA-A2-A0SX","sample_unique_id":"brca_tcga_TCGA-A2-A0SX-01","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"Somatic"},{"cancer_study_identifier":"brca_tcga","patient_unique_id":"brca_tcga_TCGA-C8-A27A","sample_unique_id":"brca_tcga_TCGA-C8-A27A-01","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"Somatic"},{"cancer_study_identifier":"brca_tcga_gdc","patient_unique_id":"brca_tcga_gdc_TCGA-A2-A0SX","sample_unique_id":"brca_tcga_gdc_TCGA-A2-A0SX-01","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"Somatic"},{"cancer_study_identifier":"brca_tcga_gdc","patient_unique_id":"brca_tcga_gdc_TCGA-C8-A27A","sample_unique_id":"brca_tcga_gdc_TCGA-C8-A27A-01","mutation_variant":"G199V","mutation_type":"Missense_Mutation","mutation_status":"Somatic"},{"cancer_study_identifier":"brca_tcga_pan_can_atlas_2018","patient_unique_id":"brca_tcga_pan_can_atlas_2018_TCGA-A2-A … (44379 more chars)

▶ clickhouse_run_select_query
{
 "query": "SELECT count() AS rows, uniqExact(patient_unique_id) AS patient_study_pairs, uniqExact(cancer_study_identifier) AS studies,
uniqExact(replaceRegexpOne(patient_unique_id, '^.*?_((P-\\\\d+)|(TCGA-[A-Z0-9]{2}-[A-Z0-9]{4}))$', '\\\\1')) AS approx_distinct_ids,
uniqExactIf(patient_unique_id, upper(mutation_status)='GERMLINE') AS germline_patients,
uniqExactIf(patient_unique_id, upper(mutation_status)='SOMATIC') AS somatic_patients,
uniqExactIf(patient_unique_id, upper(mutation_status) NOT IN ('GERMLINE','SOMATIC')) AS unspecified_patients
FROM genomic_event_derived
WHERE hugo_gene_symbol='TP53' AND variant_type='mutation' AND mutation_status!='UNCALLED'
AND replaceRegexpOne(mutation_variant,'^p\\\\.','')='G199V'"
}

◀ result
{"rows":[{"rows":195,"patient_study_pairs":180,"studies":67,"approx_distinct_ids":68,"germline_patients":1,"somatic_patients":144,"unspecified_patients":36}]}

═ answer (success)
TP53 G199V (a missense mutation) appears in 180 patient entries across 67 studies. Only 1 entry is labeled germline. The overlap between studies is heavy, so the number of distinct people is much lower than 180.

The same person is often counted in several studies. MSK patients recur across MSK-IMPACT, MSK-CHORD, MSK-MET and the tumor-specific MSK studies. TCGA patients recur across the TCGA PanCancer Atlas, GDC, and older publication versions of each study. Please don't read 180 as a patient count.

**Germline (1 entry)**
- **TCGA-A2-A0SX** is labeled germline in [`brca_tcga_pub`](https://www.cbioportal.org/study/summary?id=brca_tcga_pub).
- The same patient carries G199V in other TCGA breast studies, where it is labeled somatic (`brca_tcga`, `brca_tcga_gdc`). It has no status label in the PanCancer Atlas or in `brca_tcga_pub2015`.
- The germline and somatic labels for this patient conflict across studies. I can't tell from the data which is correct.

**Labeled somatic (144 patient entries)**
- **MSK cohorts:** most entries come from MSK-IMPACT, MSK-CHORD, MSK-MET and the tumor-specific MSK studies. The patients are mainly breast, colorectal, prostate and lung, plus some bladder, pancreatic and other cancers. Examples are P-0003676, P-0006595, P-0007144, P-0034389, P-0049559 and P-0066270.
- **TCGA:**
  - Bladder: TCGA-XF-A9ST
  - Breast: TCGA-C8-A27A (and TCGA-A2-A0SX in the studies noted above)
  - Colorectal: TCGA-AD-6901
  - Glioblastoma: TCGA-06-0152
  - Prostate: TCGA-EJ-8472
- **CPTAC:** C3L-03976 and C3N-02529 (lung adenocarcinoma), and C3N-02944 (pancreatic).
- **Sarcoma (MSK):** two patients in `sarcoma_msk_2022`.

**Status not specified (36 patient entries)**
These are labeled NA, ".", or UNKNOWN, which means unknown rather than somatic or germline. They include:
- The TCGA PanCancer Atlas studies.
- Cell-line and research cohorts: CCLE, `pan_origimed_2020` (5 patients), METABRIC (MB-2632), `pog570_bcgsc_2020`, `mbl_pcgp` and `mbl_dkfz_2017`, and `crc_sysucc_2022`.
- Some older MSK-IMPACT 2017 entries.

- **Germline data is limited.** Many studies don't load germline calls or don't label mutation status. "Somatic" here means only that the study said so, and it doesn't rule out germline origin.
- **No tumor-normal check.** The database has no tumor-normal or VAF evidence to confirm somatic versus germline origin.

To see the full patient list, open the TP53 Mutations tab in a study, for example [MSK-CHORD](https://www.cbioportal.org/results/mutations?cancer_study_list=msk_chord_2024&gene_list=TP53&case_set_id=msk_chord_2024_all&Action=Submit). The mutation table there can be filtered to G199V and shows each mutation's status.

I can also list one study per patient, or restrict the list to a single study.