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

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

▶ search_oncotree
{
 "search_term": "colorectal cancer"
}

◀ 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 … (35166 more chars)

◀ result
{"result":[{"code":"CAIS","name":"Colon Adenocarcinoma In Situ","score":65,"mainType":"Colorectal Cancer","tissue":"Bowel","hierarchy":"BOWEL > COADREAD > CAIS"},{"code":"CMC","name":"Medullary Carcinoma of the Colon","score":65,"mainType":"Colorectal Cancer","tissue":"Bowel","hierarchy":"BOWEL > CMC"},{"code":"COAD","name":"Colon Adenocarcinoma","score":65,"mainType":"Colorectal Cancer","tissue":"Bowel","hierarchy":"BOWEL > COADREAD > COAD"},{"code":"COADREAD","name":"Colorectal Adenocarcinoma","score":65,"mainType":"Colorectal Cancer","tissue":"Bowel","hierarchy":"BOWEL > COADREAD"},{"code":"MACR","name":"Mucinous Adenocarcinoma of the Colon and Rectum","score":65,"mainType":"Colorectal Cancer","tissue":"Bowel","hierarchy":"BOWEL > COADREAD > MACR"},{"code":"READ","name":"Rectal Adenocarcinoma","score":65,"mainType":"Colorectal Cancer","tissue":"Bowel","hierarchy":"BOWEL > COADREAD > READ"},{"code":"SRCCR","name":"Signet Ring Cell Adenocarcinoma of the Colon and Rectum","score":65,"mainType":"Colorectal Cancer","tissue":"Bowel","hierarchy":"BOWEL > COADREAD > SRCCR"}]}

▶ resolve_and_route
{
 "studyKeywords": [
  "colorectal",
  "TCGA"
 ]
}

◀ result
{"success":true,"message":"Found 3 matching studies. Pick the best match (prefer TCGA → prefer PanCancer Atlas), use its metadata to call the appropriate navigation tool(s) and generate URLs immediately. Present other studies as alternatives.","data":{"totalCount":3,"studiesWithMetadata":[{"studyId":"coadread_tcga","name":"Colorectal Adenocarcinoma (TCGA, Firehose Legacy)","sampleCount":640,"studyViewUrl":"https://www.cbioportal.org/study?id=coadread_tcga","metadata":{"clinicalAttributeIds":["AGE","AJCC_METASTASIS_PATHOLOGIC_PM","AJCC_NODES_PATHOLOGIC_PN","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","AJCC_TUMOR_PATHOLOGIC_PT","BRAF_GENE_ANALYSIS_INDICATOR","BRAF_GENE_ANALYSIS_RESULT","CANCER_TYPE","CANCER_TYPE_DETAILED","CLINICAL_STAGE","CLIN_M_STAGE","CLIN_N_STAGE","CLIN_T_STAGE","DAYS_TO_COLLECTION","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DAYS_TO_PATIENT_PROGRESSION_FREE","DAYS_TO_SPECIMEN_COLLECTION","DAYS_TO_TUMOR_PROGRESSION","DFS_MONTHS","DFS_STATUS","DISEASE_CODE","ETHNICITY","EXTRANODAL_INVOLVEMENT","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","HEIGHT","HISTOLOGICAL_DIAGNOSIS","HISTORY_NEOADJUVANT_TRTYN","HISTORY_OTHER_MALIGNANCY","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","INFORMED_CONSENT_VERIFIED","INITIAL_PATHOLOGIC_DIAGNOSIS_METHOD","INITIAL_PATHOLOGIC_DX_YEAR","IS_FFPE","KRAS_GENE_ANALYSIS_INDICATOR","KRAS_MUTATION","LONGEST_DIMENSION","LYMPHOVASCULAR_INVASION_INDICATOR","LYMPH_NODES_EXAMINED","LYMPH_NODES_EXAMINED_HE_COUNT","LYMPH_NODES_EXAMINED_IHC_COUNT","LYMPH_NODE_EXAMINED_COUNT","METHOD_OF_SAMPLE_PROCUREMENT","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","OCT_EMBEDDED","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_METHOD_OF_SAMPLE_PROCUREMENT","OTHER_PATIENT_ID","OTHER_SAMPLE_ID","PATHOLOGY_REPORT_FILE_NAME","PATHOLOGY_REPORT_UUID","PERINEURAL_INVASION","PHARMACEUTICAL_TX_ADJUVANT","PRIMARY_SITE_PATIENT","PROJECT_CODE","PROSPECTIVE_COLLECTION","RACE","RADIATION_TREATMENT_ADJUVANT","RESIDUAL_TUMOR","RETROSPECTIVE_COLLECTION","SAMPLE_COUNT","SAMPLE_INITIAL_WEIGHT","SAMPLE_TYPE","SAMPLE_TYPE_ID","SEX","SHORTEST_DIMENSION","SITE_OF_TUMOR_TISSUE","SOMATIC_STATUS","SPECIMEN_CURRENT_WEIGHT","SPECIMEN_FREEZING_METHOD","SPECIMEN_SECOND_LONGEST_DIMENSION","STAGE_OTHER","TIME_BETWEEN_CLAMPING_AND_FREEZING","TIME_BETWEEN_EXCISION_AND_FREEZING","TISSUE_SOURCE_SITE","TMB_NONSYNONYMOUS","TREATMENT_OUTCOME_FIRST_COURSE","TUMOR_STATUS","VASCULAR_INVASION_INDICATOR","VIAL_NUMBER","WEIGHT"],"molecularProfileIds":["coadread_tcga_gistic","coadread_tcga_linear_CNA","coadread_tcga_methylation_hm450","coadread_tcga_mrna","coadread_tcga_mrna_median_Zscores","coadread_tcga_mrna_median_all_sample_Zscores","coadread_tcga_mutations","coadread_tcga_protein_quantification","coadread_tcga_protein_quantification_zscores","coadread_tcga_rna_seq_v2_mrna","coadread_tcga_rna_seq_v2_mrna_median_Zscores","coadread_tcga_rna_seq_v2_mrna_median_all_sample_Zscores","coadread_tcga_rppa","coadread_tcga_rppa_Zscores"],"heatmapProfileIds":["coadr … (4966 more chars)

▶ clickhouse_list_table_columns
{
 "table": "clinical_data_derived"
}

◀ result
{"columns":[{"name":"internal_id","type":"Int32"},{"name":"sample_unique_id","type":"String","comment":"Globally unique sample ID: cancer_study_identifier + \"_\" + sample.stable_id. Empty for patient-level attributes. Use this for sample filtering and joins."},{"name":"patient_unique_id","type":"String","comment":"Globally unique patient ID: cancer_study_identifier + \"_\" + patient.stable_id. Present for both sample and patient-level attributes."},{"name":"attribute_name","type":"LowCardinality(String)","comment":"Clinical attribute name (e.g., SAMPLE_TYPE, CANCER_TYPE, AGE, OS_MONTHS). Use with attribute_value for filtering. AGE may be floored or capped for de-identification (e.g. all children recorded as 18, or everyone 89+ recorded as 89 or 90): before age statistics check for a pile-up at the min/max, and if present compute age from DAYS_TO_BIRTH (-days / 365.25)."},{"name":"attribute_value","type":"String","comment":"Value of the clinical attribute (String). For SAMPLE_TYPE: Primary, Metastasis, Local Recurrence, Unknown. Missing values are empty strings, so use toFloat64OrNull(attribute_value) for numeric comparisons — CAST fails on them."},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"type","type":"LowCardinality(String)","comment":"Data level: \"sample\" for sample-level attributes (e.g., SAMPLE_TYPE), \"patient\" for patient-level attributes (e.g., AGE, OS_MONTHS)."}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_name
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
ORDER BY attribute_name
",
 "max_rows": 100
}

◀ result
{"rows":[{"attribute_name":"AGE"},{"attribute_name":"AJCC_PATHOLOGIC_TUMOR_STAGE"},{"attribute_name":"AJCC_STAGING_EDITION"},{"attribute_name":"ANEUPLOIDY_SCORE"},{"attribute_name":"BUFFA_HYPOXIA_SCORE"},{"attribute_name":"CANCER_TYPE"},{"attribute_name":"CANCER_TYPE_ACRONYM"},{"attribute_name":"CANCER_TYPE_DETAILED"},{"attribute_name":"DAYS_LAST_FOLLOWUP"},{"attribute_name":"DAYS_TO_BIRTH"},{"attribute_name":"DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS"},{"attribute_name":"DFS_MONTHS"},{"attribute_name":"DFS_STATUS"},{"attribute_name":"DSS_MONTHS"},{"attribute_name":"DSS_STATUS"},{"attribute_name":"ETHNICITY"},{"attribute_name":"FORM_COMPLETION_DATE"},{"attribute_name":"FRACTION_GENOME_ALTERED"},{"attribute_name":"GENETIC_ANCESTRY_LABEL"},{"attribute_name":"GRADE"},{"attribute_name":"HISTORY_NEOADJUVANT_TRTYN"},{"attribute_name":"ICD_10"},{"attribute_name":"ICD_O_3_HISTOLOGY"},{"attribute_name":"ICD_O_3_SITE"},{"attribute_name":"INFORMED_CONSENT_VERIFIED"},{"attribute_name":"IN_PANCANPATHWAYS_FREEZE"},{"attribute_name":"MSI_SCORE_MANTIS"},{"attribute_name":"MSI_SENSOR_SCORE"},{"attribute_name":"MUTATION_COUNT"},{"attribute_name":"NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT"},{"attribute_name":"ONCOTREE_CODE"},{"attribute_name":"OS_MONTHS"},{"attribute_name":"OS_STATUS"},{"attribute_name":"OTHER_PATIENT_ID"},{"attribute_name":"PATH_M_STAGE"},{"attribute_name":"PATH_N_STAGE"},{"attribute_name":"PATH_T_STAGE"},{"attribute_name":"PERSON_NEOPLASM_CANCER_STATUS"},{"attribute_name":"PFS_MONTHS"},{"attribute_name":"PFS_STATUS"},{"attribute_name":"PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT"},{"attribute_name":"PRIOR_DX"},{"attribute_name":"RACE"},{"attribute_name":"RADIATION_THERAPY"},{"attribute_name":"RAGNUM_HYPOXIA_SCORE"},{"attribute_name":"SAMPLE_COUNT"},{"attribute_name":"SAMPLE_TYPE"},{"attribute_name":"SEX"},{"attribute_name":"SOMATIC_STATUS"},{"attribute_name":"SUBTYPE"},{"attribute_name":"TBL_SCORE"},{"attribute_name":"TISSUE_PROSPECTIVE_COLLECTION_INDICATOR"},{"attribute_name":"TISSUE_RETROSPECTIVE_COLLECTION_INDICATOR"},{"attribute_name":"TISSUE_SOURCE_SITE"},{"attribute_name":"TISSUE_SOURCE_SITE_CODE"},{"attribute_name":"TMB_NONSYNONYMOUS"},{"attribute_name":"TUMOR_TISSUE_SITE"},{"attribute_name":"TUMOR_TYPE"},{"attribute_name":"WEIGHT"},{"attribute_name":"WINTER_HYPOXIA_SCORE"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_value, COUNT(*) AS count
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'TUMOR_TISSUE_SITE'
  AND attribute_value != ''
GROUP BY attribute_value
ORDER BY count DESC
",
 "max_rows": 50
}

◀ result
{"rows":[{"attribute_value":"Colon","count":436},{"attribute_value":"Rectum","count":152}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_value, COUNT(*) AS count
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'ICD_O_3_SITE'
  AND attribute_value != ''
GROUP BY attribute_value
ORDER BY count DESC
",
 "max_rows": 50
}

◀ result
{"rows":[{"attribute_value":"C18.7","count":106},{"attribute_value":"C18.2","count":100},{"attribute_value":"C18.9","count":97},{"attribute_value":"C18.0","count":81},{"attribute_value":"C20.9","count":81},{"attribute_value":"C19.9","count":72},{"attribute_value":"C18.4","count":20},{"attribute_value":"C18.6","count":16},{"attribute_value":"C18.3","count":10},{"attribute_value":"C18.5","count":5},{"attribute_value":"C49.4","count":2},{"attribute_value":"C80.9","count":1}]}

▶ clickhouse_run_select_query
{
 "query": "
-- ICD-O-3 mapping:
-- Left colon: C18.2 (left colon NOS), C18.3 (left flexure), C18.4 (sigmoid), C19.9 (rectosigmoid), C20.9 (rectum)
-- Right colon: C18.0 (cecum), C18.5 (right colon NOS), C18.6 (right flexure), C18.7 (transverse colon)
-- C18.9 = colon unspecified

WITH location_mapping AS (
  SELECT 
    sample_unique_id,
    cancer_study_identifier,
    CASE 
      WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
      ELSE 'Unspecified'
    END AS location
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND attribute_name = 'ICD_O_3_SITE'
),
altered_by_location AS (
  SELECT 
    lm.location,
    ged.hugo_gene_symbol,
    COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
  FROM genomic_event_derived ged
  JOIN location_mapping lm USING (sample_unique_id, cancer_study_identifier)
  WHERE ged.variant_type = 'mutation'
    AND ged.mutation_status != 'UNCALLED'
    AND ged.off_panel = 0
    AND lm.location IN ('Right', 'Left')
  GROUP BY lm.location, ged.hugo_gene_symbol
),
profiled_by_location AS (
  SELECT 
    lm.location,
    COUNT(DISTINCT lm.sample_unique_id) AS profiled_samples
  FROM location_mapping lm
  WHERE lm.location IN ('Right', 'Left')
  GROUP BY lm.location
),
top_genes_right AS (
  SELECT 
    'Right' AS location,
    hugo_gene_symbol,
    altered_samples,
    (SELECT profiled_samples FROM profiled_by_location WHERE location = 'Right') AS profiled_samples,
    ROUND(altered_samples * 100.0 / (SELECT profiled_samples FROM profiled_by_location WHERE location = 'Right'), 1) AS frequency_pct
  FROM altered_by_location
  WHERE location = 'Right'
  ORDER BY altered_samples DESC
  LIMIT 20
),
top_genes_left AS (
  SELECT 
    'Left' AS location,
    hugo_gene_symbol,
    altered_samples,
    (SELECT profiled_samples FROM profiled_by_location WHERE location = 'Left') AS profiled_samples,
    ROUND(altered_samples * 100.0 / (SELECT profiled_samples FROM profiled_by_location WHERE location = 'Left'), 1) AS frequency_pct
  FROM altered_by_location
  WHERE location = 'Left'
  ORDER BY altered_samples DESC
  LIMIT 20
)
SELECT * FROM top_genes_right
UNION ALL
SELECT * FROM top_genes_left
ORDER BY location, frequency_pct DESC
",
 "max_rows": 100
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
-- Check available samples for joining
SELECT COUNT(DISTINCT sample_unique_id) as unique_samples
FROM genomic_event_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND variant_type = 'mutation'
  AND mutation_status != 'UNCALLED'
",
 "max_rows": 10
}

◀ result
{"rows":[{"unique_samples":528}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Check sample overlap between clinical data and genomic data
WITH location_data AS (
  SELECT DISTINCT sample_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND attribute_name = 'ICD_O_3_SITE'
    AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
),
mutation_data AS (
  SELECT DISTINCT sample_unique_id
  FROM genomic_event_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND variant_type = 'mutation'
    AND mutation_status != 'UNCALLED'
)
SELECT 
  (SELECT COUNT(*) FROM location_data) AS samples_with_location_info,
  (SELECT COUNT(*) FROM mutation_data) AS samples_with_mutations,
  (SELECT COUNT(*) FROM location_data WHERE sample_unique_id IN (SELECT sample_unique_id FROM mutation_data)) AS overlap
",
 "max_rows": 10
}

◀ result
{"rows":[{"samples_with_location_info":1,"samples_with_mutations":528,"overlap":0}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Check what samples have ICD_O_3_SITE values
SELECT 
  sample_unique_id,
  attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'ICD_O_3_SITE'
  AND attribute_value != ''
LIMIT 20
",
 "max_rows": 30
}

◀ result
{"rows":[{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C20.9"},{"attribute_value":"C19.9"},{"attribute_value":"C20.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C20.9"},{"attribute_value":"C20.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"},{"attribute_value":"C20.9"},{"attribute_value":"C20.9"},{"attribute_value":"C19.9"},{"attribute_value":"C19.9"}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Check what type of attribute ICD_O_3_SITE is
SELECT DISTINCT type
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'ICD_O_3_SITE'
",
 "max_rows": 10
}

◀ result
{"rows":[{"type":"patient"}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Mutation frequency by anatomical location (left vs right colon)
WITH patient_location AS (
  SELECT 
    patient_unique_id,
    cancer_study_identifier,
    CASE 
      WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right Colon'
      WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left Colon/Rectum'
      ELSE 'Unspecified'
    END AS location
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND attribute_name = 'ICD_O_3_SITE'
    AND attribute_value != ''
),
sample_location AS (
  SELECT DISTINCT
    ged.sample_unique_id,
    pl.location
  FROM genomic_event_derived ged
  CROSS JOIN clinical_data_derived cd ON ged.cancer_study_identifier = cd.cancer_study_identifier
  JOIN patient_location pl ON cd.patient_unique_id = pl.patient_unique_id 
    AND ged.cancer_study_identifier = pl.cancer_study_identifier
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND cd.attribute_name = 'SAMPLE_TYPE'
    AND cd.sample_unique_id IS NOT NULL
  LIMIT 1
),
altered_by_location AS (
  SELECT 
    sl.location,
    ged.hugo_gene_symbol,
    COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
  FROM genomic_event_derived ged
  JOIN sample_location sl ON ged.sample_unique_id = sl.sample_unique_id
  WHERE ged.variant_type = 'mutation'
    AND ged.mutation_status != 'UNCALLED'
    AND ged.off_panel = 0
  GROUP BY sl.location, ged.hugo_gene_symbol
),
location_sample_counts AS (
  SELECT 
    sl.location,
    COUNT(DISTINCT ged.sample_unique_id) AS profiled_samples
  FROM genomic_event_derived ged
  JOIN sample_location sl ON ged.sample_unique_id = sl.sample_unique_id
  GROUP BY sl.location
)
SELECT 
  abl.location,
  abl.hugo_gene_symbol,
  abl.altered_samples,
  lsc.profiled_samples,
  ROUND(abl.altered_samples * 100.0 / lsc.profiled_samples, 1) AS frequency_pct
FROM altered_by_location abl
JOIN location_sample_counts lsc ON abl.location = lsc.location
ORDER BY abl.location, abl.altered_samples DESC
LIMIT 50
",
 "max_rows": 100
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 62\n Code: 62. DB::Exception: Syntax error: failed at position 737 (ON) (line 21, col 39): ON ged.cancer_study_identifier = cd.cancer_study_identifier\n  JOIN patient_location pl ON cd.patient_unique_id = pl.patient_unique_id \n    AND ged.cancer_study_id... Expected one of: FINAL, SAMPLE, table, table function, subquery or list of joined tables, array join, LEFT ARRAY JOIN, INNER, ARRAY JOIN, GLOBAL, LOCAL, NATURAL, ANY, ALL, ASOF, SEMI, ANTI, ONLY, LEFT, RIGHT, FULL, CROSS, PASTE, JOIN, token, OpeningRoundBracket, PREWHERE, WHERE, GROUP BY, WITH, HAVING, WINDOW, QUALIFY, ORDER BY, LIMIT, OFFSET, FETCH, SETTINGS, UNION, EXCEPT, INTERSECT, alias, AS. (SYNTAX_ERROR) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
-- Step 1: Get patient-to-location mapping
WITH patient_location AS (
  SELECT 
    patient_unique_id,
    CASE 
      WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right Colon'
      WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left Colon/Rectum'
      ELSE 'Unspecified'
    END AS location
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND attribute_name = 'ICD_O_3_SITE'
    AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
),
-- Step 2: Map samples to locations via patients
sample_patient_mapping AS (
  SELECT DISTINCT
    ged.sample_unique_id,
    pl.location,
    ged.cancer_study_identifier
  FROM genomic_event_derived ged
  JOIN patient_location pl ON 
    ged.cancer_study_identifier || '_' || SUBSTRING_INDEX(ged.sample_unique_id, '_', -1) LIKE CONCAT('%_', SUBSTRING_INDEX(pl.patient_unique_id, '_', -1))
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
)
SELECT 
  spm.location,
  COUNT(DISTINCT spm.sample_unique_id) AS samples_count,
  'test' AS check_val
FROM sample_patient_mapping spm
GROUP BY spm.location
",
 "max_rows": 10
}

◀ result
{"error_message":"Query timed out after 30 seconds"}

▶ clickhouse_run_select_query
{
 "query": "
-- Get all sample data for patients with location info
SELECT 
  patient_unique_id,
  attribute_value AS icd_site
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'ICD_O_3_SITE'
  AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
LIMIT 100
",
 "max_rows": 100
}

◀ result
{"rows":[{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-2687","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-2689","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-2690","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-2691","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-2692","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-2693","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-3400","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-3911","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-4110","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-5654","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-6136","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-6655","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-6672","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-A56K","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-A56L","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AF-A56N","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3574","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3575","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3578","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3580","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3581","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3583","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3584","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3586","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3587","icd_site":"C20.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3591","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3592","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3593","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3594","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3598","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3599","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3600","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AG-3601","icd_site":"C19.9"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018 … (5910 more chars)

▶ clickhouse_run_select_query
{
 "query": "
-- Get unique patient-to-location mapping
SELECT 
  patient_unique_id,
  attribute_value AS icd_site,
  CASE 
    WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right Colon'
    WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left Colon/Rectum'
    ELSE 'Unspecified'
  END AS location
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'ICD_O_3_SITE'
  AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
GROUP BY patient_unique_id, attribute_value
ORDER BY location, icd_site
",
 "max_rows": 300
}

◀ result
{"rows":[{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-3875","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-3821","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-D5-5539","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-CA-6717","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AY-6196","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-G4-6311","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-A00U","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-4N-A93T","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-A6-5665","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-A6-4105","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AY-5543","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-A6-5660","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-F4-6703","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-3522","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-3495","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-A00D","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-NH-A8F8","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-CK-4952","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-A6-2679","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-3496","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-CM-6162","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AD-6963","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AD-6888","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-CK-5916","icd_site":"C18.2","location":"Left Colon/Rectum"},{"patient_unique_id":"coadread_tcga_pan_can_atlas_2018_TCGA-AA-3870","icd_site":"C18.2","location":"Left Colon/ … (33201 more chars)

▶ clickhouse_run_select_query
{
 "query": "
-- Top 20 mutated genes comparing left-sided vs right-sided CRC
WITH patient_location_map AS (
  SELECT 
    patient_unique_id,
    CASE 
      WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
      ELSE 'Unspecified'
    END AS location
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND attribute_name = 'ICD_O_3_SITE'
    AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
),
patient_sample_mapping AS (
  SELECT DISTINCT
    ged.sample_unique_id,
    ged.cancer_study_identifier,
    plm.location
  FROM genomic_event_derived ged
  CROSS JOIN clinical_data_derived cd 
    ON ged.cancer_study_identifier = cd.cancer_study_identifier 
    AND cd.attribute_name = 'SAMPLE_TYPE'
  JOIN patient_location_map plm 
    ON cd.patient_unique_id = plm.patient_unique_id
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND cd.sample_unique_id IS NOT NULL
  LIMIT 100
)
SELECT 
  psm.location,
  COUNT(DISTINCT psm.sample_unique_id) as mapped_samples
FROM patient_sample_mapping psm
GROUP BY psm.location
",
 "max_rows": 10
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 62\n Code: 62. DB::Exception: Syntax error: failed at position 815 (ON) (line 22, col 5): ON ged.cancer_study_identifier = cd.cancer_study_identifier \n    AND cd.attribute_name = 'SAMPLE_TYPE'\n  JOIN patient_location_map plm \n    ON cd.patient_unique_id... Expected one of: FINAL, SAMPLE, table, table function, subquery or list of joined tables, array join, LEFT ARRAY JOIN, INNER, ARRAY JOIN, GLOBAL, LOCAL, NATURAL, ANY, ALL, ASOF, SEMI, ANTI, ONLY, LEFT, RIGHT, FULL, CROSS, PASTE, JOIN, token, OpeningRoundBracket, PREWHERE, WHERE, GROUP BY, WITH, HAVING, WINDOW, QUALIFY, ORDER BY, LIMIT, OFFSET, FETCH, SETTINGS, UNION, EXCEPT, INTERSECT, alias, AS. (SYNTAX_ERROR) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
-- Top mutated genes by anatomical location: Left vs Right CRC
WITH patient_locations AS (
  SELECT 
    patient_unique_id,
    CASE 
      WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
    END AS location
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND attribute_name = 'ICD_O_3_SITE'
    AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
),
sample_with_location AS (
  SELECT 
    ged.sample_unique_id,
    ged.cancer_study_identifier,
    pl.location
  FROM genomic_event_derived ged
  INNER JOIN clinical_data_derived cd ON ged.sample_unique_id = cd.sample_unique_id
  INNER JOIN patient_locations pl ON cd.patient_unique_id = pl.patient_unique_id
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND cd.attribute_name = 'SAMPLE_TYPE'
),
gene_mutations_by_location AS (
  SELECT 
    swl.location,
    ged.hugo_gene_symbol,
    COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
  FROM genomic_event_derived ged
  INNER JOIN sample_with_location swl ON ged.sample_unique_id = swl.sample_unique_id
  WHERE ged.variant_type = 'mutation'
    AND ged.mutation_status != 'UNCALLED'
    AND ged.off_panel = 0
  GROUP BY swl.location, ged.hugo_gene_symbol
),
profiled_samples_by_location AS (
  SELECT 
    location,
    COUNT(DISTINCT sample_unique_id) AS profiled_samples
  FROM sample_with_location
  GROUP BY location
)
SELECT 
    gmbl.location,
    gmbl.hugo_gene_symbol,
    gmbl.altered_samples,
    psbl.profiled_samples,
    ROUND(gmbl.altered_samples * 100.0 / psbl.profiled_samples, 1) AS frequency_pct
FROM gene_mutations_by_location gmbl
INNER JOIN profiled_samples_by_location psbl ON gmbl.location = psbl.location
ORDER BY gmbl.location, gmbl.altered_samples DESC
LIMIT 50
",
 "max_rows": 100
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 47\n Code: 47. DB::Exception: Identifier 'swl.sample_unique_id' cannot be resolved from subquery with name swl. In scope  gene_mutations_by_location AS gmbl. Maybe you meant: ['ged.sample_unique_id']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
-- Top mutated genes by anatomical location: Left vs Right CRC
WITH patient_locations AS (
  SELECT 
    patient_unique_id,
    CASE 
      WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
    END AS location
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND attribute_name = 'ICD_O_3_SITE'
    AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
),
sample_with_location AS (
  SELECT 
    ged.sample_unique_id,
    ged.cancer_study_identifier,
    pl.location
  FROM genomic_event_derived ged
  JOIN clinical_data_derived cd ON (ged.sample_unique_id = cd.sample_unique_id AND ged.cancer_study_identifier = cd.cancer_study_identifier)
  JOIN patient_locations pl ON cd.patient_unique_id = pl.patient_unique_id
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND cd.attribute_name = 'SAMPLE_TYPE'
),
gene_mutations_by_location AS (
  SELECT 
    swl.location,
    ged.hugo_gene_symbol,
    COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
  FROM genomic_event_derived ged
  JOIN sample_with_location swl ON (ged.sample_unique_id = swl.sample_unique_id AND ged.cancer_study_identifier = swl.cancer_study_identifier)
  WHERE ged.variant_type = 'mutation'
    AND ged.mutation_status != 'UNCALLED'
    AND ged.off_panel = 0
  GROUP BY swl.location, ged.hugo_gene_symbol
),
profiled_samples_by_location AS (
  SELECT 
    location,
    COUNT(DISTINCT sample_unique_id) AS profiled_samples
  FROM sample_with_location
  GROUP BY location
)
SELECT 
    gmbl.location,
    gmbl.hugo_gene_symbol,
    gmbl.altered_samples,
    psbl.profiled_samples,
    ROUND(gmbl.altered_samples * 100.0 / psbl.profiled_samples, 1) AS frequency_pct
FROM gene_mutations_by_location gmbl
JOIN profiled_samples_by_location psbl ON gmbl.location = psbl.location
ORDER BY gmbl.location, gmbl.altered_samples DESC
LIMIT 50
",
 "max_rows": 100
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 47\n Code: 47. DB::Exception: Identifier 'swl.sample_unique_id' cannot be resolved from subquery with name swl. In scope  gene_mutations_by_location AS gmbl. Maybe you meant: ['ged.sample_unique_id']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
-- Top mutated genes by anatomical location: Left vs Right CRC
SELECT 
    location,
    hugo_gene_symbol,
    altered_samples,
    profiled_samples,
    ROUND(altered_samples * 100.0 / profiled_samples, 1) AS frequency_pct
FROM (
  SELECT 
    CASE 
      WHEN icd_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN icd_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
    END AS location,
    hugo_gene_symbol,
    COUNT(DISTINCT sample_unique_id) AS altered_samples,
    MAX(profiled_count) AS profiled_samples
  FROM (
    SELECT 
      ged.sample_unique_id,
      ged.hugo_gene_symbol,
      cd_icd.attribute_value AS icd_value,
      (SELECT COUNT(DISTINCT s_c.sample_unique_id)
       FROM genomic_event_derived s_c
       JOIN clinical_data_derived cd_s ON s_c.sample_unique_id = cd_s.sample_unique_id
       JOIN clinical_data_derived cd_icd2 ON cd_s.patient_unique_id = cd_icd2.patient_unique_id
       WHERE s_c.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
         AND cd_s.attribute_name = 'SAMPLE_TYPE'
         AND cd_icd2.attribute_name = 'ICD_O_3_SITE'
         AND cd_icd2.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
         AND cd_icd2.attribute_value = cd_icd.attribute_value
      ) AS profiled_count
    FROM genomic_event_derived ged
    JOIN clinical_data_derived cd ON ged.sample_unique_id = cd.sample_unique_id
    JOIN clinical_data_derived cd_icd ON cd.patient_unique_id = cd_icd.patient_unique_id
    WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
      AND ged.variant_type = 'mutation'
      AND ged.mutation_status != 'UNCALLED'
      AND ged.off_panel = 0
      AND cd.attribute_name = 'SAMPLE_TYPE'
      AND cd_icd.attribute_name = 'ICD_O_3_SITE'
      AND cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
  )
  GROUP BY location, hugo_gene_symbol
)
WHERE location IS NOT NULL
ORDER BY location, frequency_pct DESC
LIMIT 50
",
 "max_rows": 100
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 1\n Code: 1. DB::Exception: Resolved identifier 'cd_icd.attribute_value' in parent scope to expression 'cd_icd.attribute_value' with correlated columns 'attribute_value' (Enable 'allow_experimental_correlated_subqueries' setting to allow correlated subqueries execution). In scope (SELECT COUNTDistinct(s_c.sample_unique_id) FROM genomic_event_derived AS s_c INNER JOIN clinical_data_derived AS cd_s ON s_c.sample_unique_id = cd_s.sample_unique_id INNER JOIN clinical_data_derived AS cd_icd2 ON cd_s.patient_unique_id = cd_icd2.patient_unique_id WHERE (s_c.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018') AND (cd_s.attribute_name = 'SAMPLE_TYPE') AND (cd_icd2.attribute_name = 'ICD_O_3_SITE') AND (cd_icd2.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')) AND (cd_icd2.attribute_value = cd_icd.attribute_value)) AS profiled_count. (UNSUPPORTED_METHOD) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
-- First, get counts for each location
SELECT 
  CASE 
    WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
    WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
  END AS location,
  COUNT(DISTINCT patient_unique_id) AS num_patients,
  countif(attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7')) AS right_patients,
  countif(attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')) AS left_patients
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'ICD_O_3_SITE'
  AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
GROUP BY location
",
 "max_rows": 10
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 46\n Code: 46. DB::Exception: Function with name `countif` does not exist. In scope SELECT multiIf((attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7')), 'Right', (attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')), 'Left', NULL) AS location, COUNTDistinct(patient_unique_id) AS num_patients, countif(attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7')) AS right_patients, countif(attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')) AS left_patients FROM clinical_data_derived WHERE (cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018') AND (attribute_name = 'ICD_O_3_SITE') AND (attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')) GROUP BY location. Maybe you meant: ['count']. (UNKNOWN_FUNCTION) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
-- Get basic location counts  
SELECT 
  CASE 
    WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
    WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
  END AS location,
  COUNT(DISTINCT patient_unique_id) AS num_patients
FROM clinical_data_derived
WHERE cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND attribute_name = 'ICD_O_3_SITE'
  AND attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
GROUP BY location
",
 "max_rows": 10
}

◀ result
{"rows":[{"location":"Left","num_patients":283},{"location":"Right","num_patients":208}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Mutation frequencies for key CRC driver genes by anatomical location
-- Breakdown: Left (C18.2, C18.3, C18.4, C19.9, C20.9) vs Right (C18.0, C18.5, C18.6, C18.7)

SELECT 
  ged.hugo_gene_symbol,
  cd_icd.attribute_value AS icd_code,
  CASE 
    WHEN cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right Colon'
    WHEN cd_icd.attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left Colon/Rectum'
  END AS location,
  COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
FROM genomic_event_derived ged
INNER JOIN clinical_data_derived cd ON (ged.sample_unique_id = cd.sample_unique_id AND ged.cancer_study_identifier = cd.cancer_study_identifier)
INNER JOIN clinical_data_derived cd_icd ON (cd.patient_unique_id = cd_icd.patient_unique_id AND cd.cancer_study_identifier = cd_icd.cancer_study_identifier)
WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND cd.attribute_name = 'SAMPLE_TYPE'
  AND cd_icd.attribute_name = 'ICD_O_3_SITE'
  AND cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
  AND ged.variant_type = 'mutation'
  AND ged.mutation_status != 'UNCALLED'
  AND ged.off_panel = 0
  AND ged.hugo_gene_symbol IN ('TP53', 'KRAS', 'APC', 'BRAF', 'SMAD4', 'PIK3CA', 'SMAD2', 'FBXW7', 'NRAS', 'MSH6')
GROUP BY ged.hugo_gene_symbol, location, icd_code
ORDER BY ged.hugo_gene_symbol, location
",
 "max_rows": 100
}

◀ result
{"rows":[{"hugo_gene_symbol":"APC","icd_code":"C20.9","location":"Left Colon/Rectum","altered_samples":66},{"hugo_gene_symbol":"APC","icd_code":"C18.3","location":"Left Colon/Rectum","altered_samples":7},{"hugo_gene_symbol":"APC","icd_code":"C18.2","location":"Left Colon/Rectum","altered_samples":53},{"hugo_gene_symbol":"APC","icd_code":"C19.9","location":"Left Colon/Rectum","altered_samples":41},{"hugo_gene_symbol":"APC","icd_code":"C18.4","location":"Left Colon/Rectum","altered_samples":12},{"hugo_gene_symbol":"APC","icd_code":"C18.5","location":"Right Colon","altered_samples":4},{"hugo_gene_symbol":"APC","icd_code":"C18.7","location":"Right Colon","altered_samples":78},{"hugo_gene_symbol":"APC","icd_code":"C18.6","location":"Right Colon","altered_samples":10},{"hugo_gene_symbol":"APC","icd_code":"C18.0","location":"Right Colon","altered_samples":52},{"hugo_gene_symbol":"BRAF","icd_code":"C20.9","location":"Left Colon/Rectum","altered_samples":5},{"hugo_gene_symbol":"BRAF","icd_code":"C18.3","location":"Left Colon/Rectum","altered_samples":1},{"hugo_gene_symbol":"BRAF","icd_code":"C19.9","location":"Left Colon/Rectum","altered_samples":1},{"hugo_gene_symbol":"BRAF","icd_code":"C18.4","location":"Left Colon/Rectum","altered_samples":2},{"hugo_gene_symbol":"BRAF","icd_code":"C18.2","location":"Left Colon/Rectum","altered_samples":17},{"hugo_gene_symbol":"BRAF","icd_code":"C18.7","location":"Right Colon","altered_samples":3},{"hugo_gene_symbol":"BRAF","icd_code":"C18.0","location":"Right Colon","altered_samples":22},{"hugo_gene_symbol":"BRAF","icd_code":"C18.6","location":"Right Colon","altered_samples":1},{"hugo_gene_symbol":"FBXW7","icd_code":"C18.4","location":"Left Colon/Rectum","altered_samples":4},{"hugo_gene_symbol":"FBXW7","icd_code":"C18.2","location":"Left Colon/Rectum","altered_samples":15},{"hugo_gene_symbol":"FBXW7","icd_code":"C18.3","location":"Left Colon/Rectum","altered_samples":4},{"hugo_gene_symbol":"FBXW7","icd_code":"C20.9","location":"Left Colon/Rectum","altered_samples":16},{"hugo_gene_symbol":"FBXW7","icd_code":"C19.9","location":"Left Colon/Rectum","altered_samples":7},{"hugo_gene_symbol":"FBXW7","icd_code":"C18.7","location":"Right Colon","altered_samples":10},{"hugo_gene_symbol":"FBXW7","icd_code":"C18.6","location":"Right Colon","altered_samples":2},{"hugo_gene_symbol":"FBXW7","icd_code":"C18.0","location":"Right Colon","altered_samples":11},{"hugo_gene_symbol":"FBXW7","icd_code":"C18.5","location":"Right Colon","altered_samples":2},{"hugo_gene_symbol":"KRAS","icd_code":"C18.2","location":"Left Colon/Rectum","altered_samples":36},{"hugo_gene_symbol":"KRAS","icd_code":"C18.3","location":"Left Colon/Rectum","altered_samples":7},{"hugo_gene_symbol":"KRAS","icd_code":"C19.9","location":"Left Colon/Rectum","altered_samples":18},{"hugo_gene_symbol":"KRAS","icd_code":"C20.9","location":"Left Colon/Rectum","altered_samples":30},{"hugo_gene_symbol":"KRAS","icd_code":"C18.4","location":"Left Colon/Rectum","altered_samples":10},{" … (4612 more chars)

▶ clickhouse_run_select_query
{
 "query": "
-- Aggregate mutation frequencies by location with percentages
WITH location_totals AS (
  SELECT 
    CASE 
      WHEN attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
    END AS location,
    COUNT(DISTINCT ged.sample_unique_id) AS total_profiled
  FROM genomic_event_derived ged
  INNER JOIN clinical_data_derived cd ON (ged.sample_unique_id = cd.sample_unique_id AND ged.cancer_study_identifier = cd.cancer_study_identifier)
  INNER JOIN clinical_data_derived cd_icd ON (cd.patient_unique_id = cd_icd.patient_unique_id AND cd.cancer_study_identifier = cd_icd.cancer_study_identifier)
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND cd.attribute_name = 'SAMPLE_TYPE'
    AND cd_icd.attribute_name = 'ICD_O_3_SITE'
    AND cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
  GROUP BY location
),
mutations_by_location AS (
  SELECT 
    CASE 
      WHEN cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN cd_icd.attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
    END AS location,
    ged.hugo_gene_symbol,
    COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
  FROM genomic_event_derived ged
  INNER JOIN clinical_data_derived cd ON (ged.sample_unique_id = cd.sample_unique_id AND ged.cancer_study_identifier = cd.cancer_study_identifier)
  INNER JOIN clinical_data_derived cd_icd ON (cd.patient_unique_id = cd_icd.patient_unique_id AND cd.cancer_study_identifier = cd_icd.cancer_study_identifier)
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND cd.attribute_name = 'SAMPLE_TYPE'
    AND cd_icd.attribute_name = 'ICD_O_3_SITE'
    AND cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
    AND ged.variant_type = 'mutation'
    AND ged.mutation_status != 'UNCALLED'
    AND ged.off_panel = 0
    AND ged.hugo_gene_symbol IN ('TP53', 'KRAS', 'APC', 'BRAF', 'SMAD4', 'PIK3CA', 'SMAD2', 'FBXW7', 'NRAS', 'MSH6')
  GROUP BY location, ged.hugo_gene_symbol
)
SELECT 
  mbl.location,
  mbl.hugo_gene_symbol,
  mbl.altered_samples,
  lt.total_profiled,
  ROUND(mbl.altered_samples * 100.0 / lt.total_profiled, 1) AS frequency_pct
FROM mutations_by_location mbl
INNER JOIN location_totals lt ON mbl.location = lt.location
ORDER BY mbl.location, mbl.altered_samples DESC
",
 "max_rows": 100
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
-- Simpler approach: aggregate the raw counts we already have
SELECT 
  location,
  hugo_gene_symbol,
  SUM(altered_samples) AS altered_samples
FROM (
  SELECT 
    ged.hugo_gene_symbol,
    cd_icd.attribute_value AS icd_code,
    CASE 
      WHEN cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
      WHEN cd_icd.attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
    END AS location,
    COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
  FROM genomic_event_derived ged
  INNER JOIN clinical_data_derived cd ON (ged.sample_unique_id = cd.sample_unique_id AND ged.cancer_study_identifier = cd.cancer_study_identifier)
  INNER JOIN clinical_data_derived cd_icd ON (cd.patient_unique_id = cd_icd.patient_unique_id AND cd.cancer_study_identifier = cd_icd.cancer_study_identifier)
  WHERE ged.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
    AND cd.attribute_name = 'SAMPLE_TYPE'
    AND cd_icd.attribute_name = 'ICD_O_3_SITE'
    AND cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
    AND ged.variant_type = 'mutation'
    AND ged.mutation_status != 'UNCALLED'
    AND ged.off_panel = 0
    AND ged.hugo_gene_symbol IN ('TP53', 'KRAS', 'APC', 'BRAF', 'SMAD4', 'PIK3CA', 'SMAD2', 'FBXW7', 'NRAS', 'MSH6')
  GROUP BY ged.hugo_gene_symbol, location, icd_code
)
GROUP BY location, hugo_gene_symbol
ORDER BY location, altered_samples DESC
",
 "max_rows": 100
}

◀ result
{"rows":[{"location":"Left","hugo_gene_symbol":"APC","altered_samples":179},{"location":"Left","hugo_gene_symbol":"TP53","altered_samples":148},{"location":"Left","hugo_gene_symbol":"KRAS","altered_samples":101},{"location":"Left","hugo_gene_symbol":"PIK3CA","altered_samples":65},{"location":"Left","hugo_gene_symbol":"FBXW7","altered_samples":46},{"location":"Left","hugo_gene_symbol":"SMAD4","altered_samples":35},{"location":"Left","hugo_gene_symbol":"BRAF","altered_samples":26},{"location":"Left","hugo_gene_symbol":"NRAS","altered_samples":16},{"location":"Left","hugo_gene_symbol":"MSH6","altered_samples":14},{"location":"Left","hugo_gene_symbol":"SMAD2","altered_samples":13},{"location":"Right","hugo_gene_symbol":"APC","altered_samples":144},{"location":"Right","hugo_gene_symbol":"TP53","altered_samples":119},{"location":"Right","hugo_gene_symbol":"KRAS","altered_samples":74},{"location":"Right","hugo_gene_symbol":"PIK3CA","altered_samples":54},{"location":"Right","hugo_gene_symbol":"BRAF","altered_samples":26},{"location":"Right","hugo_gene_symbol":"FBXW7","altered_samples":25},{"location":"Right","hugo_gene_symbol":"SMAD4","altered_samples":22},{"location":"Right","hugo_gene_symbol":"NRAS","altered_samples":14},{"location":"Right","hugo_gene_symbol":"SMAD2","altered_samples":13},{"location":"Right","hugo_gene_symbol":"MSH6","altered_samples":7}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get sample counts per location (profiled denominators)
SELECT 
  CASE 
    WHEN cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7') THEN 'Right'
    WHEN cd_icd.attribute_value IN ('C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9') THEN 'Left'
  END AS location,
  COUNT(DISTINCT cd.sample_unique_id) AS profiled_samples
FROM clinical_data_derived cd
INNER JOIN clinical_data_derived cd_icd ON (cd.patient_unique_id = cd_icd.patient_unique_id AND cd.cancer_study_identifier = cd_icd.cancer_study_identifier)
WHERE cd.cancer_study_identifier = 'coadread_tcga_pan_can_atlas_2018'
  AND cd.attribute_name = 'SAMPLE_TYPE'
  AND cd_icd.attribute_name = 'ICD_O_3_SITE'
  AND cd_icd.attribute_value IN ('C18.0', 'C18.5', 'C18.6', 'C18.7', 'C18.2', 'C18.3', 'C18.4', 'C19.9', 'C20.9')
GROUP BY location
",
 "max_rows": 10
}

◀ result
{"rows":[{"location":"Left","profiled_samples":283},{"location":"Right","profiled_samples":208}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Final: Mutation frequencies Left vs Right CRC with percentages
SELECT 
  hugo_gene_symbol,
  LEFT_altered,
  LEFT_pct,
  RIGHT_altered,
  RIGHT_pct,
  ROUND(LEFT_pct - RIGHT_pct, 1) AS diff_pct
FROM (
  SELECT 
    'APC' AS hugo_gene_symbol,
    179 AS LEFT_altered,
    ROUND(179 * 100.0 / 283, 1) AS LEFT_pct,
    144 AS RIGHT_altered,
    ROUND(144 * 100.0 / 208, 1) AS RIGHT_pct
  UNION ALL
  SELECT 'TP53', 148, ROUND(148 * 100.0 / 283, 1), 119, ROUND(119 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'KRAS', 101, ROUND(101 * 100.0 / 283, 1), 74, ROUND(74 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'PIK3CA', 65, ROUND(65 * 100.0 / 283, 1), 54, ROUND(54 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'FBXW7', 46, ROUND(46 * 100.0 / 283, 1), 25, ROUND(25 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'SMAD4', 35, ROUND(35 * 100.0 / 283, 1), 22, ROUND(22 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'BRAF', 26, ROUND(26 * 100.0 / 283, 1), 26, ROUND(26 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'NRAS', 16, ROUND(16 * 100.0 / 283, 1), 14, ROUND(14 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'MSH6', 14, ROUND(14 * 100.0 / 283, 1), 7, ROUND(7 * 100.0 / 208, 1)
  UNION ALL
  SELECT 'SMAD2', 13, ROUND(13 * 100.0 / 283, 1), 13, ROUND(13 * 100.0 / 208, 1)
)
ORDER BY LEFT_pct DESC
",
 "max_rows": 20
}

◀ result
{"rows":[{"hugo_gene_symbol":"APC","LEFT_altered":179,"LEFT_pct":63.3,"RIGHT_altered":144,"RIGHT_pct":69.2,"diff_pct":-5.9},{"hugo_gene_symbol":"TP53","LEFT_altered":148,"LEFT_pct":52.3,"RIGHT_altered":119,"RIGHT_pct":57.2,"diff_pct":-4.9},{"hugo_gene_symbol":"KRAS","LEFT_altered":101,"LEFT_pct":35.7,"RIGHT_altered":74,"RIGHT_pct":35.6,"diff_pct":0.1},{"hugo_gene_symbol":"PIK3CA","LEFT_altered":65,"LEFT_pct":23,"RIGHT_altered":54,"RIGHT_pct":26,"diff_pct":-3},{"hugo_gene_symbol":"FBXW7","LEFT_altered":46,"LEFT_pct":16.3,"RIGHT_altered":25,"RIGHT_pct":12,"diff_pct":4.3},{"hugo_gene_symbol":"SMAD4","LEFT_altered":35,"LEFT_pct":12.4,"RIGHT_altered":22,"RIGHT_pct":10.6,"diff_pct":1.8},{"hugo_gene_symbol":"BRAF","LEFT_altered":26,"LEFT_pct":9.2,"RIGHT_altered":26,"RIGHT_pct":12.5,"diff_pct":-3.3},{"hugo_gene_symbol":"NRAS","LEFT_altered":16,"LEFT_pct":5.7,"RIGHT_altered":14,"RIGHT_pct":6.7,"diff_pct":-1},{"hugo_gene_symbol":"MSH6","LEFT_altered":14,"LEFT_pct":4.9,"RIGHT_altered":7,"RIGHT_pct":3.4,"diff_pct":1.5},{"hugo_gene_symbol":"SMAD2","LEFT_altered":13,"LEFT_pct":4.6,"RIGHT_altered":13,"RIGHT_pct":6.2,"diff_pct":-1.6}]}

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

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"coadread_tcga_pan_can_atlas_2018","name":"Colorectal Adenocarcinoma (TCGA, PanCancer Atlas)","sampleCount":594,"studyViewUrl":"https://www.cbioportal.org/study?id=coadread_tcga_pan_can_atlas_2018","metadata":{"clinicalAttributeIds":["AGE","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","ANEUPLOIDY_SCORE","BUFFA_HYPOXIA_SCORE","CANCER_TYPE","CANCER_TYPE_ACRONYM","CANCER_TYPE_DETAILED","DAYS_LAST_FOLLOWUP","DAYS_TO_BIRTH","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DFS_MONTHS","DFS_STATUS","DSS_MONTHS","DSS_STATUS","ETHNICITY","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","GENETIC_ANCESTRY_LABEL","GRADE","HISTORY_NEOADJUVANT_TRTYN","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","INFORMED_CONSENT_VERIFIED","IN_PANCANPATHWAYS_FREEZE","MSI_SCORE_MANTIS","MSI_SENSOR_SCORE","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_PATIENT_ID","PATH_M_STAGE","PATH_N_STAGE","PATH_T_STAGE","PERSON_NEOPLASM_CANCER_STATUS","PFS_MONTHS","PFS_STATUS","PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT","PRIOR_DX","RACE","RADIATION_THERAPY","RAGNUM_HYPOXIA_SCORE","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SOMATIC_STATUS","SUBTYPE","TBL_SCORE","TISSUE_PROSPECTIVE_COLLECTION_INDICATOR","TISSUE_RETROSPECTIVE_COLLECTION_INDICATOR","TISSUE_SOURCE_SITE","TISSUE_SOURCE_SITE_CODE","TMB_NONSYNONYMOUS","TUMOR_TISSUE_SITE","TUMOR_TYPE","WEIGHT","WINTER_HYPOXIA_SCORE"],"molecularProfileIds":["coadread_tcga_pan_can_atlas_2018_gistic","coadread_tcga_pan_can_atlas_2018_log2CNA","coadread_tcga_pan_can_atlas_2018_mutations","coadread_tcga_pan_can_atlas_2018_protein_quantification","coadread_tcga_pan_can_atlas_2018_protein_quantification_zscores","coadread_tcga_pan_can_atlas_2018_rna_seq_v2_mrna","coadread_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","coadread_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","coadread_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","coadread_tcga_pan_can_atlas_2018_rppa","coadread_tcga_pan_can_atlas_2018_rppa_Zscores","coadread_tcga_pan_can_atlas_2018_structural_variants"],"genericAssayProfiles":["coadread_tcga_pan_can_atlas_2018_armlevel_cna","coadread_tcga_pan_can_atlas_2018_genetic_ancestry","coadread_tcga_pan_can_atlas_2018_methylation_hm27_hm450_merge","coadread_tcga_pan_can_atlas_2018_methylation_hm450"],"heatmapProfileIds":["coadread_tcga_pan_can_atlas_2018_protein_quantification_zscores","coadread_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","coadread_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","coadread_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","coadread_tcga_pan_can_atlas_2018_rppa_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","protein","generic_assay_armlevel_c … (70 more chars)

▶ navigate_to_group_comparison
{
 "studyIds": [
  "coadread_tcga_pan_can_atlas_2018"
 ],
 "groups": [
  {
   "name": "Left Colon/Rectum",
   "studyViewFilter": {
    "clinicalDataFilters": [
     {
      "attributeId": "ICD_O_3_SITE",
      "values": [
       {
        "value": "C18.2"
       },
       {
        "value": "C18.3"
       },
       {
        "value": "C18.4"
       },
       {
        "value": "C19.9"
       },
       {
        "value": "C20.9"
       }
      ]
     }
    ]
   }
  },
  {
   "name": "Right Colon",
   "studyViewFilter": {
    "clinicalDataFilters": [
     {
      "attributeId": "ICD_O_3_SITE",
      "values": [
       {
        "value": "C18.0"
       },
       {
        "value": "C18.5"
       },
       {
        "value": "C18.6"
       },
       {
        "value": "C18.7"
       }
      ]
     }
    ]
   }
  }
 ],
 "tab": "alterations"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/comparison/alterations?comparisonId=6ab5c8b9c2115c492d884e05","url":"https://www.cbioportal.org/comparison/alterations?comparisonId=6ab5c8b9c2115c492d884e05","data":{"description":"Group comparison (2 custom groups)","studies":["coadread_tcga_pan_can_atlas_2018"],"totalGroups":2,"groups":[{"name":"Left Colon/Rectum","sampleCount":283},{"name":"Right Colon","sampleCount":208}],"studyViewUrl":"https://www.cbioportal.org/study?id=coadread_tcga_pan_can_atlas_2018","groupUrls":[{"groupName":"Left Colon/Rectum","url":"https://www.cbioportal.org/study?id=coadread_tcga_pan_can_atlas_2018#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22ICD_O_3_SITE%22%2C%22values%22%3A%5B%7B%22value%22%3A%22C18.2%22%7D%2C%7B%22value%22%3A%22C18.3%22%7D%2C%7B%22value%22%3A%22C18.4%22%7D%2C%7B%22value%22%3A%22C19.9%22%7D%2C%7B%22value%22%3A%22C20.9%22%7D%5D%7D%5D%7D"},{"groupName":"Right Colon","url":"https://www.cbioportal.org/study?id=coadread_tcga_pan_can_atlas_2018#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22ICD_O_3_SITE%22%2C%22values%22%3A%5B%7B%22value%22%3A%22C18.0%22%7D%2C%7B%22value%22%3A%22C18.5%22%7D%2C%7B%22value%22%3A%22C18.6%22%7D%2C%7B%22value%22%3A%22C18.7%22%7D%5D%7D%5D%7D"}],"pageDescription":"Comparison of genomic alteration rates across Left Colon/Rectum (283) vs Right Colon (208)."}}

▶ navigate_to_group_comparison
{
 "studyIds": [
  "coadread_tcga_pan_can_atlas_2018"
 ],
 "groups": [
  {
   "name": "Left Colon/Rectum",
   "studyViewFilter": {
    "clinicalDataFilters": [
     {
      "attributeId": "ICD_O_3_SITE",
      "values": [
       {
        "value": "C18.2"
       },
       {
        "value": "C18.3"
       },
       {
        "value": "C18.4"
       },
       {
        "value": "C19.9"
       },
       {
        "value": "C20.9"
       }
      ]
     }
    ]
   }
  },
  {
   "name": "Right Colon",
   "studyViewFilter": {
    "clinicalDataFilters": [
     {
      "attributeId": "ICD_O_3_SITE",
      "values": [
       {
        "value": "C18.0"
       },
       {
        "value": "C18.5"
       },
       {
        "value": "C18.6"
       },
       {
        "value": "C18.7"
       }
      ]
     }
    ]
   }
  }
 ],
 "tab": "mutations"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/comparison/mutations?comparisonId=6ab5c8b9c2115c492d884e05","url":"https://www.cbioportal.org/comparison/mutations?comparisonId=6ab5c8b9c2115c492d884e05","data":{"description":"Group comparison (2 custom groups)","studies":["coadread_tcga_pan_can_atlas_2018"],"totalGroups":2,"groups":[{"name":"Left Colon/Rectum","sampleCount":283},{"name":"Right Colon","sampleCount":208}],"studyViewUrl":"https://www.cbioportal.org/study?id=coadread_tcga_pan_can_atlas_2018","groupUrls":[{"groupName":"Left Colon/Rectum","url":"https://www.cbioportal.org/study?id=coadread_tcga_pan_can_atlas_2018#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22ICD_O_3_SITE%22%2C%22values%22%3A%5B%7B%22value%22%3A%22C18.2%22%7D%2C%7B%22value%22%3A%22C18.3%22%7D%2C%7B%22value%22%3A%22C18.4%22%7D%2C%7B%22value%22%3A%22C19.9%22%7D%2C%7B%22value%22%3A%22C20.9%22%7D%5D%7D%5D%7D"},{"groupName":"Right Colon","url":"https://www.cbioportal.org/study?id=coadread_tcga_pan_can_atlas_2018#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22ICD_O_3_SITE%22%2C%22values%22%3A%5B%7B%22value%22%3A%22C18.0%22%7D%2C%7B%22value%22%3A%22C18.5%22%7D%2C%7B%22value%22%3A%22C18.6%22%7D%2C%7B%22value%22%3A%22C18.7%22%7D%5D%7D%5D%7D"}],"pageDescription":"Mutation frequency (protein change) compared across Left Colon/Rectum (283) vs Right Colon (208)."}}

═ answer (success)
---

### **Interactive Comparison Links:**

**1. Overall Alterations Comparison**
[Compare left vs right CRC alterations](https://www.cbioportal.org/comparison/alterations?comparisonId=6ab5c8b9c2115c492d884e05) — View genome-wide mutation frequencies, CNAs, and other alterations across both groups

**2. Detailed Mutation Breakdown**
[Compare mutations by anatomical site](https://www.cbioportal.org/comparison/mutations?comparisonId=6ab5c8b9c2115c492d884e05) — Specific protein changes and mutation types in each location

---

### **Biological Interpretation:**

**Right-sided enrichment (serrated pathway):**
- **BRAF** mutations (12.5% R vs 9.2% L) suggest higher involvement of the CpG Island Methylator Phenotype (CIMP) pathway in right-sided tumors
- **APC** slightly elevated (69.2% vs 63.3%) — though both are nearly ubiquitous

**Left-sided preferences:**
- **FBXW7** enrichment (16.3% L vs 12.0% R) — more TP53-associated truncal instability in distal tumors
- **SMAD4** modestly elevated (12.4% vs 10.6%)

**Symmetric genes:**
- **KRAS** essentially identical (35.7% vs 35.6%) — affects both compartments equally

This left/right distinction reflects known molecular subtypes of CRC: right-sided tumors show higher MSI (microsatellite instability) and BRAF-driven serrated pathway activation, while left-sided tumors have more classical APC-driven adenoma-carcinoma sequences.