Q (haiku): Show me cases where PALB2 or ATM have germline mutations

▶ 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_run_select_query
{
 "query": "
-- Find studies with 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
",
 "max_rows": 50
}

◀ result
{"rows":[{"cancer_study_identifier":"coadread_tcga_pub","germline_count":101},{"cancer_study_identifier":"lung_nci_2022","germline_count":100},{"cancer_study_identifier":"pog570_bcgsc_2020","germline_count":74},{"cancer_study_identifier":"coadread_tcga","germline_count":70},{"cancer_study_identifier":"hnsc_a5consortium_2025","germline_count":67},{"cancer_study_identifier":"brca_tcga_pub","germline_count":64},{"cancer_study_identifier":"kirc_tcga_pub","germline_count":57},{"cancer_study_identifier":"all_stjude_2013","germline_count":52},{"cancer_study_identifier":"ov_tcga_pub","germline_count":51},{"cancer_study_identifier":"prad_su2c_2019","germline_count":40},{"cancer_study_identifier":"ov_tcga","germline_count":20},{"cancer_study_identifier":"aml_stjude_2024","germline_count":19},{"cancer_study_identifier":"mrt_bcgsc_2016","germline_count":10},{"cancer_study_identifier":"prad_tcga_pub","germline_count":7},{"cancer_study_identifier":"nhl_bcgsc_2011","germline_count":3},{"cancer_study_identifier":"stmyec_wcm_2022","germline_count":2}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Find cases with PALB2 or ATM germline mutations
SELECT 
  cancer_study_identifier,
  patient_unique_id,
  sample_unique_id,
  hugo_gene_symbol,
  mutation_variant,
  mutation_type,
  upper(mutation_status) as mutation_status
FROM genomic_event_derived
WHERE hugo_gene_symbol IN ('PALB2', 'ATM')
  AND variant_type = 'mutation'
  AND upper(mutation_status) = 'GERMLINE'
  AND off_panel = FALSE
ORDER BY cancer_study_identifier, hugo_gene_symbol, patient_unique_id
",
 "max_rows": 200
}

◀ result
{"rows":[{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-A2-A0T7","sample_unique_id":"brca_tcga_pub_TCGA-A2-A0T7-01","hugo_gene_symbol":"ATM","mutation_variant":"R3047*","mutation_type":"Nonsense_Mutation","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-A8-A08L","sample_unique_id":"brca_tcga_pub_TCGA-A8-A08L-01","hugo_gene_symbol":"ATM","mutation_variant":"G2023R","mutation_type":"Missense_Mutation","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-AN-A041","sample_unique_id":"brca_tcga_pub_TCGA-AN-A041-01","hugo_gene_symbol":"ATM","mutation_variant":"D1815Tfs*13","mutation_type":"Frame_Shift_Del","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-AN-A049","sample_unique_id":"brca_tcga_pub_TCGA-AN-A049-01","hugo_gene_symbol":"ATM","mutation_variant":"Q95*","mutation_type":"Nonsense_Mutation","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-AN-A0XL","sample_unique_id":"brca_tcga_pub_TCGA-AN-A0XL-01","hugo_gene_symbol":"ATM","mutation_variant":"X166_splice","mutation_type":"Splice_Site","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-B6-A0RV","sample_unique_id":"brca_tcga_pub_TCGA-B6-A0RV-01","hugo_gene_symbol":"ATM","mutation_variant":"X2032_splice","mutation_type":"Splice_Site","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-B6-A0WY","sample_unique_id":"brca_tcga_pub_TCGA-B6-A0WY-01","hugo_gene_symbol":"ATM","mutation_variant":"R2547_S2549del","mutation_type":"In_Frame_Del","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-BH-A0DD","sample_unique_id":"brca_tcga_pub_TCGA-BH-A0DD-01","hugo_gene_symbol":"ATM","mutation_variant":"R2912G","mutation_type":"Missense_Mutation","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-C8-A12X","sample_unique_id":"brca_tcga_pub_TCGA-C8-A12X-01","hugo_gene_symbol":"ATM","mutation_variant":"N1650S","mutation_type":"Missense_Mutation","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-D8-A13Z","sample_unique_id":"brca_tcga_pub_TCGA-D8-A13Z-01","hugo_gene_symbol":"ATM","mutation_variant":"X2544_splice","mutation_type":"Splice_Site","mutation_status":"GERMLINE"},{"cancer_study_identifier":"brca_tcga_pub","patient_unique_id":"brca_tcga_pub_TCGA-E2-A154","sample_unique_id":"brca_tcga_pub_TCGA-E2-A154-01","hugo_gene_symbol":"ATM","mutation_variant":"G2023R","mutation_type":"Missense_Mutation","mutation_status":"GERMLINE"},{"cancer_study_identifier":"kirc_tcga_pub","patient_unique_id":"kirc_tcga_pub_TCGA-B0-5097","sample_unique … (5376 more chars)

▶ clickhouse_run_select_query
{
 "query": "
-- Summary of germline PALB2 and ATM mutations
SELECT 
  hugo_gene_symbol,
  COUNT(DISTINCT patient_unique_id) as patient_count,
  COUNT(DISTINCT sample_unique_id) as sample_count,
  COUNT(DISTINCT cancer_study_identifier) as study_count
FROM genomic_event_derived
WHERE hugo_gene_symbol IN ('PALB2', 'ATM')
  AND variant_type = 'mutation'
  AND upper(mutation_status) = 'GERMLINE'
  AND off_panel = FALSE
GROUP BY hugo_gene_symbol
ORDER BY hugo_gene_symbol
"
}

◀ result
{"rows":[{"hugo_gene_symbol":"ATM","patient_count":29,"sample_count":30,"study_count":5},{"hugo_gene_symbol":"PALB2","patient_count":2,"sample_count":2,"study_count":1}]}

═ answer (success)
## Summary

I found **31 cases with germline PALB2 or ATM mutations** across cBioPortal studies:

### By Gene:
- **ATM:** 29 patients, 30 samples across 5 studies
- **PALB2:** 2 patients, 2 samples in 1 study

### Studies with Germline Cases:
1. **BRCA TCGA** (brca_tcga_pub) — 11 ATM cases
2. **KIRC TCGA** (kirc_tcga_pub) — 3 ATM cases  
3. **Lung NCI 2022** (lung_nci_2022) — 2 ATM cases
4. **POG 570 BCGSC** (pog570_bcgsc_2020) — 6 ATM cases, 2 PALB2 cases
5. **Prostate SU2C 2019** (prad_su2c_2019) — 7 ATM cases

### Variant Types:
- **ATM:** Mostly truncating mutations (nonsense, frameshift, splice site), with some missense variants
- **PALB2:** Frameshift deletion and splice site mutation

The cases above show sample IDs, specific variants (e.g., R3047*, D1815Tfs*13), and mutation types. Would you like me to:
- Navigate to a specific cohort visualization?
- Filter by cancer type or study?
- Look up clinical data for these patients?