Q (sonnet-5.5): Can you explore the difference in mutation frequency between left-sided and right-sided CRC?

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

▶ list_studies
{
 "search": "colorectal",
 "limit": 50
}

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

◀ result
{"result":[{"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":"crc_msk_2026","name":"Colorectal Adenocarcinoma (MSK, 2026)","type_of_cancer_id":"read","sample_count":7237,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=crc_msk_2026"},{"cancer_study_identifier":"crc_eo_2020","name":"Colorectal Cancer (MSK, JNCI 2021)","type_of_cancer_id":"bowel","sample_count":1516,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=crc_eo_2020"},{"cancer_study_identifier":"crc_msk_2017","name":"Metastatic Colorectal Cancer (MSK, Cancer Cell 2018)","type_of_cancer_id":"coadread","sample_count":1134,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=crc_msk_2017"},{"cancer_study_identifier":"crc_sysucc_2022","name":"Colorectal Cancer- ChangKang Project (SYSUCC, Nat Commun 2022)","type_of_cancer_id":"coadread","sample_count":1015,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=crc_sysucc_2022"},{"cancer_study_identifier":"coadread_tcga","name":"Colorectal Adenocarcinoma (TCGA, Firehose Legacy)","type_of_cancer_id":"coadread","sample_count":640,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=coadread_tcga"},{"cancer_study_identifier":"coadread_dfci_2016","name":"Colorectal Adenocarcinoma (DFCI, Cell Reports 2016)","type_of_cancer_id":"coadread","sample_count":619,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=coadread_dfci_2016"},{"cancer_study_identifier":"coadread_tcga_pan_can_atlas_2018","name":"Colorectal Adenocarcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"coadread","sample_count":594,"has_guide":true,"url":"https://www.cbioportal.org/study/summary?id=coadread_tcga_pan_can_atlas_2018"},{"cancer_study_identifier":"crc_apc_impact_2020","name":"Colorectal Cancer (MSK, Gastroenterology 2020)","type_of_cancer_id":"coadread","sample_count":471,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=crc_apc_impact_2020"},{"cancer_study_identifier":"coadread_tcga_pub","name":"Colorectal Adenocarcinoma (TCGA, Nature 2012)","type_of_cancer_id":"coadread","sample_count":276,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=coadread_tcga_pub"},{"cancer_study_identifier":"bowel_colitis_msk_2022","name":"Colorectal Adenocarcinoma (MSK, Nat Commun 2022)","type_of_cancer_id":"bowel","sample_count":179,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=bowel_colitis_msk_2022"},{"cancer_study_identifier":"coadread_cass_2020","name":"Colorectal Cancer (CAS Shanghai, Cancer Cell 2020)","type_of_cancer_id":"coadread","sample_count":146,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=coadread_cass_2020"},{"cancer_study_identifier":"coadread_mskcc","name":"Colorectal Adenocarcinoma Triplets (MSK, Geno … (2148 more chars)

▶ get_study_guide
{
 "study_id": "coadread_tcga_pan_can_atlas_2018"
}

◀ result
# Colorectal Adenocarcinoma (TCGA, PanCancer Atlas)

**Study ID:** `coadread_tcga_pan_can_atlas_2018`

See `_tcga_pancan_template.md` for common TCGA clinical attributes.

## Study-Specific Attributes

### Microsatellite Instability (MSI)
There is no `MSI_STATUS` attribute. Three attributes carry MSI (594 patients, one sample each):

| Attribute | Definition | MSI-high count |
|-----------|------------|----------------|
| `SUBTYPE` | TCGA molecular classification: `COAD_MSI` 60 + `READ_MSI` 3 | **63** |
| `MSI_SENSOR_SCORE` | MSIsensor score ≥10 (indeterminate 4–10: 10 more) | 78 of 584 scored |
| `MSI_SCORE_MANTIS` | MANTIS score >0.4 (>0.6 = MSI: 67; 0.4–0.6 indeterminate) | 89 of 557 scored |

**For "MSI-high" questions, use `SUBTYPE` IN (`COAD_MSI`, `READ_MSI`)** (the TCGA molecular classification) and state which definition you used; mention the score-based alternatives if the counts matter. Do not switch to `coadread_tcga_pub` to find MSI; this study has it.
```sql
SELECT count(DISTINCT patient_unique_id) AS msi_patients   -- 63
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'SUBTYPE' AND attribute_value IN ('COAD_MSI', 'READ_MSI');
```

### Molecular Classification (`SUBTYPE`, patients)
`COAD_CIN` 226, `READ_CIN` 102, `COAD_MSI` 60, `COAD_GS` 49, `READ_GS` 9, `COAD_POLE` 6, `READ_POLE` 4, `READ_MSI` 3, blank 135.
- **Hypermutated**: no `HYPERMUTATED` attribute. Use `SUBTYPE` MSI + POLE (73 patients), or `TMB_NONSYNONYMOUS` ≥10 (83 samples; all MSI and POLE tumors exceed it).
- **CMS (consensus molecular subtypes)** are not available in this study.

### Anatomic Location
There is no `TUMOR_LOCATION` attribute.
- Colon vs rectum: `TUMOR_TISSUE_SITE` — Colon 436, Rectum 152, blank 6.
- Subsite (for left vs right): `ICD_O_3_SITE` — right: C18.0 cecum 81, C18.2 ascending 100, C18.3 hepatic flexure 10; transverse C18.4 20; left: C18.5 splenic flexure 5, C18.6 descending 16, C18.7 sigmoid 106, C19.9 rectosigmoid 72, C20.9 rectum 81; C18.9 colon NOS 97 (side unknown).

## Key Genes
| Gene | Frequency | Clinical Relevance |
|------|-----------|-------------------|
| APC | ~80% | Initiating event in most CRC |
| TP53 | ~55% | Progression marker |
| KRAS | ~40% | Predicts anti-EGFR resistance |
| PIK3CA | ~15% | May predict aspirin benefit |
| BRAF | ~10% | V600E poor prognosis (MSS context) |
| SMAD4 | ~10% | TGF-β pathway |

## Notes
- MSI-H tumors respond well to immunotherapy
- KRAS/NRAS mutations contraindicate anti-EGFR therapy
- BRAF V600E has different prognosis in MSI-H vs MSS context
- Left vs right-sided tumors have different biology and outcomes


▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, attribute_name, count() n FROM clinical_data_derived WHERE cancer_study_identifier IN ('crc_msk_2026','crc_msk_2017','coadread_tcga_pan_can_atlas_2018','crc_eo_2020','crc_sysucc_2022','coadread_tcga','coadread_dfci_2016') AND (lower(attribute_name) LIKE '%side%' OR lower(attribute_name) LIKE '%site%' OR lower(attribute_name) LIKE '%locat%' OR lower(attribute_name) LIKE '%primary%') GROUP BY 1,2 ORDER BY 1,2"
}

◀ result
{"rows":[{"cancer_study_identifier":"coadread_dfci_2016","attribute_name":"PRIMARY_TUMOR_GRADE","n":619},{"cancer_study_identifier":"coadread_dfci_2016","attribute_name":"TUMOR_SITE","n":619},{"cancer_study_identifier":"coadread_tcga","attribute_name":"ICD_O_3_SITE","n":636},{"cancer_study_identifier":"coadread_tcga","attribute_name":"PRIMARY_SITE_PATIENT","n":636},{"cancer_study_identifier":"coadread_tcga","attribute_name":"SITE_OF_TUMOR_TISSUE","n":636},{"cancer_study_identifier":"coadread_tcga","attribute_name":"TISSUE_SOURCE_SITE","n":636},{"cancer_study_identifier":"coadread_tcga_pan_can_atlas_2018","attribute_name":"ICD_O_3_SITE","n":594},{"cancer_study_identifier":"coadread_tcga_pan_can_atlas_2018","attribute_name":"PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT","n":594},{"cancer_study_identifier":"coadread_tcga_pan_can_atlas_2018","attribute_name":"TISSUE_SOURCE_SITE","n":594},{"cancer_study_identifier":"coadread_tcga_pan_can_atlas_2018","attribute_name":"TISSUE_SOURCE_SITE_CODE","n":594},{"cancer_study_identifier":"coadread_tcga_pan_can_atlas_2018","attribute_name":"TUMOR_TISSUE_SITE","n":594},{"cancer_study_identifier":"crc_eo_2020","attribute_name":"METASTATIC_SITE","n":1516},{"cancer_study_identifier":"crc_eo_2020","attribute_name":"PRIMARY_TUMOR_LOCATION","n":1516},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"FIRST_SITE_OF_METS","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASECTOMY_SITE","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_BONE","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_BRAIN","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_GYN","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_LIVER","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_LN","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_LUNG","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_PELVIS","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTASES_SITE_FIRST_PERITONEUM_OMENTUM_ABDOMEN","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"METASTATIC_BIOPSY_SITE","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"OTHER_SITES_OF_METS","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","n":1134},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_TUMOR_LOCATION","n":1134},{"cancer_study_identifier":"crc_msk_2026","attribute_name":"METASTATIC_SITE","n":7237},{"cancer_study_identifier":"crc_msk_2026","attribute_name":"PRIMARY_SITE","n":7237},{"cancer_study_identifier":"crc_sysucc_2022","attribute_name":"PRIMARY_TUMOR_LOCATION","n":1015},{"cancer_study_identifier":"crc_sysucc_2022","attribute_name":"TUMOR_SITE","n":1015}] … (1 more chars)

▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, attribute_name, attribute_value, count() n, uniqExact(patient_unique_id) p FROM clinical_data_derived WHERE cancer_study_identifier IN ('crc_msk_2026','crc_msk_2017','crc_eo_2020','crc_sysucc_2022') AND attribute_name IN ('PRIMARY_SITE','PRIMARY_TUMOR_LOCATION') GROUP BY 1,2,3 ORDER BY 1,2,n DESC",
 "max_rows": 200
}

▶ get_study_guide
{
 "study_id": "crc_msk_2026"
}

◀ result
{"rows":[{"cancer_study_identifier":"crc_eo_2020","attribute_name":"PRIMARY_TUMOR_LOCATION","attribute_value":"Left","n":664,"p":664},{"cancer_study_identifier":"crc_eo_2020","attribute_name":"PRIMARY_TUMOR_LOCATION","attribute_value":"Rectum","n":411,"p":411},{"cancer_study_identifier":"crc_eo_2020","attribute_name":"PRIMARY_TUMOR_LOCATION","attribute_value":"Right","n":374,"p":374},{"cancer_study_identifier":"crc_eo_2020","attribute_name":"PRIMARY_TUMOR_LOCATION","n":67,"p":67},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Sigmoid Colon","n":296,"p":286},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Rectum","n":261,"p":255},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Cecum","n":154,"p":151},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Rectosigmoid","n":119,"p":118},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Ascending Colon","n":116,"p":108},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Descending Colon","n":61,"p":61},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Transverse Colon","n":54,"p":52},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Hepatic Flexure","n":36,"p":34},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Splenic Flexure","n":18,"p":17},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","n":8,"p":8},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Colon","n":7,"p":7},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Left Colon","n":3,"p":3},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_SITE","attribute_value":"Proximal Colon","n":1,"p":1},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_TUMOR_LOCATION","attribute_value":"Left","n":779,"p":760},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_TUMOR_LOCATION","attribute_value":"Right","n":341,"p":326},{"cancer_study_identifier":"crc_msk_2017","attribute_name":"PRIMARY_TUMOR_LOCATION","n":14,"p":14},{"cancer_study_identifier":"crc_msk_2026","attribute_name":"PRIMARY_SITE","attribute_value":"Colon","n":3430,"p":3286},{"cancer_study_identifier":"crc_msk_2026","attribute_name":"PRIMARY_SITE","attribute_value":"Rectum","n":1719,"p":1643},{"cancer_study_identifier":"crc_msk_2026","attribute_name":"PRIMARY_SITE","attribute_value":"Sigmoid Colon","n":646,"p":625},{"cancer_study_identifier":"crc_msk_2026","attribute_name":"PRIMARY_SITE","attribute_value":"Ascending Colon","n":370,"p":360},{"cancer_study_identifier":"crc_msk_2026","attribute_name":"PRIMARY_SITE","attribute_value":"Cecum","n":332,"p":327},{" … (1985 more chars)

◀ result
# Study Guide: Colorectal Adenocarcinoma (MSK, 2026)

**Study ID:** `crc_msk_2026`
**Cancer Type:** read
**Description:** Targeted sequencing of 7,237 colorectal clinical cases and their matched normals via MSK-IMPACT.

## Cohort Statistics
- **Patients:** 6,789
- **Samples:** 7,238

## Available Data Types

- **COPY_NUMBER_ALTERATION**: Copy Number Alterations (MSK-IMPACT, HEME)
- **MUTATION_EXTENDED**: Mutations
- **STRUCTURAL_VARIANT**: Structural Variants

## Gene Panels

- **IMPACT468**: 3,331 samples
- **IMPACT505**: 2,789 samples
- **IMPACT410**: 915 samples
- **IMPACT341**: 202 samples

## Available Clinical Attributes

| Attribute | Samples with Data |
|-----------|------------------|
| CANCER_TYPE_DETAILED | 7,237 |
| MSI_SCORE | 7,237 |
| MUTATION_COUNT | 7,237 |
| SOMATIC_STATUS | 7,237 |
| FRACTION_GENOME_ALTERED | 7,237 |
| TMB_NONSYNONYMOUS | 7,237 |
| SAMPLE_CLASS | 7,237 |
| CANCER_TYPE | 7,237 |
| SAMPLE_TYPE | 7,237 |
| PRIMARY_SITE | 7,237 |
| TUMOR_PURITY | 7,237 |
| ONCOTREE_CODE | 7,237 |
| GENE_PANEL | 7,237 |
| SAMPLE_COVERAGE | 7,237 |
| METASTATIC_SITE | 7,237 |
| MSI_TYPE | 7,237 |
| OS_STATUS | 1 |
| RACE | 1 |
| ETHNICITY | 1 |
| OS_MONTHS | 1 |

## Top Mutated Genes

| Gene | Altered Samples |
|------|----------------|
| APC | 5,400 |
| TP53 | 5,295 |
| KRAS | 3,096 |
| PIK3CA | 1,486 |
| FBXW7 | 1,127 |
| TCF7L2 | 1,088 |
| SMAD4 | 1,078 |
| ARID1A | 912 |
| SOX9 | 869 |
| BRAF | 813 |

## Sample Types

- **Primary**: 5,138 samples
- **Metastasis**: 2,045 samples
- **Local Recurrence**: 36 samples
- **Unknown**: 18 samples

## Query Tips for crc_msk_2026

```sql
-- Get all samples in this study
SELECT DISTINCT sample_unique_id, patient_unique_id
FROM clinical_data_derived
WHERE cancer_study_identifier = 'crc_msk_2026';

-- Get mutations for a specific gene
SELECT sample_unique_id, hugo_gene_symbol, mutation_variant, mutation_type
FROM genomic_event_derived
WHERE cancer_study_identifier = 'crc_msk_2026'
    AND hugo_gene_symbol = 'TP53'
    AND variant_type = 'mutation';

-- Get clinical data for specific attributes
SELECT sample_unique_id, attribute_name, attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'crc_msk_2026'
    AND attribute_name IN ('CANCER_TYPE', 'SAMPLE_TYPE', 'OS_MONTHS');
```


▶ clickhouse_run_select_query
{
 "query": "SELECT attribute_name, attribute_value, count() n FROM clinical_data_derived WHERE cancer_study_identifier='crc_msk_2026' AND attribute_name IN ('MSI_TYPE','SAMPLE_TYPE') GROUP BY 1,2 ORDER BY 1,3 DESC"
}

◀ result
{"rows":[{"attribute_name":"MSI_TYPE","attribute_value":"Stable","n":6114},{"attribute_name":"MSI_TYPE","attribute_value":"Instable","n":770},{"attribute_name":"MSI_TYPE","attribute_value":"Indeterminate","n":281},{"attribute_name":"MSI_TYPE","attribute_value":"Do not report","n":52},{"attribute_name":"MSI_TYPE","n":20},{"attribute_name":"SAMPLE_TYPE","attribute_value":"Primary","n":5138},{"attribute_name":"SAMPLE_TYPE","attribute_value":"Metastasis","n":2045},{"attribute_name":"SAMPLE_TYPE","attribute_value":"Local Recurrence","n":36},{"attribute_name":"SAMPLE_TYPE","attribute_value":"Unknown","n":18}]}

▶ clickhouse_run_select_query
{
 "query": "WITH s AS (
 SELECT a.sample_unique_id sid,
  multiIf(lower(a.v) IN ('cecum','ascending colon','hepatic flexure'),'Right',
          lower(a.v) IN ('splenic flexure','descending colon','sigmoid colon','rectosigmoid colon','rectum'),'Left','Other') side,
  m.v msi
 FROM (SELECT sample_unique_id, attribute_value v FROM clinical_data_derived WHERE cancer_study_identifier='crc_msk_2026' AND attribute_name='PRIMARY_SITE') a
 JOIN (SELECT sample_unique_id, attribute_value v FROM clinical_data_derived WHERE cancer_study_identifier='crc_msk_2026' AND attribute_name='SAMPLE_TYPE') t ON a.sample_unique_id=t.sample_unique_id AND t.v='Primary'
 JOIN (SELECT sample_unique_id, attribute_value v FROM clinical_data_derived WHERE cancer_study_identifier='crc_msk_2026' AND attribute_name='MSI_TYPE') m ON a.sample_unique_id=m.sample_unique_id
),
genes AS (SELECT arrayJoin(['APC','TP53','KRAS','BRAF','PIK3CA','SMAD4','FBXW7','NRAS','ERBB2','ARID1A','TCF7L2','SOX9','RNF43','PTEN','CTNNB1','ATM','KMT2D','GNAS']) g),
prof AS (SELECT sample_unique_id sid, hugo_gene_symbol g FROM mutation_panel_gene_coverage WHERE cancer_study_identifier='crc_msk_2026' AND hugo_gene_symbol IN (SELECT g FROM genes)),
alt AS (SELECT DISTINCT sample_unique_id sid, hugo_gene_symbol g FROM genomic_event_derived WHERE cancer_study_identifier='crc_msk_2026' AND variant_type='mutation' AND mutation_status!='UNCALLED' AND off_panel=0 AND hugo_gene_symbol IN (SELECT g FROM genes))
SELECT p.g gene, s.side,
 if(s.msi='Stable','MSS','all') dummy,
 count() profiled, countIf((p.sid, p.g) IN (SELECT sid, g FROM alt)) altered,
 round(altered*100/profiled,1) pct
FROM prof p JOIN s ON p.sid=s.sid
WHERE s.side IN ('Left','Right')
GROUP BY gene, s.side, dummy
ORDER BY gene, dummy, s.side",
 "max_rows": 200
}

◀ result
{"rows":[{"gene":"APC","side":"Left","dummy":"MSS","profiled":1989,"altered":1577,"pct":79.3},{"gene":"APC","side":"Right","dummy":"MSS","profiled":406,"altered":295,"pct":72.7},{"gene":"APC","side":"Left","dummy":"all","profiled":191,"altered":137,"pct":71.7},{"gene":"APC","side":"Right","dummy":"all","profiled":181,"altered":83,"pct":45.9},{"gene":"ARID1A","side":"Left","dummy":"MSS","profiled":1989,"altered":136,"pct":6.8},{"gene":"ARID1A","side":"Right","dummy":"MSS","profiled":406,"altered":22,"pct":5.4},{"gene":"ARID1A","side":"Left","dummy":"all","profiled":191,"altered":100,"pct":52.4},{"gene":"ARID1A","side":"Right","dummy":"all","profiled":181,"altered":107,"pct":59.1},{"gene":"ATM","side":"Left","dummy":"MSS","profiled":1989,"altered":112,"pct":5.6},{"gene":"ATM","side":"Right","dummy":"MSS","profiled":406,"altered":39,"pct":9.6},{"gene":"ATM","side":"Left","dummy":"all","profiled":191,"altered":51,"pct":26.7},{"gene":"ATM","side":"Right","dummy":"all","profiled":181,"altered":51,"pct":28.2},{"gene":"BRAF","side":"Left","dummy":"MSS","profiled":1989,"altered":102,"pct":5.1},{"gene":"BRAF","side":"Right","dummy":"MSS","profiled":406,"altered":61,"pct":15},{"gene":"BRAF","side":"Left","dummy":"all","profiled":191,"altered":32,"pct":16.8},{"gene":"BRAF","side":"Right","dummy":"all","profiled":181,"altered":95,"pct":52.5},{"gene":"CTNNB1","side":"Left","dummy":"MSS","profiled":1989,"altered":89,"pct":4.5},{"gene":"CTNNB1","side":"Right","dummy":"MSS","profiled":406,"altered":27,"pct":6.7},{"gene":"CTNNB1","side":"Left","dummy":"all","profiled":191,"altered":23,"pct":12},{"gene":"CTNNB1","side":"Right","dummy":"all","profiled":181,"altered":39,"pct":21.5},{"gene":"ERBB2","side":"Left","dummy":"MSS","profiled":1989,"altered":47,"pct":2.4},{"gene":"ERBB2","side":"Right","dummy":"MSS","profiled":406,"altered":20,"pct":4.9},{"gene":"ERBB2","side":"Left","dummy":"all","profiled":191,"altered":50,"pct":26.2},{"gene":"ERBB2","side":"Right","dummy":"all","profiled":181,"altered":34,"pct":18.8},{"gene":"FBXW7","side":"Left","dummy":"MSS","profiled":1989,"altered":305,"pct":15.3},{"gene":"FBXW7","side":"Right","dummy":"MSS","profiled":406,"altered":46,"pct":11.3},{"gene":"FBXW7","side":"Left","dummy":"all","profiled":191,"altered":73,"pct":38.2},{"gene":"FBXW7","side":"Right","dummy":"all","profiled":181,"altered":65,"pct":35.9},{"gene":"GNAS","side":"Left","dummy":"MSS","profiled":1989,"altered":35,"pct":1.8},{"gene":"GNAS","side":"Right","dummy":"MSS","profiled":406,"altered":19,"pct":4.7},{"gene":"GNAS","side":"Left","dummy":"all","profiled":191,"altered":25,"pct":13.1},{"gene":"GNAS","side":"Right","dummy":"all","profiled":181,"altered":23,"pct":12.7},{"gene":"KMT2D","side":"Left","dummy":"MSS","profiled":1989,"altered":58,"pct":2.9},{"gene":"KMT2D","side":"Right","dummy":"MSS","profiled":406,"altered":22,"pct":5.4},{"gene":"KMT2D","side":"Left","dummy":"all","profiled":191,"altered":89,"pct":46.6},{"gene":"KMT2D","side":"Right","dummy":"all","pr … (3074 more chars)

═ answer (success)
I ran the comparison in MSK's colorectal cohort, so this is a quick note on what I did and then the results.

# Left- vs right-sided colorectal cancer: mutation frequency

**Cohort:** [Colorectal Adenocarcinoma (MSK, 2026)](https://www.cbioportal.org/study/summary?id=crc_msk_2026), MSK-IMPACT targeted sequencing. You didn't name a study, so I picked the largest CRC cohort with primary-site detail.

**Unit:** primary-tumor samples, each with a known left or right site.
- **Right:** cecum, ascending colon, hepatic flexure.
- **Left:** splenic flexure, descending colon, sigmoid, rectosigmoid, rectum.
- **Excluded:** transverse colon and unspecified "colon" or "rectum-only-unclear" records. Metastases were also excluded.

**Denominator:** samples whose panel covers the gene.

**Cohort size:** 2,180 left-sided and 587 right-sided samples.

## Frequencies, stratified by MSI status
Right-sided tumors are much more often MSI-high, and MSI drives many of these differences. The table therefore splits samples by microsatellite status.

- **MSS** means MSI_TYPE "Stable".
- **Non-MSS** is everything else: mostly MSI-instable, plus indeterminate and not-reported samples.

| Gene | MSS left (n=1,989) | MSS right (n=406) | Non-MSS left (n=191) | Non-MSS right (n=181) |
|---|---|---|---|---|
| APC | 79.3% | 72.7% | 71.7% | 45.9% |
| TP53 | 81.3% | 63.3% | 44.0% | 36.5% |
| KRAS | 39.4% | 64.3% | 47.6% | 24.9% |
| BRAF | 5.1% | 15.0% | 16.8% | 52.5% |
| PIK3CA | 14.1% | 32.8% | 38.2% | 41.4% |
| SMAD4 | 12.8% | 15.8% | 12.6% | 9.4% |
| FBXW7 | 15.3% | 11.3% | 38.2% | 35.9% |
| NRAS | 4.2% | 3.7% | 4.7% | 3.9% |
| RNF43 | 2.6% | 7.6% | 35.1% | 70.7% |
| PTEN | 2.8% | 9.1% | 24.1% | 21.5% |
| ERBB2 | 2.4% | 4.9% | 26.2% | 18.8% |
| TCF7L2 | 12.4% | 8.2% | 48.9% | 37.2% |

## Pooled across MSI status (all left vs all right)

| Gene | Left | Right |
|---|---|---|
| APC | 78.6% | 64.4% |
| TP53 | 78.1% | 55.0% |
| KRAS | 40.1% | 52.1% |
| BRAF | 6.1% | 26.6% |
| PIK3CA | 16.2% | 35.4% |

## What the counts show
- **Higher on the right:** BRAF, PIK3CA, RNF43, PTEN and KRAS. BRAF and PIK3CA are higher on the right in both MSI strata. KRAS is higher on the right in the MSS group only. In the non-MSS group it is higher on the left (47.6% vs 24.9%).
- **Higher on the left:** APC and TP53. TP53 is the clearest, at 81% vs 63% in MSS tumors.
- **Similar on both sides:** NRAS and SMAD4.
- **MSI confounding:** the pooled numbers mix MSI status with sidedness. The MSS columns are the cleaner comparison.
- **Hypermutation:** the non-MSS groups are hypermutated, which inflates frequencies of large genes such as ARID1A, KMT2D and ATM. Those groups are also small (n≈190 each), so read them cautiously.

## Caveats
- **No statistical tests:** I can't compute p-values here, so these are descriptive frequencies only. I'm not claiming any difference is significant. Fisher's exact test (with FDR correction across genes) is the appropriate test.
- **Non-MSS is a mixed group:** it also includes indeterminate and not-reported MSI samples.
- **Sidedness is my mapping:** I derived it from the recorded primary site. The transverse colon and unspecified sites are left out.
- **Multiple samples per patient:** some patients contribute more than one sample.
- **Other cohorts:** `crc_msk_2017`, `crc_eo_2020` and `crc_sysucc_2022` have explicit Left/Right labels. TCGA PanCancer Atlas (`coadread_tcga_pan_can_atlas_2018`) has subsite in `ICD_O_3_SITE`. I can rerun this in any of them to check whether the pattern replicates.

To get p-values, use Group Comparison in [the study](https://www.cbioportal.org/study/summary?id=crc_msk_2026) with groups defined by Primary Site. Alternatively, I can give you the 2×2 counts for any gene to run through Fisher's test in R or Python.