Q (haiku): What is the correlation between tumor mutational burden and microsatellite instability status in colorectal cancer patients from the MSK-CHORD Study? ▶ read_guide { "uri": "cbioportal://clinical-data-guide" } ▶ list_studies { "search": "MSK-CHORD colorectal", "verbose": true } ◀ 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 {"result":[]} ▶ list_studies { "search": "MSK-CHORD", "verbose": true, "limit": 20 } ◀ result {"result":[{"cancer_study_identifier":"msk_chord_2024","name":"MSK-CHORD (MSK, Nature 2024)","description":"Targeted sequencing of 25040 tumors from 24950 patients and their matched normals via MSK-IMPACT, along with clinical annotations, some of which are derived from natural language processing (denoted NLP). This data is available under the Creative Commons BY-NC-ND 4.0 license. For commercial use, please contact datarequests@mskcc.org","type_of_cancer_id":"mixed","sample_count":25040,"has_guide":true,"url":"https://www.cbioportal.org/study/summary?id=msk_chord_2024"}]} ▶ clickhouse_list_tables {} ▶ resolve_and_route { "studyIds": [ "msk_chord_2024" ] } ◀ 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"}]} ◀ result {"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"msk_chord_2024","name":"MSK-CHORD (MSK, Nature 2024)","sampleCount":25040,"studyViewUrl":"https://www.cbioportal.org/study?id=msk_chord_2024","metadata":{"clinicalAttributeIds":["ADRENAL_GLANDS","BONE","CANCER_TYPE","CANCER_TYPE_DETAILED","CLINICAL_GROUP","CLINICAL_SUMMARY","CNS_BRAIN","CURRENT_AGE_DEID","DIAGNOSIS_DESCRIPTION","ETHNICITY","FRACTION_GENOME_ALTERED","GENDER","GENE_PANEL","GLEASON_FIRST_REPORTED","GLEASON_HIGHEST_REPORTED","GLEASON_SAMPLE_LEVEL","HER2","HISTORY_OF_PDL1","HR","ICD_O_HISTOLOGY_DESCRIPTION","INTRA_ABDOMINAL","LIVER","LUNG","LYMPH_NODES","METASTATIC_SITE","MSI_COMMENT","MSI_SCORE","MSI_TYPE","MUTATION_COUNT","NUM_ICDO_DX","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER","PATHOLOGICAL_GROUP","PDL1_POSITIVE","PLEURA","PRIMARY_SITE","PRIOR_MED_TO_MSK","RACE","REPRODUCTIVE_ORGANS","SAMPLE_CLASS","SAMPLE_COUNT","SAMPLE_COVERAGE","SAMPLE_TYPE","SMOKING_PREDICTIONS_3_CLASSES","SOMATIC_STATUS","STAGE_HIGHEST_RECORDED","TMB_NONSYNONYMOUS","TUMOR_PURITY"],"molecularProfileIds":["msk_chord_2024_cna","msk_chord_2024_mutations","msk_chord_2024_structural_variants"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations"]}}]}} ▶ clickhouse_run_select_query { "query": " -- First, identify colorectal cancer samples in MSK-CHORD SELECT DISTINCT attribute_value FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name IN ('CANCER_TYPE', 'CANCER_TYPE_DETAILED') AND upper(attribute_value) LIKE '%COLORECTAL%' ORDER BY attribute_value ", "max_rows": 100 } ◀ result {"rows":[{"attribute_value":"Colorectal Adenocarcinoma"},{"attribute_value":"Colorectal Cancer"}]} ▶ clickhouse_run_select_query { "query": " -- Extract TMB and MSI data for colorectal cancer patients in MSK-CHORD WITH crc_samples AS ( SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'CANCER_TYPE' AND attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma') ) SELECT cs.sample_unique_id, cs.patient_unique_id, MAX(CASE WHEN attribute_name = 'TMB_NONSYNONYMOUS' THEN toFloat64OrNull(attribute_value) END) as tmb, MAX(CASE WHEN attribute_name = 'MSI_TYPE' THEN attribute_value END) as msi_type, MAX(CASE WHEN attribute_name = 'MSI_SCORE' THEN toFloat64OrNull(attribute_value) END) as msi_score FROM crc_samples cs LEFT JOIN clinical_data_derived cdd ON cs.sample_unique_id = cdd.sample_unique_id AND cancer_study_identifier = 'msk_chord_2024' AND attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE', 'MSI_SCORE') GROUP BY cs.sample_unique_id, cs.patient_unique_id HAVING tmb IS NOT NULL OR msi_type IS NOT NULL OR msi_score IS NOT NULL ORDER BY cs.sample_unique_id ", "max_rows": 10000 } ◀ result Error: result (875,406 characters) exceeds maximum allowed tokens. Output has been saved to /home/ino/.claude-cbioportal-team/projects/-tmp-mcp-qa-claude-3sjqt3v5/8b57c7de-5e66-470e-9946-9cfa3bf8413e/tool-results/mcp-claude_ai_cBioPortal_MCP-clickhouse_run_select_query-1790294205143.txt. Format: JSON with schema: {rows: [{...}]} - For targeted queries (find a value, filter by field): use jq on the file directly. - For analysis or summarization that requires reading the full content: first probe the structure (e.g., jq 'type, length, keys?' /home/ino/.claude-cbioportal-team/projects/-tmp-mcp-qa-claude-3sjqt3v5/8b57c7de-5e66-470e-9946-9cfa3bf8413e/tool-results/mcp-claude_ai_cBioPortal_MCP-clickhouse_run_select_query-1790294205143.txt), then extract slices with jq or python — Read's line-based offset/limit will not chunk this file. - If the Agent tool is available, do this inside a subagent so the full output stays out of your main context. Give it the instruction above verbatim, and be explicit about what it must return — e.g. "/home/ino/.claude-cbioportal-team/projects/-tmp-mcp-qa-claude-3sjqt3v5/8b57c7de-5e66-470e-9946-9cfa3bf8413e/tool-results/mcp-claude_ai_cBioPortal_MCP-clickhouse_run_select_query-1790294205143.txt is JSON with schema: {rows: [{...}]}; probe the structure with jq (type/length/keys), then extract and read the content in full with jq or python, then summarize and quote any key findings verbatim." A vague "summarize this" may lose detail. ▶ clickhouse_run_select_query { "query": " -- Get summary statistics on TMB and MSI correlation for colorectal cancer in MSK-CHORD WITH crc_samples AS ( SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'CANCER_TYPE' AND attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma') ), tmb_msi_data AS ( SELECT cs.sample_unique_id, cs.patient_unique_id, MAX(CASE WHEN attribute_name = 'TMB_NONSYNONYMOUS' THEN toFloat64OrNull(attribute_value) END) as tmb, MAX(CASE WHEN attribute_name = 'MSI_TYPE' THEN attribute_value END) as msi_type, MAX(CASE WHEN attribute_name = 'MSI_SCORE' THEN toFloat64OrNull(attribute_value) END) as msi_score FROM crc_samples cs LEFT JOIN clinical_data_derived cdd ON cs.sample_unique_id = cdd.sample_unique_id AND cdd.cancer_study_identifier = 'msk_chord_2024' AND cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE', 'MSI_SCORE') GROUP BY cs.sample_unique_id, cs.patient_unique_id ) SELECT 'Summary' as metric, COUNT(*) as total_samples, SUM(CASE WHEN tmb IS NOT NULL THEN 1 ELSE 0 END) as samples_with_tmb, SUM(CASE WHEN msi_type IS NOT NULL THEN 1 ELSE 0 END) as samples_with_msi_type, SUM(CASE WHEN msi_score IS NOT NULL THEN 1 ELSE 0 END) as samples_with_msi_score, SUM(CASE WHEN tmb IS NOT NULL AND (msi_type IS NOT NULL OR msi_score IS NOT NULL) THEN 1 ELSE 0 END) as samples_with_both FROM tmb_msi_data ", "max_rows": 10 } ◀ result {"rows":[{"metric":"Summary","total_samples":5543,"samples_with_tmb":5543,"samples_with_msi_type":5543,"samples_with_msi_score":5505,"samples_with_both":5543}]} ▶ clickhouse_run_select_query { "query": " -- Correlation analysis: TMB by MSI type (categorical) WITH crc_samples AS ( SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'CANCER_TYPE' AND attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma') ), tmb_msi_data AS ( SELECT cs.sample_unique_id, MAX(CASE WHEN attribute_name = 'TMB_NONSYNONYMOUS' THEN toFloat64OrNull(attribute_value) END) as tmb, MAX(CASE WHEN attribute_name = 'MSI_TYPE' THEN attribute_value END) as msi_type, MAX(CASE WHEN attribute_name = 'MSI_SCORE' THEN toFloat64OrNull(attribute_value) END) as msi_score FROM crc_samples cs LEFT JOIN clinical_data_derived cdd ON cs.sample_unique_id = cdd.sample_unique_id AND cdd.cancer_study_identifier = 'msk_chord_2024' AND cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE', 'MSI_SCORE') GROUP BY cs.sample_unique_id ) SELECT msi_type, COUNT(*) as n_samples, ROUND(AVG(tmb), 2) as mean_tmb, ROUND(QUANTILE(0.5)(tmb), 2) as median_tmb, ROUND(MIN(tmb), 2) as min_tmb, ROUND(MAX(tmb), 2) as max_tmb, ROUND(STDDEV(tmb), 2) as stddev_tmb FROM tmb_msi_data WHERE tmb IS NOT NULL AND msi_type IS NOT NULL GROUP BY msi_type ORDER BY median_tmb DESC ", "max_rows": 50 } ◀ 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 WITH crc_samples AS (SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE (cancer_study_identifier = 'msk_chord_2024') AND (attribute_name = 'CANCER_TYPE') AND (attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma'))), tmb_msi_data AS (SELECT cs.sample_unique_id, MAX(multiIf(attribute_name = 'TMB_NONSYNONYMOUS', toFloat64OrNull(attribute_value), NULL)) AS tmb, MAX(multiIf(attribute_name = 'MSI_TYPE', attribute_value, NULL)) AS msi_type, MAX(multiIf(attribute_name = 'MSI_SCORE', toFloat64OrNull(attribute_value), NULL)) AS msi_score FROM crc_samples AS cs LEFT JOIN clinical_data_derived AS cdd ON (cs.sample_unique_id = cdd.sample_unique_id) AND (cdd.cancer_study_identifier = 'msk_chord_2024') AND (cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE', 'MSI_SCORE')) GROUP BY cs.sample_unique_id) SELECT msi_type, COUNT(*) AS n_samples, ROUND(AVG(tmb), 2) AS mean_tmb, ROUND(QUANTILE(0.5)(tmb), 2) AS median_tmb, ROUND(MIN(tmb), 2) AS min_tmb, ROUND(MAX(tmb), 2) AS max_tmb, ROUND(STDDEV(tmb), 2) AS stddev_tmb FROM tmb_msi_data WHERE (tmb IS NOT NULL) AND (msi_type IS NOT NULL) GROUP BY msi_type ORDER BY median_tmb DESC. Maybe you meant: ['quantile','quantiles']. (UNKNOWN_FUNCTION) (version 26.4.1.2359 (official build))\n"} ▶ clickhouse_run_select_query { "query": " -- Correlation analysis: TMB by MSI type (categorical) WITH crc_samples AS ( SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'CANCER_TYPE' AND attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma') ), tmb_msi_data AS ( SELECT cs.sample_unique_id, MAX(CASE WHEN attribute_name = 'TMB_NONSYNONYMOUS' THEN toFloat64OrNull(attribute_value) END) as tmb, MAX(CASE WHEN attribute_name = 'MSI_TYPE' THEN attribute_value END) as msi_type, MAX(CASE WHEN attribute_name = 'MSI_SCORE' THEN toFloat64OrNull(attribute_value) END) as msi_score FROM crc_samples cs LEFT JOIN clinical_data_derived cdd ON cs.sample_unique_id = cdd.sample_unique_id AND cdd.cancer_study_identifier = 'msk_chord_2024' AND cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE', 'MSI_SCORE') GROUP BY cs.sample_unique_id ) SELECT msi_type, COUNT(*) as n_samples, ROUND(AVG(tmb), 2) as mean_tmb, ROUND(quantile(0.5)(tmb), 2) as median_tmb, ROUND(MIN(tmb), 2) as min_tmb, ROUND(MAX(tmb), 2) as max_tmb, ROUND(STDDEV(tmb), 2) as stddev_tmb FROM tmb_msi_data WHERE tmb IS NOT NULL AND msi_type IS NOT NULL GROUP BY msi_type ORDER BY median_tmb DESC ", "max_rows": 50 } ◀ 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 `STDDEV` does not exist. In scope WITH crc_samples AS (SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE (cancer_study_identifier = 'msk_chord_2024') AND (attribute_name = 'CANCER_TYPE') AND (attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma'))), tmb_msi_data AS (SELECT cs.sample_unique_id, MAX(multiIf(attribute_name = 'TMB_NONSYNONYMOUS', toFloat64OrNull(attribute_value), NULL)) AS tmb, MAX(multiIf(attribute_name = 'MSI_TYPE', attribute_value, NULL)) AS msi_type, MAX(multiIf(attribute_name = 'MSI_SCORE', toFloat64OrNull(attribute_value), NULL)) AS msi_score FROM crc_samples AS cs LEFT JOIN clinical_data_derived AS cdd ON (cs.sample_unique_id = cdd.sample_unique_id) AND (cdd.cancer_study_identifier = 'msk_chord_2024') AND (cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE', 'MSI_SCORE')) GROUP BY cs.sample_unique_id) SELECT msi_type, COUNT(*) AS n_samples, ROUND(AVG(tmb), 2) AS mean_tmb, ROUND(quantile(0.5)(tmb), 2) AS median_tmb, ROUND(MIN(tmb), 2) AS min_tmb, ROUND(MAX(tmb), 2) AS max_tmb, ROUND(STDDEV(tmb), 2) AS stddev_tmb FROM tmb_msi_data WHERE (tmb IS NOT NULL) AND (msi_type IS NOT NULL) GROUP BY msi_type ORDER BY median_tmb DESC. (UNKNOWN_FUNCTION) (version 26.4.1.2359 (official build))\n"} ▶ clickhouse_run_select_query { "query": " -- Correlation analysis: TMB by MSI type (categorical) WITH crc_samples AS ( SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'CANCER_TYPE' AND attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma') ), tmb_msi_data AS ( SELECT cs.sample_unique_id, MAX(CASE WHEN attribute_name = 'TMB_NONSYNONYMOUS' THEN toFloat64OrNull(attribute_value) END) as tmb, MAX(CASE WHEN attribute_name = 'MSI_TYPE' THEN attribute_value END) as msi_type, MAX(CASE WHEN attribute_name = 'MSI_SCORE' THEN toFloat64OrNull(attribute_value) END) as msi_score FROM crc_samples cs LEFT JOIN clinical_data_derived cdd ON cs.sample_unique_id = cdd.sample_unique_id AND cdd.cancer_study_identifier = 'msk_chord_2024' AND cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE', 'MSI_SCORE') GROUP BY cs.sample_unique_id ) SELECT msi_type, COUNT(*) as n_samples, ROUND(AVG(tmb), 2) as mean_tmb, ROUND(quantile(0.5)(tmb), 2) as median_tmb, ROUND(MIN(tmb), 2) as min_tmb, ROUND(MAX(tmb), 2) as max_tmb, ROUND(sqrt(varSamp(tmb)), 2) as stddev_tmb FROM tmb_msi_data WHERE tmb IS NOT NULL AND msi_type IS NOT NULL GROUP BY msi_type ORDER BY median_tmb DESC ", "max_rows": 50 } ◀ result {"rows":[{"msi_type":"Instable","n_samples":586,"mean_tmb":64.63,"median_tmb":56.68,"min_tmb":0,"max_tmb":490.57,"stddev_tmb":42.19},{"msi_type":"Indeterminate","n_samples":188,"mean_tmb":18.57,"median_tmb":6.56,"min_tmb":1.64,"max_tmb":343.53,"stddev_tmb":41.67},{"msi_type":"Stable","n_samples":4682,"mean_tmb":7.58,"median_tmb":5.87,"min_tmb":0,"max_tmb":570.96,"stddev_tmb":20.23},{"n_samples":38,"mean_tmb":8.06,"median_tmb":3.69,"min_tmb":0,"max_tmb":95.12,"stddev_tmb":17.13},{"msi_type":"Do not report","n_samples":49,"mean_tmb":7.06,"median_tmb":3.33,"min_tmb":0,"max_tmb":74.31,"stddev_tmb":12.54}]} ▶ clickhouse_run_select_query { "query": " -- Continuous correlation analysis: TMB vs MSI score WITH crc_samples AS ( SELECT DISTINCT sample_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'CANCER_TYPE' AND attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma') ), tmb_msi_data AS ( SELECT cs.sample_unique_id, MAX(CASE WHEN attribute_name = 'TMB_NONSYNONYMOUS' THEN toFloat64OrNull(attribute_value) END) as tmb, MAX(CASE WHEN attribute_name = 'MSI_SCORE' THEN toFloat64OrNull(attribute_value) END) as msi_score FROM crc_samples cs LEFT JOIN clinical_data_derived cdd ON cs.sample_unique_id = cdd.sample_unique_id AND cdd.cancer_study_identifier = 'msk_chord_2024' AND cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_SCORE') GROUP BY cs.sample_unique_id ) SELECT COUNT(*) as n_pairs, ROUND(AVG(tmb), 2) as mean_tmb, ROUND(AVG(msi_score), 2) as mean_msi_score, ROUND(corr(tmb, msi_score), 3) as pearson_correlation, ROUND(sqrt(varSamp(tmb)), 2) as stddev_tmb, ROUND(sqrt(varSamp(msi_score)), 2) as stddev_msi_score FROM tmb_msi_data WHERE tmb IS NOT NULL AND msi_score IS NOT NULL ", "max_rows": 50 } ◀ result {"rows":[{"n_pairs":5505,"mean_tmb":14.03,"mean_msi_score":3.81,"pearson_correlation":0.583,"stddev_tmb":30.11,"stddev_msi_score":9.5}]} ▶ clickhouse_run_select_query { "query": " -- Detailed statistics: MSI status distribution and TMB percentiles WITH crc_samples AS ( SELECT DISTINCT sample_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'CANCER_TYPE' AND attribute_value IN ('Colorectal Cancer', 'Colorectal Adenocarcinoma') ), tmb_msi_data AS ( SELECT cs.sample_unique_id, MAX(CASE WHEN attribute_name = 'TMB_NONSYNONYMOUS' THEN toFloat64OrNull(attribute_value) END) as tmb, MAX(CASE WHEN attribute_name = 'MSI_TYPE' THEN attribute_value END) as msi_type FROM crc_samples cs LEFT JOIN clinical_data_derived cdd ON cs.sample_unique_id = cdd.sample_unique_id AND cdd.cancer_study_identifier = 'msk_chord_2024' AND cdd.attribute_name IN ('TMB_NONSYNONYMOUS', 'MSI_TYPE') GROUP BY cs.sample_unique_id ) SELECT msi_type, COUNT(*) as n_samples, ROUND(quantile(0.25)(tmb), 2) as q1_tmb, ROUND(quantile(0.5)(tmb), 2) as median_tmb, ROUND(quantile(0.75)(tmb), 2) as q3_tmb, ROUND(quantile(0.9)(tmb), 2) as p90_tmb, ROUND(quantile(0.95)(tmb), 2) as p95_tmb FROM tmb_msi_data WHERE tmb IS NOT NULL AND msi_type IS NOT NULL GROUP BY msi_type ORDER BY median_tmb DESC ", "max_rows": 50 } ◀ result {"rows":[{"msi_type":"Instable","n_samples":586,"q1_tmb":44.1,"median_tmb":56.68,"q3_tmb":75.94,"p90_tmb":93.89,"p95_tmb":108.24},{"msi_type":"Indeterminate","n_samples":188,"q1_tmb":4.92,"median_tmb":6.56,"q3_tmb":10.15,"p90_tmb":43.72,"p95_tmb":73.26},{"msi_type":"Stable","n_samples":4682,"q1_tmb":4.32,"median_tmb":5.87,"q3_tmb":7.78,"p90_tmb":9.51,"p95_tmb":11.09},{"n_samples":38,"q1_tmb":1.64,"median_tmb":3.69,"q3_tmb":5.74,"p90_tmb":13.86,"p95_tmb":28.55},{"msi_type":"Do not report","n_samples":49,"q1_tmb":0.86,"median_tmb":3.33,"q3_tmb":6.92,"p90_tmb":13.84,"p95_tmb":22.48}]} ▶ navigate_to_study_view { "studyIds": [ "msk_chord_2024" ], "filterJson": { "clinicalDataFilters": [ { "attributeId": "CANCER_TYPE", "values": [ { "value": "Colorectal Cancer" }, { "value": "Colorectal Adenocarcinoma" } ] } ] }, "tab": "clinicalData" } ◀ result {"success":true,"message":"Navigating to https://www.cbioportal.org/study/clinicalData?id=msk_chord_2024#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22CANCER_TYPE%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Colorectal%20Cancer%22%7D%2C%7B%22value%22%3A%22Colorectal%20Adenocarcinoma%22%7D%5D%7D%5D%7D","url":"https://www.cbioportal.org/study/clinicalData?id=msk_chord_2024#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22CANCER_TYPE%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Colorectal%20Cancer%22%7D%2C%7B%22value%22%3A%22Colorectal%20Adenocarcinoma%22%7D%5D%7D%5D%7D","data":{"studyIds":["msk_chord_2024"],"studies":[{"studyId":"msk_chord_2024","name":"MSK-CHORD (MSK, Nature 2024)","sampleCount":25040}],"tab":"clinicalData","hasFilters":true,"hasPlotsConfig":false,"pageDescription":"Table of clinical attributes per sample."}} ═ answer (success) Perfect! Now let me create a summary with the navigation link: --- ## Tumor Mutational Burden vs Microsatellite Instability in MSK-CHORD Colorectal Cancer I've analyzed the correlation between TMB and MSI status in **5,543 colorectal cancer samples** from the MSK-CHORD study. Here are the key findings: ### Continuous Correlation (TMB vs MSI Score) - **Pearson correlation coefficient: r = 0.583** (moderate positive correlation; n = 5,505 samples with both measurements) - Mean TMB: 14.03 mutations/Mb (SD = 30.11) - Mean MSI Score: 3.81 (SD = 9.50) This moderate correlation indicates that MSI score and TMB are positively associated, but TMB varies substantially even within MSI categories. ### TMB Distribution by MSI Type (Categorical) | MSI Type | Samples | Median TMB | Mean TMB | Q1–Q3 | 90th %ile | |---|---|---|---|---|---| | **Instable (MSI-H)** | 586 | **56.68** | 64.63 | 44.1–75.94 | 93.89 | | Indeterminate | 188 | 6.56 | 18.57 | 4.92–10.15 | 43.72 | | **Stable (MSI-L/MSS)** | 4,682 | **5.87** | 7.58 | 4.32–7.78 | 9.51 | | Unknown/Missing | 87 | 3.69–3.33 | 7.06–8.06 | — | — | **Key observations:** - **MSI-Instable (MSI-H)** samples have ~10-fold higher median TMB than stable samples (56.68 vs 5.87 mutations/Mb) - Clear separation between MSI-H and MSS/MSI-L groups with minimal overlap - Indeterminate MSI samples show intermediate TMB values - Within each group, substantial TMB heterogeneity exists (e.g., some MSS tumors reach 570 mutations/Mb) ### Interactive Exploration [View colorectal cancer cohort in MSK-CHORD](https://www.cbioportal.org/study/clinicalData?id=msk_chord_2024#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22CANCER_TYPE%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Colorectal%20Cancer%22%7D%2C%7B%22value%22%3A%22Colorectal%20Adenocarcinoma%22%7D%5D%7D%5D%7D) — Opens the clinical data table for 5,543 samples with sortable TMB and MSI columns. --- ### Statistical Context The **0.583 Pearson correlation** reflects a well-established biological relationship: MSI is a major driver of elevated mutation burden in colorectal cancer. However, the moderate strength (not near 1.0) reflects: 1. **MSI-independent hypermutation:** Some MSS samples harbor exceptionally high TMB (e.g., via POLE mutations, ultra-hypermutation) 2. **MSI-independent factors:** TMB is influenced by DNA repair pathway disruption beyond mismatch repair 3. **Measurement heterogeneity:** MSI score and TMB are calculated via different methodologies (MSI = microsatellite repeat analysis; TMB = nonsynonymous SNV/indel count per Mb)