Q (sonnet): What are the key genomic differences between lung adenocarcinomas and squamous cell carcinomas identified in the Pan-Lung Cancer TCGA study?

▶ list_studies
{
 "search": "Pan-Lung Cancer"
}

◀ result
{"result":[{"cancer_study_identifier":"nsclc_tcga_broad_2016","name":"Pan-Lung Cancer (TCGA, Nat Genet 2016)","type_of_cancer_id":"nsclc","sample_count":1144,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=nsclc_tcga_broad_2016"},{"cancer_study_identifier":"msk_met_2021","name":"MSK MetTropism (MSK, Cell 2021)","type_of_cancer_id":"mixed","sample_count":25775,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=msk_met_2021"},{"cancer_study_identifier":"luad_tcga_pan_can_atlas_2018","name":"Lung Adenocarcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"luad","sample_count":566,"has_guide":true,"url":"https://www.cbioportal.org/study/summary?id=luad_tcga_pan_can_atlas_2018"},{"cancer_study_identifier":"lusc_tcga_pan_can_atlas_2018","name":"Lung Squamous Cell Carcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"lusc","sample_count":487,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=lusc_tcga_pan_can_atlas_2018"}]}

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

▶ read_guide
{
 "uri": "cbioportal://statistical-tests-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
Statistical Test Selection Guide
================================

Purpose
-------
This guide ensures the correct statistical test is selected before performing any group comparison, matching cBioPortal's own Group Comparison defaults.

HARD RULES — NEVER FABRICATE A STATISTIC
----------------------------------------
ClickHouse cannot run statistical tests. The agent therefore must NEVER produce a derived statistic that is not a literal column value from a SQL result. Specifically:

1. **Never invent a p-value.** Not "p < 0.001", not "p ≈ 0.05", not any p-value. If the user asks "what is the p-value?", the answer is *"I can't compute that — here is the 2x2 contingency table (or group statistics). Run it in cBioPortal's Group Comparison tab, in R with `fisher.test(...)` / `wilcox.test(...)`, or in Python with `scipy.stats.fisher_exact(...)` / `mannwhitneyu(...)`."*
2. **Never claim mutual exclusivity (or co-occurrence) from a contingency table alone.** A 2x2 table is not a test. The shape "altered/not altered × group A/group B" needs Fisher's exact + a defined direction (odds ratio < 1 with significant p). Without that test, the agent presents the table and stops. Descriptive phrasing is also a claim: "largely/mostly mutually exclusive", "rarely co-occur", "tend to co-occur" are forbidden without the test — point to cBioPortal's Mutual Exclusivity tab instead.
3. **Never report a "median" that came from `AVG(...)` or any non-median aggregate.** "Median" and "mean" are different statistics; for skewed clinical distributions (especially survival) they differ substantially. Use ClickHouse's `quantile(0.5)(...)` for actual median, and label arithmetic averages as "mean", never "median".
4. **Never report a hazard ratio, odds ratio, risk ratio, or relative risk** that wasn't computed by an external tool. These require regression / model fitting that ClickHouse does not do.
5. **Never report median overall survival from `AVG(OS_MONTHS)` or even `quantile(0.5)(OS_MONTHS)`.** Median OS requires Kaplan-Meier estimation, which handles censoring (`OS_STATUS = 0:LIVING` means the event hasn't happened yet). Naive medians/means over `OS_MONTHS` ignore censoring and are systematically wrong. The correct handoff: return the raw `(OS_MONTHS, OS_STATUS)` pairs (or descriptive counts: N events, N censored, follow-up range) and tell the user to run KM in R (`survival::survfit`) or Python (`lifelines.KaplanMeierFitter`), or use cBioPortal's Survival comparison.

If asked to do any of (1)–(5), respond with the appropriate handoff template from the "Approved Response Templates" section below — do not produce the number.

Ambiguous-Term Clarification (before any analysis)
--------------------------------------------------
If the user's question uses an ambiguous term, ask before computing anything. Examples:

- **"aggressive"** → could mean shorter overall survival, higher metastasis rate, higher grade/stage, higher TMB, or specific molecular features. Ask which definit … (9984 more chars)

▶ clickhouse_run_select_query
{
 "query": "SELECT DISTINCT attribute_name FROM clinical_data_derived WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND (lower(attribute_name) LIKE '%histolog%' OR lower(attribute_name) LIKE '%cancer_type%' OR lower(attribute_name) LIKE '%subtype%')"
}

◀ result
{"rows":[{"attribute_name":"CANCER_TYPE"},{"attribute_name":"CANCER_TYPE_DETAILED"}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT attribute_value, count(DISTINCT sample_unique_id) AS n FROM clinical_data_derived WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND attribute_name = 'CANCER_TYPE_DETAILED' GROUP BY attribute_value ORDER BY n DESC"
}

◀ result
{"rows":[{"attribute_value":"Lung Adenocarcinoma","n":660},{"attribute_value":"Lung Squamous Cell Carcinoma","n":484}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH sct AS (
    SELECT sample_unique_id, attribute_value AS histology
    FROM clinical_data_derived
    WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND attribute_name = 'CANCER_TYPE_DETAILED'
),
altered AS (
    SELECT sct.histology, ged.hugo_gene_symbol, COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN sct USING (sample_unique_id)
    WHERE ged.cancer_study_identifier = 'nsclc_tcga_broad_2016'
      AND ged.variant_type = 'mutation'
      AND ged.mutation_status != 'UNCALLED'
      AND ged.off_panel = 0
    GROUP BY sct.histology, ged.hugo_gene_symbol
),
profiled AS (
    SELECT sct.histology, COUNT(DISTINCT sct.sample_unique_id) AS profiled_samples
    FROM sct
    JOIN (SELECT DISTINCT sample_unique_id FROM sample_to_gene_panel_derived WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND alteration_type = 'MUTATION_EXTENDED') p USING (sample_unique_id)
    GROUP BY sct.histology
)
SELECT a.histology, a.hugo_gene_symbol, a.altered_samples, p.profiled_samples,
       ROUND(a.altered_samples*100.0/p.profiled_samples,1) AS pct
FROM altered a JOIN profiled p USING (histology)
ORDER BY histology, altered_samples DESC
LIMIT 40
"
}

◀ result
{"rows":[{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"TP53","altered_samples":357,"profiled_samples":660,"pct":54.1},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"TTN","altered_samples":325,"profiled_samples":660,"pct":49.2},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"MUC16","altered_samples":271,"profiled_samples":660,"pct":41.1},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CSMD3","altered_samples":262,"profiled_samples":660,"pct":39.7},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"RYR2","altered_samples":250,"profiled_samples":660,"pct":37.9},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"LRP1B","altered_samples":231,"profiled_samples":660,"pct":35},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"USH2A","altered_samples":224,"profiled_samples":660,"pct":33.9},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"KRAS","altered_samples":214,"profiled_samples":660,"pct":32.4},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"ZFHX4","altered_samples":205,"profiled_samples":660,"pct":31.1},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SPTA1","altered_samples":178,"profiled_samples":660,"pct":27},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"XIRP2","altered_samples":173,"profiled_samples":660,"pct":26.2},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"FLG","altered_samples":161,"profiled_samples":660,"pct":24.4},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"FAT3","altered_samples":147,"profiled_samples":660,"pct":22.3},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CSMD1","altered_samples":145,"profiled_samples":660,"pct":22},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"MUC17","altered_samples":136,"profiled_samples":660,"pct":20.6},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"PCLO","altered_samples":131,"profiled_samples":660,"pct":19.8},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"PCDH15","altered_samples":131,"profiled_samples":660,"pct":19.8},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"NAV3","altered_samples":130,"profiled_samples":660,"pct":19.7},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"COL11A1","altered_samples":130,"profiled_samples":660,"pct":19.7},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"APOB","altered_samples":130,"profiled_samples":660,"pct":19.7},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"ZNF536","altered_samples":127,"profiled_samples":660,"pct":19.2},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"PAPPA2","altered_samples":125,"profiled_samples":660,"pct":18.9},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"ANK2","altered_samples":122,"profiled_samples":660,"pct":18.5},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"RYR3","altered_samples":120,"profiled_samples":660,"pct":18.2},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SI","altered_samples":119,"profiled_samples":660,"pct":18},{"histology":"Lung Adenocarcinoma", … (1753 more chars)

▶ clickhouse_run_select_query
{
 "query": "
WITH sct AS (
    SELECT sample_unique_id, attribute_value AS histology
    FROM clinical_data_derived
    WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND attribute_name = 'CANCER_TYPE_DETAILED'
)
SELECT sct.histology, ged.hugo_gene_symbol, ged.cna_alteration,
       COUNT(DISTINCT ged.sample_unique_id) AS n
FROM genomic_event_derived ged
JOIN sct USING (sample_unique_id)
WHERE ged.cancer_study_identifier = 'nsclc_tcga_broad_2016'
  AND ged.variant_type = 'cna'
  AND ged.cna_alteration IN (2,-2)
GROUP BY sct.histology, ged.hugo_gene_symbol, ged.cna_alteration
ORDER BY sct.histology, n DESC
LIMIT 40
"
}

◀ result
{"rows":[{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CDKN2A","cna_alteration":-2,"n":112},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CDKN2B","cna_alteration":-2,"n":108},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CDKN2A-AS1","cna_alteration":-2,"n":105},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CDKN2B-AS1","cna_alteration":-2,"n":104},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CEP72","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"EXOC3","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CCDC127","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"EXOC3-AS1","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"PDCD6","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SLC9A3-OT1","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"BRD9","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"PLEKHG4B","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SLC9A3","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SDHA","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"LRRC14B","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"TPPP","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"ZDHHC11","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"TRIP13","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"AHRR","cna_alteration":2,"n":99},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"NKD2","cna_alteration":2,"n":98},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"LINC02982","cna_alteration":2,"n":98},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"TERT","cna_alteration":2,"n":98},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SLC6A19","cna_alteration":2,"n":97},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SLC12A7","cna_alteration":2,"n":97},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SLC6A18","cna_alteration":2,"n":97},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CLPTM1L","cna_alteration":2,"n":96},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SLC6A3","cna_alteration":2,"n":95},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SDHAP3","cna_alteration":2,"n":93},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"IRX4","cna_alteration":2,"n":93},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"MRPL36","cna_alteration":2,"n":93},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"LPCAT1","cna_alteration":2,"n":93},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"NDUFS6","cna_alteration":2,"n":93},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"PDCD6P1","cna_alteration":2,"n":93},{" … (624 more chars)

▶ clickhouse_run_select_query
{
 "query": "
WITH sct AS (
    SELECT sample_unique_id, attribute_value AS histology
    FROM clinical_data_derived
    WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND attribute_name = 'CANCER_TYPE_DETAILED'
),
altered AS (
    SELECT sct.histology, ged.hugo_gene_symbol, COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN sct USING (sample_unique_id)
    WHERE ged.cancer_study_identifier = 'nsclc_tcga_broad_2016'
      AND ged.variant_type = 'mutation'
      AND ged.mutation_status != 'UNCALLED'
      AND ged.off_panel = 0
    GROUP BY sct.histology, ged.hugo_gene_symbol
),
profiled AS (
    SELECT sct.histology, COUNT(DISTINCT sct.sample_unique_id) AS profiled_samples
    FROM sct
    JOIN (SELECT DISTINCT sample_unique_id FROM sample_to_gene_panel_derived WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND alteration_type = 'MUTATION_EXTENDED') p USING (sample_unique_id)
    GROUP BY sct.histology
),
ranked AS (
    SELECT a.histology, a.hugo_gene_symbol, a.altered_samples, p.profiled_samples,
           ROUND(a.altered_samples*100.0/p.profiled_samples,1) AS pct,
           row_number() OVER (PARTITION BY a.histology ORDER BY a.altered_samples DESC) AS rn
    FROM altered a JOIN profiled p USING (histology)
)
SELECT * FROM ranked WHERE rn <= 15 ORDER BY histology, rn
"
}

◀ result
{"rows":[{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"TP53","altered_samples":357,"profiled_samples":660,"pct":54.1,"rn":1},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"TTN","altered_samples":325,"profiled_samples":660,"pct":49.2,"rn":2},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"MUC16","altered_samples":271,"profiled_samples":660,"pct":41.1,"rn":3},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CSMD3","altered_samples":262,"profiled_samples":660,"pct":39.7,"rn":4},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"RYR2","altered_samples":250,"profiled_samples":660,"pct":37.9,"rn":5},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"LRP1B","altered_samples":231,"profiled_samples":660,"pct":35,"rn":6},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"USH2A","altered_samples":224,"profiled_samples":660,"pct":33.9,"rn":7},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"KRAS","altered_samples":214,"profiled_samples":660,"pct":32.4,"rn":8},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"ZFHX4","altered_samples":205,"profiled_samples":660,"pct":31.1,"rn":9},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"SPTA1","altered_samples":178,"profiled_samples":660,"pct":27,"rn":10},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"XIRP2","altered_samples":173,"profiled_samples":660,"pct":26.2,"rn":11},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"FLG","altered_samples":161,"profiled_samples":660,"pct":24.4,"rn":12},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"FAT3","altered_samples":147,"profiled_samples":660,"pct":22.3,"rn":13},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"CSMD1","altered_samples":145,"profiled_samples":660,"pct":22,"rn":14},{"histology":"Lung Adenocarcinoma","hugo_gene_symbol":"MUC17","altered_samples":136,"profiled_samples":660,"pct":20.6,"rn":15},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"TP53","altered_samples":418,"profiled_samples":484,"pct":86.4,"rn":1},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"TTN","altered_samples":357,"profiled_samples":484,"pct":73.8,"rn":2},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"CSMD3","altered_samples":215,"profiled_samples":484,"pct":44.4,"rn":3},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"RYR2","altered_samples":198,"profiled_samples":484,"pct":40.9,"rn":4},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"MUC16","altered_samples":192,"profiled_samples":484,"pct":39.7,"rn":5},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"LRP1B","altered_samples":171,"profiled_samples":484,"pct":35.3,"rn":6},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"USH2A","altered_samples":166,"profiled_samples":484,"pct":34.3,"rn":7},{"histology":"Lung Squamous Cell Carcinoma","hugo_gene_symbol":"SYNE1","altered_samples":158,"profiled_samples":484,"pct":32.6,"rn":8},{"histology":"Lung Squamous Cell  … (914 more chars)

▶ clickhouse_run_select_query
{
 "query": "
WITH sct AS (
    SELECT sample_unique_id, attribute_value AS histology
    FROM clinical_data_derived
    WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND attribute_name = 'CANCER_TYPE_DETAILED'
),
altered AS (
    SELECT sct.histology, ged.hugo_gene_symbol, COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN sct USING (sample_unique_id)
    WHERE ged.cancer_study_identifier = 'nsclc_tcga_broad_2016'
      AND ged.variant_type = 'mutation'
      AND ged.mutation_status != 'UNCALLED'
      AND ged.off_panel = 0
      AND ged.hugo_gene_symbol IN ('KRAS','EGFR','STK11','KEAP1','NF1','TP53','PIK3CA','NFE2L2','FAT1','PTEN','RB1','CDKN2A','ARID1A','MET','BRAF','U2AF1','SETD2')
    GROUP BY sct.histology, ged.hugo_gene_symbol
),
profiled AS (
    SELECT sct.histology, COUNT(DISTINCT sct.sample_unique_id) AS profiled_samples
    FROM sct
    JOIN (SELECT DISTINCT sample_unique_id FROM sample_to_gene_panel_derived WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND alteration_type = 'MUTATION_EXTENDED') p USING (sample_unique_id)
    GROUP BY sct.histology
)
SELECT a.hugo_gene_symbol, a.histology, a.altered_samples, p.profiled_samples,
       ROUND(a.altered_samples*100.0/p.profiled_samples,1) AS pct
FROM altered a JOIN profiled p USING (histology)
ORDER BY a.hugo_gene_symbol, a.histology
"
}

◀ result
{"rows":[{"hugo_gene_symbol":"ARID1A","histology":"Lung Adenocarcinoma","altered_samples":48,"profiled_samples":660,"pct":7.3},{"hugo_gene_symbol":"ARID1A","histology":"Lung Squamous Cell Carcinoma","altered_samples":32,"profiled_samples":484,"pct":6.6},{"hugo_gene_symbol":"BRAF","histology":"Lung Adenocarcinoma","altered_samples":54,"profiled_samples":660,"pct":8.2},{"hugo_gene_symbol":"BRAF","histology":"Lung Squamous Cell Carcinoma","altered_samples":16,"profiled_samples":484,"pct":3.3},{"hugo_gene_symbol":"CDKN2A","histology":"Lung Adenocarcinoma","altered_samples":27,"profiled_samples":660,"pct":4.1},{"hugo_gene_symbol":"CDKN2A","histology":"Lung Squamous Cell Carcinoma","altered_samples":75,"profiled_samples":484,"pct":15.5},{"hugo_gene_symbol":"EGFR","histology":"Lung Adenocarcinoma","altered_samples":104,"profiled_samples":660,"pct":15.8},{"hugo_gene_symbol":"EGFR","histology":"Lung Squamous Cell Carcinoma","altered_samples":13,"profiled_samples":484,"pct":2.7},{"hugo_gene_symbol":"FAT1","histology":"Lung Adenocarcinoma","altered_samples":73,"profiled_samples":660,"pct":11.1},{"hugo_gene_symbol":"FAT1","histology":"Lung Squamous Cell Carcinoma","altered_samples":69,"profiled_samples":484,"pct":14.3},{"hugo_gene_symbol":"KEAP1","histology":"Lung Adenocarcinoma","altered_samples":110,"profiled_samples":660,"pct":16.7},{"hugo_gene_symbol":"KEAP1","histology":"Lung Squamous Cell Carcinoma","altered_samples":52,"profiled_samples":484,"pct":10.7},{"hugo_gene_symbol":"KRAS","histology":"Lung Adenocarcinoma","altered_samples":214,"profiled_samples":660,"pct":32.4},{"hugo_gene_symbol":"KRAS","histology":"Lung Squamous Cell Carcinoma","altered_samples":8,"profiled_samples":484,"pct":1.7},{"hugo_gene_symbol":"MET","histology":"Lung Adenocarcinoma","altered_samples":28,"profiled_samples":660,"pct":4.2},{"hugo_gene_symbol":"MET","histology":"Lung Squamous Cell Carcinoma","altered_samples":6,"profiled_samples":484,"pct":1.2},{"hugo_gene_symbol":"NF1","histology":"Lung Adenocarcinoma","altered_samples":76,"profiled_samples":660,"pct":11.5},{"hugo_gene_symbol":"NF1","histology":"Lung Squamous Cell Carcinoma","altered_samples":51,"profiled_samples":484,"pct":10.5},{"hugo_gene_symbol":"NFE2L2","histology":"Lung Adenocarcinoma","altered_samples":14,"profiled_samples":660,"pct":2.1},{"hugo_gene_symbol":"NFE2L2","histology":"Lung Squamous Cell Carcinoma","altered_samples":70,"profiled_samples":484,"pct":14.5},{"hugo_gene_symbol":"PIK3CA","histology":"Lung Adenocarcinoma","altered_samples":39,"profiled_samples":660,"pct":5.9},{"hugo_gene_symbol":"PIK3CA","histology":"Lung Squamous Cell Carcinoma","altered_samples":55,"profiled_samples":484,"pct":11.4},{"hugo_gene_symbol":"PTEN","histology":"Lung Adenocarcinoma","altered_samples":11,"profiled_samples":660,"pct":1.7},{"hugo_gene_symbol":"PTEN","histology":"Lung Squamous Cell Carcinoma","altered_samples":56,"profiled_samples":484,"pct":11.6},{"hugo_gene_symbol":"RB1","histology":"Lung Adenocarcinoma","altered_sam … (1017 more chars)

▶ clickhouse_run_select_query
{
 "query": "
WITH sct AS (
    SELECT sample_unique_id, attribute_value AS histology
    FROM clinical_data_derived
    WHERE cancer_study_identifier = 'nsclc_tcga_broad_2016' AND attribute_name = 'CANCER_TYPE_DETAILED'
),
n AS (
  SELECT histology, count(DISTINCT sample_unique_id) AS profiled
  FROM sct
  JOIN (SELECT DISTINCT sample_unique_id FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='nsclc_tcga_broad_2016' AND alteration_type='COPY_NUMBER_ALTERATION') p USING(sample_unique_id)
  GROUP BY histology
),
cna AS (
  SELECT sct.histology, ged.hugo_gene_symbol, ged.cna_alteration, COUNT(DISTINCT ged.sample_unique_id) AS n_altered
  FROM genomic_event_derived ged
  JOIN sct USING (sample_unique_id)
  WHERE ged.cancer_study_identifier = 'nsclc_tcga_broad_2016'
    AND ged.variant_type = 'cna'
    AND ged.cna_alteration IN (2,-2)
    AND ged.hugo_gene_symbol IN ('SOX2','PIK3CA','EGFR','MDM2','CDKN2A','MYC','NKX2-1','TERT','FGFR1')
  GROUP BY sct.histology, ged.hugo_gene_symbol, ged.cna_alteration
)
SELECT cna.hugo_gene_symbol, cna.histology, cna.cna_alteration, cna.n_altered, n.profiled,
       ROUND(cna.n_altered*100.0/n.profiled,1) AS pct
FROM cna JOIN n USING(histology)
ORDER BY hugo_gene_symbol, histology, cna_alteration
"
}

◀ result
{"rows":[{"hugo_gene_symbol":"CDKN2A","histology":"Lung Adenocarcinoma","cna_alteration":-2,"n_altered":112,"profiled":660,"pct":17},{"hugo_gene_symbol":"CDKN2A","histology":"Lung Adenocarcinoma","cna_alteration":2,"n_altered":1,"profiled":660,"pct":0.2},{"hugo_gene_symbol":"CDKN2A","histology":"Lung Squamous Cell Carcinoma","cna_alteration":-2,"n_altered":129,"profiled":484,"pct":26.7},{"hugo_gene_symbol":"CDKN2A","histology":"Lung Squamous Cell Carcinoma","cna_alteration":2,"n_altered":2,"profiled":484,"pct":0.4},{"hugo_gene_symbol":"EGFR","histology":"Lung Adenocarcinoma","cna_alteration":-2,"n_altered":3,"profiled":660,"pct":0.5},{"hugo_gene_symbol":"EGFR","histology":"Lung Adenocarcinoma","cna_alteration":2,"n_altered":36,"profiled":660,"pct":5.5},{"hugo_gene_symbol":"EGFR","histology":"Lung Squamous Cell Carcinoma","cna_alteration":-2,"n_altered":2,"profiled":484,"pct":0.4},{"hugo_gene_symbol":"EGFR","histology":"Lung Squamous Cell Carcinoma","cna_alteration":2,"n_altered":33,"profiled":484,"pct":6.8},{"hugo_gene_symbol":"FGFR1","histology":"Lung Adenocarcinoma","cna_alteration":-2,"n_altered":10,"profiled":660,"pct":1.5},{"hugo_gene_symbol":"FGFR1","histology":"Lung Adenocarcinoma","cna_alteration":2,"n_altered":19,"profiled":660,"pct":2.9},{"hugo_gene_symbol":"FGFR1","histology":"Lung Squamous Cell Carcinoma","cna_alteration":-2,"n_altered":2,"profiled":484,"pct":0.4},{"hugo_gene_symbol":"FGFR1","histology":"Lung Squamous Cell Carcinoma","cna_alteration":2,"n_altered":83,"profiled":484,"pct":17.1},{"hugo_gene_symbol":"MDM2","histology":"Lung Adenocarcinoma","cna_alteration":-2,"n_altered":1,"profiled":660,"pct":0.2},{"hugo_gene_symbol":"MDM2","histology":"Lung Adenocarcinoma","cna_alteration":2,"n_altered":36,"profiled":660,"pct":5.5},{"hugo_gene_symbol":"MDM2","histology":"Lung Squamous Cell Carcinoma","cna_alteration":2,"n_altered":10,"profiled":484,"pct":2.1},{"hugo_gene_symbol":"MYC","histology":"Lung Adenocarcinoma","cna_alteration":2,"n_altered":62,"profiled":660,"pct":9.4},{"hugo_gene_symbol":"MYC","histology":"Lung Squamous Cell Carcinoma","cna_alteration":2,"n_altered":38,"profiled":484,"pct":7.9},{"hugo_gene_symbol":"NKX2-1","histology":"Lung Adenocarcinoma","cna_alteration":2,"n_altered":79,"profiled":660,"pct":12},{"hugo_gene_symbol":"NKX2-1","histology":"Lung Squamous Cell Carcinoma","cna_alteration":-2,"n_altered":3,"profiled":484,"pct":0.6},{"hugo_gene_symbol":"NKX2-1","histology":"Lung Squamous Cell Carcinoma","cna_alteration":2,"n_altered":13,"profiled":484,"pct":2.7},{"hugo_gene_symbol":"PIK3CA","histology":"Lung Adenocarcinoma","cna_alteration":-2,"n_altered":3,"profiled":660,"pct":0.5},{"hugo_gene_symbol":"PIK3CA","histology":"Lung Adenocarcinoma","cna_alteration":2,"n_altered":14,"profiled":660,"pct":2.1},{"hugo_gene_symbol":"PIK3CA","histology":"Lung Squamous Cell Carcinoma","cna_alteration":2,"n_altered":194,"profiled":484,"pct":40.1},{"hugo_gene_symbol":"SOX2","histology":"Lung Adenocarcinoma","cna_alteration":-2," … (546 more chars)

▶ resolve_and_route
{
 "studyIds": [
  "nsclc_tcga_broad_2016"
 ]
}

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"nsclc_tcga_broad_2016","name":"Pan-Lung Cancer (TCGA, Nat Genet 2016)","sampleCount":1144,"studyViewUrl":"https://www.cbioportal.org/study?id=nsclc_tcga_broad_2016","metadata":{"clinicalAttributeIds":["AGE","AGE_AT_SURGERY","CANCER_TYPE","CANCER_TYPE_DETAILED","DAYS_TO_DEATH","DAYS_TO_LAST_FOLLOWUP","FRACTION_GENOME_ALTERED","HISTORY_NEOADJUVANT_TRTYN","HISTORY_OTHER_MALIGNANCY","MUTATION_COUNT","M_STAGE","N_STAGE","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","SAMPLE_COUNT","SEX","SMOKING_HISTORY","SMOKING_PACK_YEARS","SOMATIC_STATUS","STAGE","TMB_NONSYNONYMOUS","T_STAGE"],"molecularProfileIds":["nsclc_tcga_broad_2016_cna","nsclc_tcga_broad_2016_mutations","nsclc_tcga_broad_2016_structural_variants"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations"]}}]}}

▶ navigate_to_group_comparison
{
 "studyIds": [
  "nsclc_tcga_broad_2016"
 ],
 "groups": [
  {
   "name": "Lung Adenocarcinoma",
   "studyViewFilter": {
    "clinicalDataFilters": [
     {
      "attributeId": "CANCER_TYPE_DETAILED",
      "values": [
       {
        "value": "Lung Adenocarcinoma"
       }
      ]
     }
    ]
   }
  },
  {
   "name": "Lung Squamous Cell Carcinoma",
   "studyViewFilter": {
    "clinicalDataFilters": [
     {
      "attributeId": "CANCER_TYPE_DETAILED",
      "values": [
       {
        "value": "Lung Squamous Cell Carcinoma"
       }
      ]
     }
    ]
   }
  }
 ],
 "tab": "alterations"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/comparison/alterations?comparisonId=6ab4329ee497aad53593be0b","url":"https://www.cbioportal.org/comparison/alterations?comparisonId=6ab4329ee497aad53593be0b","data":{"description":"Group comparison (2 custom groups)","studies":["nsclc_tcga_broad_2016"],"totalGroups":2,"groups":[{"name":"Lung Adenocarcinoma","sampleCount":660},{"name":"Lung Squamous Cell Carcinoma","sampleCount":484}],"studyViewUrl":"https://www.cbioportal.org/study?id=nsclc_tcga_broad_2016","groupUrls":[{"groupName":"Lung Adenocarcinoma","url":"https://www.cbioportal.org/study?id=nsclc_tcga_broad_2016#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22CANCER_TYPE_DETAILED%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Lung%20Adenocarcinoma%22%7D%5D%7D%5D%7D"},{"groupName":"Lung Squamous Cell Carcinoma","url":"https://www.cbioportal.org/study?id=nsclc_tcga_broad_2016#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22CANCER_TYPE_DETAILED%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Lung%20Squamous%20Cell%20Carcinoma%22%7D%5D%7D%5D%7D"}],"pageDescription":"Comparison of genomic alteration rates across Lung Adenocarcinoma (660) vs Lung Squamous Cell Carcinoma (484)."}}

▶ navigate_to_results_view
{
 "studyIds": [
  "nsclc_tcga_broad_2016"
 ],
 "genes": [
  "TP53",
  "KRAS",
  "EGFR",
  "STK11",
  "KEAP1",
  "NFE2L2",
  "PIK3CA",
  "SOX2",
  "CDKN2A",
  "PTEN",
  "NF1"
 ],
 "tab": "oncoprint",
 "oncoprintClinicalTracks": [
  "CANCER_TYPE_DETAILED",
  "SMOKING_HISTORY"
 ]
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/results/oncoprint?cancer_study_list=nsclc_tcga_broad_2016&gene_list=TP53%0AKRAS%0AEGFR%0ASTK11%0AKEAP1%0ANFE2L2%0APIK3CA%0ASOX2%0ACDKN2A%0APTEN%0ANF1&case_set_id=nsclc_tcga_broad_2016_all&clinicallist=CANCER_TYPE_DETAILED%2CSMOKING_HISTORY","url":"https://www.cbioportal.org/results/oncoprint?cancer_study_list=nsclc_tcga_broad_2016&gene_list=TP53%0AKRAS%0AEGFR%0ASTK11%0AKEAP1%0ANFE2L2%0APIK3CA%0ASOX2%0ACDKN2A%0APTEN%0ANF1&case_set_id=nsclc_tcga_broad_2016_all&clinicallist=CANCER_TYPE_DETAILED%2CSMOKING_HISTORY","data":{"studyIds":["nsclc_tcga_broad_2016"],"studies":[{"studyId":"nsclc_tcga_broad_2016","name":"Pan-Lung Cancer (TCGA, Nat Genet 2016)","sampleCount":1144}],"genes":["TP53","KRAS","EGFR","STK11","KEAP1","NFE2L2","PIK3CA","SOX2","CDKN2A","PTEN","NF1"],"caseSetId":"nsclc_tcga_broad_2016_all","pageDescription":"Visual summary of alterations in queried genes across samples."}}

═ answer (success)
## Pan-Lung Cancer (TCGA, *Nat Genet* 2016) — LUAD vs LUSC genomic comparison

Study: [Pan-Lung Cancer (TCGA, Nat Genet 2016)](https://www.cbioportal.org/study/summary?id=nsclc_tcga_broad_2016), 1,144 samples total — **660 Lung Adenocarcinoma (LUAD)** and **484 Lung Squamous Cell Carcinoma (LUSC)** samples (`CANCER_TYPE_DETAILED`). All frequencies below are altered/profiled samples (sample-level).

### Mutational differences

| Gene | LUAD | LUSC | Pattern |
|---|---|---|---|
| **TP53** | 357/660 = 54.1% | 418/484 = **86.4%** | Near-universal in LUSC, majority but lower in LUAD |
| **KRAS** | 214/660 = **32.4%** | 8/484 = 1.7% | Essentially LUAD-restricted |
| **EGFR** | 104/660 = **15.8%** | 13/484 = 2.7% | LUAD-enriched (classic oncogenic driver) |
| **STK11 (LKB1)** | 104/660 = **15.8%** | 7/484 = 1.4% | LUAD-restricted |
| **KEAP1** | 110/660 = **16.7%** | 52/484 = 10.7% | More frequent in LUAD |
| **BRAF** | 54/660 = 8.2% | 16/484 = 3.3% | LUAD-enriched |
| **NFE2L2** | 14/660 = 2.1% | 70/484 = **14.5%** | LUSC-enriched (Keap1-Nrf2 pathway, opposite arm from KEAP1) |
| **PIK3CA** (mutation) | 39/660 = 5.9% | 55/484 = **11.4%** | LUSC-enriched |
| **PTEN** | 11/660 = 1.7% | 56/484 = **11.6%** | LUSC-enriched |
| **CDKN2A** (mutation) | 27/660 = 4.1% | 75/484 = **15.5%** | LUSC-enriched |
| **FAT1** | 73/660 = 11.1% | 69/484 = 14.3% | Similar, slightly higher LUSC |
| **NF1** | 76/660 = 11.5% | 51/484 = 10.5% | Similar |
| **RB1** | 38/660 = 5.8% | 35/484 = 7.2% | Similar |

Top overall mutated genes per histology (beyond TP53) are largely shared "long gene" passengers (TTN, MUC16, CSMD3, RYR2, LRP1B, USH2A) reflecting high tumor mutation burden in both — this study cohort is dominated by smoking-associated tumors.

### Copy-number differences

| Gene | LUAD AMP | LUSC AMP | Pattern |
|---|---|---|---|
| **SOX2** | 15/660 = 2.3% | 205/484 = **42.4%** | Hallmark LUSC-defining amplicon (3q26) |
| **PIK3CA** (amp) | 14/660 = 2.1% | 194/484 = **40.1%** | Co-amplified with SOX2 (same 3q26 region) |
| **FGFR1** | 19/660 = 2.9% | 83/484 = **17.1%** | LUSC-enriched amplicon |
| **NKX2-1 (TTF-1)** | 79/660 = **12.0%** | 13/484 = 2.7% | LUAD-enriched (lineage-defining transcription factor) |
| **EGFR** (amp) | 36/660 = 5.5% | 33/484 = 6.8% | Similar |
| **MDM2** | 36/660 = 5.5% | 10/484 = 2.1% | More frequent in LUAD |
| **MYC** | 62/660 = 9.4% | 38/484 = 7.9% | Similar |
| **CDKN2A** (deep deletion) | 112/660 = 17.0% | 129/484 = **26.7%** | More frequent in LUSC |
| **TERT** (amp) | 98/660 = 14.8% | 73/484 = 15.1% | Similar |

### Summary of key distinctions
- **LUAD** is defined by RTK/RAS/RAF pathway drivers — **KRAS**, **EGFR**, **BRAF**, **STK11**, and amplification of the lineage transcription factor **NKX2-1** — consistent with an adenocarcinoma differentiation program.
- **LUSC** is defined by near-universal **TP53** mutation, the **SOX2/PIK3CA** 3q26 co-amplification (a squamous-lineage hallmark), frequent **PTEN** loss, **CDKN2A** inactivation (both mutation and deletion), and **NFE2L2/KEAP1** pathway dysregulation via the NFE2L2 mutation arm (versus KEAP1 loss-of-function in LUAD).

These frequency differences are descriptive counts, not statistical enrichment tests — to formally test significance (e.g., Fisher's exact per gene) or view survival differences by histology, use the Group Comparison alterations tab below, which also has a Survival sub-tab.

[View LUAD vs LUSC Alteration Comparison](https://www.cbioportal.org/comparison/alterations?comparisonId=6ab4329ee497aad53593be0b) — group comparison across all genes/CNAs, with statistical enrichment tests computed by the portal.

[View OncoPrint of Key Driver Genes](https://www.cbioportal.org/results/oncoprint?cancer_study_list=nsclc_tcga_broad_2016&gene_list=TP53%0AKRAS%0AEGFR%0ASTK11%0AKEAP1%0ANFE2L2%0APIK3CA%0ASOX2%0ACDKN2A%0APTEN%0ANF1&case_set_id=nsclc_tcga_broad_2016_all&clinicallist=CANCER_TYPE_DETAILED%2CSMOKING_HISTORY) — visualizes co-mutation patterns with histology and smoking history annotated as tracks.

[Study Overview](https://www.cbioportal.org/study/summary?id=nsclc_tcga_broad_2016) — full cohort clinical/genomic summary.