Q (haiku): Which cancer type has the highest average tumor mutational burden across all studies? ▶ read_guide { "uri": "cbioportal://clinical-data-guide" } ▶ clickhouse_list_tables {} ◀ 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) ◀ result {"tables":[{"name":"allele_specific_copy_number"},{"name":"alteration_driver_annotation"},{"name":"cancer_study"},{"name":"cancer_study_query_preferences"},{"name":"cancer_study_tags"},{"name":"cbioportal_sequence_state"},{"name":"clinical_attribute_counts"},{"name":"clinical_attribute_meta"},{"name":"clinical_data_derived"},{"name":"clinical_event"},{"name":"clinical_event_data"},{"name":"clinical_event_data_derived"},{"name":"clinical_event_derived"},{"name":"clinical_patient"},{"name":"clinical_sample"},{"name":"cna_event"},{"name":"cna_panel_gene_coverage"},{"name":"cna_wes_coverage"},{"name":"co_altered_genes_in_study"},{"name":"copy_number_seg"},{"name":"copy_number_seg_file"},{"name":"gene"},{"name":"gene_alias"},{"name":"gene_alteration_frequency_by_cancer_type"},{"name":"gene_cna_distribution_in_study"},{"name":"gene_mutation_frequency_by_cancer_type"},{"name":"gene_mutation_frequency_in_studies"},{"name":"gene_mutation_frequency_in_study"},{"name":"gene_mutation_variants_in_study"},{"name":"gene_pair_coexpression"},{"name":"gene_panel"},{"name":"gene_panel_list"},{"name":"gene_panel_to_gene_derived"},{"name":"generic_assay_data_derived"},{"name":"generic_assay_meta_derived"},{"name":"generic_assay_profile_entity_derived"},{"name":"generic_entity_properties"},{"name":"geneset"},{"name":"geneset_gene"},{"name":"geneset_hierarchy_leaf"},{"name":"geneset_hierarchy_node"},{"name":"genetic_alteration"},{"name":"genetic_alteration_derived"},{"name":"genetic_entity"},{"name":"genetic_profile"},{"name":"genetic_profile_link"},{"name":"genetic_profile_samples"},{"name":"genomic_event_derived"},{"name":"gistic"},{"name":"gistic_to_gene"},{"name":"info"},{"name":"mut_sig"},{"name":"mutation"},{"name":"mutation_count_by_keyword"},{"name":"mutation_derived"},{"name":"mutation_event"},{"name":"mutation_panel_gene_coverage"},{"name":"mutation_wes_coverage"},{"name":"patient"},{"name":"reference_genome"},{"name":"reference_genome_gene"},{"name":"resource_definition"},{"name":"resource_patient"},{"name":"resource_sample"},{"name":"resource_study"},{"name":"sample"},{"name":"sample_cna_event"},{"name":"sample_derived"},{"name":"sample_list"},{"name":"sample_list_list"},{"name":"sample_profile"},{"name":"sample_to_gene_panel_derived"},{"name":"structural_variant"},{"name":"sv_panel_gene_coverage"},{"name":"sv_wes_coverage"},{"name":"top_cna_genes_in_study"},{"name":"top_mutated_genes_in_cohort"},{"name":"top_mutated_genes_in_study"},{"name":"top_sv_genes_in_study"},{"name":"treatment_counts_in_study"},{"name":"treatment_regimens_in_study"},{"name":"type_of_cancer"}]} ▶ clickhouse_list_table_columns { "table": "clinical_data_derived" } ▶ clickhouse_list_table_columns { "table": "cancer_study" } ◀ 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)."}]} ◀ result {"columns":[{"name":"cancer_study_id","type":"Int64","comment":"Internal numeric ID. Prefer cancer_study_identifier for queries as it is more readable and stable."},{"name":"cancer_study_identifier","type":"Nullable(String)","comment":"Stable string identifier for the study (e.g., \"msk_chord_2024\", \"brca_tcga\"). Use this for filtering, not cancer_study_id."},{"name":"type_of_cancer_id","type":"String"},{"name":"name","type":"String","comment":"Full descriptive name of the study (e.g., \"MSK-CHORD (MSK, Nature 2024)\")."},{"name":"description","type":"String"},{"name":"public","type":"Int32"},{"name":"pmid","type":"Nullable(String)"},{"name":"citation","type":"Nullable(String)"},{"name":"groups","type":"Nullable(String)"},{"name":"status","type":"Nullable(Int64)"},{"name":"import_date","type":"Nullable(DateTime64(6))"},{"name":"reference_genome_id","type":"Nullable(Int64)"},{"name":"sample_count","type":"UInt32","comment":"Samples in the study (members of _all), as shown in the portal study list. Precomputed daily at LLM-prep time."},{"name":"mutation_sample_count","type":"UInt32","comment":"Samples profiled for mutations (_sequenced) — portal \"Data type\" filter: \"Mutations\". 0 = no mutation data."},{"name":"cna_sample_count","type":"UInt32","comment":"Samples profiled for copy-number alterations (_cna) — \"CNA\". 0 = no CNA data."},{"name":"structural_variant_sample_count","type":"UInt32","comment":"Distinct samples with at least one structural variant (fusions etc.). 0 = none."},{"name":"rna_seq_sample_count","type":"UInt32","comment":"Samples with RNA-Seq expression (_rna_seq_v2_mrna) — \"RNA-Seq\"."},{"name":"mrna_microarray_sample_count","type":"UInt32","comment":"Samples with microarray mRNA expression (_mrna) — \"RNA (microarray)\"."},{"name":"mirna_sample_count","type":"UInt32","comment":"Samples with microRNA expression (_microrna) — \"miRNA\"."},{"name":"rppa_sample_count","type":"UInt32","comment":"Samples with RPPA protein levels (_rppa) — \"RPPA\"."},{"name":"mass_spectrometry_sample_count","type":"UInt32","comment":"Samples with mass-spectrometry protein quantification (_protein_quantification) — \"Protein Mass-Spectrometry\"."},{"name":"treatment_patient_count","type":"UInt32","comment":"PATIENTS (not samples) with treatment clinical events — \"Treatment\". 0 = no treatment data."},{"name":"resource_sample_counts","type":"Map(String, UInt32)","comment":"Samples with each linked resource, keyed by display name: imaging and pathology such as 'Slide Microscopy', 'Computed Tomography', 'Magnetic Resonance', 'H&E Slide', 'MxIF Image'. Query with mapKeys(resource_sample_counts) or resource_sample_counts['Slide Microscopy'] > 0."},{"name":"mrna_expression_sample_count","type":"UInt32","comment":"Samples in any mRNA expression profile (RNA-Seq, microarray, TPM, z-scores; from sample_profile). Use this for \"does the study have expression data\" — rna_seq_sample_count and mrna_m … (136 more chars) ▶ clickhouse_run_select_query { "query": " SELECT cst.type_of_cancer_id, COUNT(DISTINCT cdd.sample_unique_id) as n_samples, ROUND(AVG(toFloat64OrNull(cdd.attribute_value)), 2) as mean_tmb, ROUND(QUANTILE(0.5)(toFloat64OrNull(cdd.attribute_value)), 2) as median_tmb FROM clinical_data_derived cdd JOIN cancer_study cs ON cdd.cancer_study_identifier = cs.cancer_study_identifier JOIN type_of_cancer cst ON cs.type_of_cancer_id = cst.type_of_cancer_id WHERE cdd.attribute_name = 'TMB_NONSYNONYMOUS' AND toFloat64OrNull(cdd.attribute_value) IS NOT NULL GROUP BY cst.type_of_cancer_id ORDER BY mean_tmb DESC LIMIT 20 ", "max_rows": 100 } ◀ 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 `QUANTILE` does not exist. In scope SELECT cst.type_of_cancer_id, COUNTDistinct(cdd.sample_unique_id) AS n_samples, ROUND(AVG(toFloat64OrNull(cdd.attribute_value)), 2) AS mean_tmb, ROUND(QUANTILE(0.5)(toFloat64OrNull(cdd.attribute_value)), 2) AS median_tmb FROM clinical_data_derived AS cdd INNER JOIN cancer_study AS cs ON cdd.cancer_study_identifier = cs.cancer_study_identifier INNER JOIN type_of_cancer AS cst ON cs.type_of_cancer_id = cst.type_of_cancer_id WHERE (cdd.attribute_name = 'TMB_NONSYNONYMOUS') AND (toFloat64OrNull(cdd.attribute_value) IS NOT NULL) GROUP BY cst.type_of_cancer_id ORDER BY mean_tmb DESC LIMIT 20. Maybe you meant: ['quantile','quantiles']. (UNKNOWN_FUNCTION) (version 26.4.1.2359 (official build))\n"} ▶ clickhouse_run_select_query { "query": " SELECT cst.type_of_cancer_id, COUNT(DISTINCT cdd.sample_unique_id) as n_samples, ROUND(AVG(toFloat64OrNull(cdd.attribute_value)), 2) as mean_tmb, ROUND(quantile(0.5)(toFloat64OrNull(cdd.attribute_value)), 2) as median_tmb FROM clinical_data_derived cdd JOIN cancer_study cs ON cdd.cancer_study_identifier = cs.cancer_study_identifier JOIN type_of_cancer cst ON cs.type_of_cancer_id = cst.type_of_cancer_id WHERE cdd.attribute_name = 'TMB_NONSYNONYMOUS' AND toFloat64OrNull(cdd.attribute_value) IS NOT NULL GROUP BY cst.type_of_cancer_id ORDER BY mean_tmb DESC LIMIT 20 ", "max_rows": 100 } ◀ result {"rows":[{"cst.type_of_cancer_id":"nfib","n_samples":38,"mean_tmb":56.85,"median_tmb":5.38},{"cst.type_of_cancer_id":"cscc","n_samples":176,"mean_tmb":53.17,"median_tmb":34.47},{"cst.type_of_cancer_id":"bcc","n_samples":293,"mean_tmb":52.74,"median_tmb":44},{"cst.type_of_cancer_id":"mpnst","n_samples":15,"mean_tmb":50.65,"median_tmb":53.24},{"cst.type_of_cancer_id":"desm","n_samples":20,"mean_tmb":29.49,"median_tmb":25.25},{"cst.type_of_cancer_id":"lymph","n_samples":34,"mean_tmb":22.4,"median_tmb":22.04},{"cst.type_of_cancer_id":"uec","n_samples":247,"mean_tmb":20.28,"median_tmb":2.37},{"cst.type_of_cancer_id":"skcm","n_samples":2931,"mean_tmb":19.85,"median_tmb":10.97},{"cst.type_of_cancer_id":"mel","n_samples":442,"mean_tmb":19.51,"median_tmb":8.28},{"cst.type_of_cancer_id":"ucec","n_samples":4016,"mean_tmb":16.04,"median_tmb":3.46},{"cst.type_of_cancer_id":"utuc","n_samples":349,"mean_tmb":15.97,"median_tmb":5.55},{"cst.type_of_cancer_id":"bladder","n_samples":2363,"mean_tmb":15.68,"median_tmb":9.79},{"cst.type_of_cancer_id":"coad","n_samples":949,"mean_tmb":14.94,"median_tmb":3.5},{"cst.type_of_cancer_id":"read","n_samples":7432,"mean_tmb":13.86,"median_tmb":6.05},{"cst.type_of_cancer_id":"schw","n_samples":40,"mean_tmb":12.77,"median_tmb":3.52},{"cst.type_of_cancer_id":"coadread","n_samples":5001,"mean_tmb":12.6,"median_tmb":4.53},{"cst.type_of_cancer_id":"stad","n_samples":1939,"mean_tmb":10.64,"median_tmb":3.33},{"cst.type_of_cancer_id":"lung","n_samples":682,"mean_tmb":10.47,"median_tmb":7.81},{"cst.type_of_cancer_id":"bowel","n_samples":954,"mean_tmb":10.21,"median_tmb":6.05},{"cst.type_of_cancer_id":"sclc","n_samples":249,"mean_tmb":9.5,"median_tmb":6.53}]} ▶ clickhouse_run_select_query { "query": " SELECT type_of_cancer_id, name FROM type_of_cancer WHERE type_of_cancer_id IN ('nfib', 'cscc', 'bcc') ORDER BY type_of_cancer_id ", "max_rows": 10 } ◀ result {"rows":[{"type_of_cancer_id":"bcc","name":"Basal Cell Carcinoma"},{"type_of_cancer_id":"cscc","name":"Cutaneous Squamous Cell Carcinoma"},{"type_of_cancer_id":"nfib","name":"Neurofibroma"}]} ▶ clickhouse_run_select_query { "query": " SELECT DISTINCT cs.cancer_study_identifier, cs.name FROM cancer_study cs WHERE cs.type_of_cancer_id = 'nfib' ORDER BY cs.name ", "max_rows": 20 } ◀ result {"rows":[{"cancer_study_identifier":"nfib_ctf_biobank_2025","name":"Cutaneous Neurofibroma Data Resource (CTF, Scientific Data 2021)"}]} ═ answer (success) ## Summary **Neurofibroma (NFIB)** has the highest average tumor mutational burden across all cBioPortal studies: | Metric | Value | |--------|-------| | **Mean TMB** | **56.85 mutations/Mb** | | Median TMB | 5.38 mutations/Mb | | Samples | 38 | ### Top 5 Cancer Types by Mean TMB: 1. **Neurofibroma (NFIB)** — 56.85 mean TMB (5.38 median) 2. **Cutaneous Squamous Cell Carcinoma (CSCC)** — 53.17 mean TMB (34.47 median) 3. **Basal Cell Carcinoma (BCC)** — 52.74 mean TMB (44 median) 4. **Malignant Peripheral Nerve Sheath Tumor (MPNST)** — 50.65 mean TMB (53.24 median) 5. **Desmoplastic Melanoma (DESM)** — 29.49 mean TMB (25.25 median) **Important note:** The high mean for NFIB is driven by a small sample size (n=38), with a much lower median (5.38), suggesting outlier hypermutatiors. In contrast, skin cancers like CSCC and BCC show more consistent TMB elevation across samples (higher medians relative to means). UCEC (Uterine Corpus Endometrial Carcinoma, 16.04 mean TMB) is the most commonly studied hypermutated cancer type with 4,016 samples.