Q (haiku): Which cancer type has the highest average tumor mutational burden across all studies?

▶ read_guide
{
 "uri": "cbioportal://clinical-data-guide"
}

◀ result
# Clinical Data Query Guide

## Overview
Clinical data in cBioPortal is stored at both patient and sample levels. Understanding the distinction is crucial for accurate analysis.

## Data Organization

### Patient-Level vs Sample-Level Data
- **Patient-level**: Demographics, overall survival, disease stage (stored once per patient)
- **Sample-level**: Sample type, sequencing platform, purity (can have multiple per patient)

### Key Tables
- `clinical_patient`: Patient-level clinical attributes
- `clinical_sample`: Sample-level clinical attributes
- `clinical_data_derived`: Pre-joined view combining both levels
- `clinical_attribute_meta`: Metadata about available clinical attributes

## Recommended Approach: Use clinical_data_derived

The `clinical_data_derived` table is pre-joined and optimized for most queries:

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

## Clinical Attribute Discovery

### Use clinical_attribute_meta for Discovering Available Attributes
**Always start here** to see what clinical attributes are available for a specific study:

```sql
-- Discover available clinical attributes for a study
SELECT
    attr_id,
    description,
    patient_attribute,
    cancer_study_id
FROM clinical_attribute_meta
WHERE
    cancer_study_id = (
        SELECT cancer_study_id
        FROM cancer_study
        WHERE cancer_study_identifier = 'your_study_id'
    )
ORDER BY patient_attribute, attr_id;
```

**Key Fields:**
- **attr_id**: matches attr_id in clinical_sample/clinical_patient tables
- **description**: human-readable description of the attribute
- **patient_attribute**: true = patient attribute, false = sample attribute
- **cancer_study_id**: links to cancer_study table (filter by study)

## Attribute Semantics and Matching

### Case-Insensitive Matching for Attribute Values

Clinical values are free text across studies and may differ only by case. For example, a controlled-looking value such as germline mutation status may appear as `GERMLINE`, `Germline`, or another case variant.

When filtering `clinical_data_derived.attribute_value`, use case-insensitive matching unless you have already profiled the exact values in the target study:

```sql
-- Correct: case-insensitive clinical value filter
SELECT DISTINCT sample_unique_id, patient_unique_id
FROM clinical_data_derived
WHERE cancer_study_identifier = 'your_study_id'
  AND attribute_name = 'MUTATION_STATUS'
  AND upper(attribute_value) = 'GERMLINE';
```

Do not write `attribute_value = 'GERMLINE'` without first checking all distinct values for that attribute in the study.

### Query the Requested Attribute, Not a Proxy

Do not infer one clinical attribute from a related subtype or marker. Query the actual requested attribute when it … (17073 more chars)

▶ clickhouse_run_select_query
{
 "query": "
-- Get TMB (Tumor Mutational Burden) by cancer type across pan_cancer_tcga studies
SELECT 
    cancer_study_identifier AS study,
    anyIf(cancer_type, cancer_type != '') AS cancer_type,
    COUNT(*) AS n_samples,
    ROUND(AVG(tmb_value), 2) AS mean_tmb,
    ROUND(quantile(0.5)(tmb_value), 2) AS median_tmb,
    ROUND(MIN(tmb_value), 2) AS min_tmb,
    ROUND(MAX(tmb_value), 2) AS max_tmb
FROM (
    SELECT 
        cd.cancer_study_identifier,
        cd.sample_unique_id,
        maxIf(cd.attribute_value, cd.attribute_name = 'CANCER_TYPE') AS cancer_type,
        toFloat64OrNull(maxIf(cd.attribute_value, cd.attribute_name = 'TMB_NONSYNONYMOUS')) AS tmb_value
    FROM clinical_data_derived cd
    WHERE cd.cancer_study_identifier IN (
        SELECT cancer_study_identifier 
        FROM cancer_study_query_preferences 
        WHERE preference_name = 'pan_cancer_tcga'
    )
    AND cd.attribute_name IN ('TMB_NONSYNONYMOUS', 'CANCER_TYPE')
    GROUP BY cd.cancer_study_identifier, cd.sample_unique_id
)
WHERE tmb_value IS NOT NULL
GROUP BY cancer_study_identifier
ORDER BY mean_tmb DESC
",
 "max_rows": 50
}

◀ result
{"rows":[{"study":"ucec_tcga_pan_can_atlas_2018","cancer_type":"Endometrial Cancer","n_samples":517,"mean_tmb":35.66,"median_tmb":2.57,"min_tmb":0,"max_tmb":861.87},{"study":"skcm_tcga_pan_can_atlas_2018","cancer_type":"Melanoma","n_samples":440,"mean_tmb":25.91,"median_tmb":14.88,"min_tmb":0.2,"max_tmb":1052.2},{"study":"coadread_tcga_pan_can_atlas_2018","cancer_type":"Colorectal Cancer","n_samples":534,"mean_tmb":13.64,"median_tmb":3.5,"min_tmb":0,"max_tmb":382.63},{"study":"stad_tcga_pan_can_atlas_2018","cancer_type":"Esophagogastric Cancer","n_samples":436,"mean_tmb":12.69,"median_tmb":3.9,"min_tmb":0,"max_tmb":330.8},{"study":"luad_tcga_pan_can_atlas_2018","cancer_type":"Non-Small Cell Lung Cancer","n_samples":566,"mean_tmb":10.02,"median_tmb":6.68,"min_tmb":0,"max_tmb":69.53},{"study":"lusc_tcga_pan_can_atlas_2018","cancer_type":"Non-Small Cell Lung Cancer","n_samples":484,"mean_tmb":9.43,"median_tmb":7.67,"min_tmb":0,"max_tmb":53.37},{"study":"blca_tcga_pan_can_atlas_2018","cancer_type":"Bladder Cancer","n_samples":410,"mean_tmb":8.44,"median_tmb":5.77,"min_tmb":0,"max_tmb":118.63},{"study":"cesc_tcga_pan_can_atlas_2018","cancer_type":"Cervical Cancer","n_samples":291,"mean_tmb":6.48,"median_tmb":2.83,"min_tmb":0,"max_tmb":379.2},{"study":"hnsc_tcga_pan_can_atlas_2018","cancer_type":"Head and Neck Cancer","n_samples":515,"mean_tmb":5.41,"median_tmb":3.53,"min_tmb":0,"max_tmb":91.67},{"study":"esca_tcga_pan_can_atlas_2018","cancer_type":"Esophagogastric Cancer","n_samples":182,"mean_tmb":4.87,"median_tmb":3.53,"min_tmb":0.03,"max_tmb":56.97},{"study":"ucs_tcga_pan_can_atlas_2018","cancer_type":"Endometrial Cancer","n_samples":57,"mean_tmb":4.28,"median_tmb":1.57,"min_tmb":0.9,"max_tmb":122.4},{"study":"dlbc_tcga_pan_can_atlas_2018","cancer_type":"Mature B-Cell Neoplasms","n_samples":41,"mean_tmb":3.96,"median_tmb":3.6,"min_tmb":0,"max_tmb":12.73},{"study":"paad_tcga_pan_can_atlas_2018","cancer_type":"Pancreatic Cancer","n_samples":179,"mean_tmb":3.89,"median_tmb":1.17,"min_tmb":0,"max_tmb":479.3},{"study":"gbm_tcga_pan_can_atlas_2018","cancer_type":"Glioblastoma","n_samples":397,"mean_tmb":3.89,"median_tmb":1.7,"min_tmb":0,"max_tmb":407.83},{"study":"lihc_tcga_pan_can_atlas_2018","cancer_type":"Hepatobiliary Cancer","n_samples":366,"mean_tmb":3.52,"median_tmb":2.83,"min_tmb":0,"max_tmb":44.1},{"study":"acc_tcga_pan_can_atlas_2018","cancer_type":"Adrenocortical Carcinoma","n_samples":91,"mean_tmb":2.78,"median_tmb":0.9,"min_tmb":0.13,"max_tmb":62.07},{"study":"brca_tcga_pan_can_atlas_2018","cancer_type":"Breast Cancer","n_samples":1066,"mean_tmb":2.73,"median_tmb":1.3,"min_tmb":0,"max_tmb":180.83},{"study":"kirp_tcga_pan_can_atlas_2018","cancer_type":"Renal Non-Clear Cell Carcinoma","n_samples":276,"mean_tmb":2.73,"median_tmb":2.23,"min_tmb":0,"max_tmb":34.5},{"study":"chol_tcga_pan_can_atlas_2018","cancer_type":"Cholangiocarcinoma","n_samples":36,"mean_tmb":2.32,"median_tmb":1.45,"min_tmb":0.2,"max_tmb":21.83},{"study":"sarc_tcga_pan_can_at … (1949 more chars)

▶ resolve_and_route
{
 "studyIds": [
  "ucec_tcga_pan_can_atlas_2018",
  "skcm_tcga_pan_can_atlas_2018"
 ]
}

◀ result
{"success":true,"message":"Found 2 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":2,"studiesWithMetadata":[{"studyId":"ucec_tcga_pan_can_atlas_2018","name":"Uterine Corpus Endometrial Carcinoma (TCGA, PanCancer Atlas)","sampleCount":529,"studyViewUrl":"https://www.cbioportal.org/study?id=ucec_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":["ucec_tcga_pan_can_atlas_2018_gistic","ucec_tcga_pan_can_atlas_2018_log2CNA","ucec_tcga_pan_can_atlas_2018_mutations","ucec_tcga_pan_can_atlas_2018_rna_seq_v2_mrna","ucec_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","ucec_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","ucec_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","ucec_tcga_pan_can_atlas_2018_rppa","ucec_tcga_pan_can_atlas_2018_rppa_Zscores","ucec_tcga_pan_can_atlas_2018_structural_variants"],"genericAssayProfiles":["ucec_tcga_pan_can_atlas_2018_armlevel_cna","ucec_tcga_pan_can_atlas_2018_genetic_ancestry","ucec_tcga_pan_can_atlas_2018_methylation_hm27_hm450_merge","ucec_tcga_pan_can_atlas_2018_methylation_hm450"],"heatmapProfileIds":["ucec_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","ucec_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","ucec_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","ucec_tcga_pan_can_atlas_2018_rppa_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","protein","generic_assay_armlevel_cna","generic_assay_genetic_ancestry","generic_assay_methylation"]}},{"studyId":"skcm_tcga_pan_can_atlas_2018","name":"S … (2429 more chars)

═ answer (success)
## Results

**Endometrial Cancer (UCEC)** has the highest average tumor mutational burden across all TCGA studies, with a **mean TMB of 35.66 mutations/Mb** across 517 samples in the pan-cancer TCGA cohort.

### Top 3 Cancer Types by Average TMB:

| Cancer Type | Study | Mean TMB | Median TMB | Samples |
|---|---|---|---|---|
| **Endometrial Cancer** | [Uterine Corpus Endometrial Carcinoma (TCGA, PanCancer Atlas)](https://www.cbioportal.org/study?id=ucec_tcga_pan_can_atlas_2018) | **35.66** | 2.57 | 517 |
| **Melanoma** | [Skin Cutaneous Melanoma (TCGA, PanCancer Atlas)](https://www.cbioportal.org/study?id=skcm_tcga_pan_can_atlas_2018) | **25.91** | 14.88 | 440 |
| **Colorectal Cancer** | Colorectal Adenocarcinoma (TCGA, PanCancer Atlas) | **13.64** | 3.50 | 534 |

### Key Observations:

1. **Endometrial cancer** shows extremely high mean TMB (35.66) but a much lower median (2.57), indicating the presence of hypermutated outliers. This is characteristic of studies with mismatch repair (MMR)-deficient tumors.

2. **Melanoma** has a more balanced distribution, with both high mean (25.91) and high median (14.88) TMB, reflecting UV-driven mutagenesis across samples.

3. The mean and median TMB differ substantially across cancers, which is why reporting both metrics is important — mean is skewed by hypermutators, while median better represents the typical patient.

These statistics are based on the **TMB_NONSYNONYMOUS** attribute (mutations/Mb) measured across all profiled samples in the TCGA PanCancer Atlas cohort.