Q (haiku): How many GLASS patients developed hypermutation after TMZ treatment?

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

▶ list_studies
{
 "search": "GLASS",
 "limit": 10,
 "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":[{"cancer_study_identifier":"difg_glass","name":"Diffuse Glioma (GLASS Consortium)","description":"Whole genome or whole exome sequencing analysis of temporally separated diffuse glioma tumor/normal pairs from 329 adult patients. The data provided is based on the May 31, 2022 version and can be accessed at <a href=\"https://www.synapse.org/#!Synapse:syn17038081/wiki/585622\">Synapse</a>.","type_of_cancer_id":"difg","sample_count":693,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=difg_glass"},{"cancer_study_identifier":"difg_glass_2019","name":"Diffuse Glioma (GLASS Consortium, Nature 2019)","description":"Whole genome or whole exome sequencing analysis of temporally separated diffuse glioma tumor/normal pairs from 222 adult patients.","type_of_cancer_id":"difg","sample_count":444,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=difg_glass_2019"}]}

▶ clickhouse_list_tables
{}

◀ 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_run_select_query
{
 "query": "
SELECT DISTINCT attribute_name
FROM clinical_data_derived
WHERE cancer_study_identifier = 'difg_glass'
ORDER BY attribute_name
LIMIT 50
"
}

◀ result
{"rows":[{"attribute_name":"AGE"},{"attribute_name":"ALIQUOT_ANALYSIS_TYPE"},{"attribute_name":"ALKYLATING_AGENT_TX"},{"attribute_name":"CANCER_TYPE"},{"attribute_name":"CANCER_TYPE_DETAILED"},{"attribute_name":"CASE_PROJECT"},{"attribute_name":"CODEL_STATUS"},{"attribute_name":"DNA_ALIQUOT_BARCODE"},{"attribute_name":"ESTIMATE_SCORE"},{"attribute_name":"FRACTION_GENOME_ALTERED"},{"attribute_name":"HISTOLOGY"},{"attribute_name":"IDH_CODEL_STATUS"},{"attribute_name":"IDH_STATUS"},{"attribute_name":"IMMUNE_SCORE"},{"attribute_name":"MGMT_METHYLATION"},{"attribute_name":"MGMT_METHYLATION_METHOD"},{"attribute_name":"MUTATION_COUNT"},{"attribute_name":"ONCOTREE_CODE"},{"attribute_name":"OS_MONTHS"},{"attribute_name":"OS_STATUS"},{"attribute_name":"PURITY"},{"attribute_name":"RNA_ALIQUOT_BARCODE"},{"attribute_name":"SAMPLE_COUNT"},{"attribute_name":"SAMPLE_TYPE"},{"attribute_name":"SEX"},{"attribute_name":"STROMAL_SCORE"},{"attribute_name":"SURGERY_EXTENT_OF_RESECTION"},{"attribute_name":"SURGERY_INDICATION"},{"attribute_name":"SURGERY_LATERALITY"},{"attribute_name":"SURGERY_LOCATION"},{"attribute_name":"SURGERY_TYPE"},{"attribute_name":"TISSUE_SOURCE"},{"attribute_name":"TREATMENT_CONCURRENT_TMZ"},{"attribute_name":"TREATMENT_RADIATION_DOSE_GY"},{"attribute_name":"TREATMENT_RADIOTHERAPY"},{"attribute_name":"TREATMENT_TMZ"},{"attribute_name":"TREATMENT_TMZ_CYCLES"},{"attribute_name":"TREATMENT_TMZ_CYCLES_6"},{"attribute_name":"TUMOR_CLASSIFICATION"},{"attribute_name":"TUMOR_GRADE"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'difg_glass'
  AND attribute_name = 'TREATMENT_TMZ'
ORDER BY attribute_value
"
}

◀ result
{"rows":[{},{"attribute_value":"No"},{"attribute_value":"Yes"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT attribute_name, COUNT(DISTINCT patient_unique_id) as n_patients
FROM clinical_data_derived
WHERE cancer_study_identifier = 'difg_glass'
  AND attribute_name IN ('MUTATION_COUNT', 'FRACTION_GENOME_ALTERED')
GROUP BY attribute_name
"
}

◀ result
{"rows":[{"attribute_name":"FRACTION_GENOME_ALTERED","n_patients":329},{"attribute_name":"MUTATION_COUNT","n_patients":329}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_name
FROM clinical_data_derived
WHERE cancer_study_identifier = 'difg_glass'
  AND upper(attribute_name) LIKE '%HYPER%'
"
}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT sample_type
FROM sample_derived
WHERE cancer_study_identifier = 'difg_glass'
ORDER BY sample_type
"
}

◀ result
{"rows":[]}

◀ result
{"rows":[{"sample_type":"Primary Solid Tumor"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_name
FROM clinical_data_derived
WHERE cancer_study_identifier = 'difg_glass_2019'
ORDER BY attribute_name
"
}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT sample_type
FROM sample_derived
WHERE cancer_study_identifier = 'difg_glass_2019'
ORDER BY sample_type
"
}

◀ result
{"rows":[{"attribute_name":"1P19Q_STATUS"},{"attribute_name":"AGE_AT_DX"},{"attribute_name":"ALIQUOT_BARCODE"},{"attribute_name":"ALKYLATING_AGENT"},{"attribute_name":"ANEUPLOIDY_AMP_SCORE"},{"attribute_name":"ANEUPLOIDY_DEL_SCORE"},{"attribute_name":"ANEUPLOIDY_SCORE"},{"attribute_name":"CANCER_TYPE"},{"attribute_name":"CANCER_TYPE_DETAILED"},{"attribute_name":"CASE_PROJECT"},{"attribute_name":"CHEMO_OTHER"},{"attribute_name":"CHEMO_OTHER_CYCLES"},{"attribute_name":"COMMENTS"},{"attribute_name":"CONCURRENT_TMZ"},{"attribute_name":"EXTENT_OF_RESECTION"},{"attribute_name":"FRACTION_GENOME_ALTERED"},{"attribute_name":"GENE_PANEL"},{"attribute_name":"GRADE"},{"attribute_name":"HISTOLOGY"},{"attribute_name":"HYPERMUTATION_STATUS"},{"attribute_name":"IDH_CODEL_SUBTYPE"},{"attribute_name":"IDH_STATUS"},{"attribute_name":"INITIAL_GRADE"},{"attribute_name":"INITIAL_HISTOLOGY"},{"attribute_name":"MGMT_METHYLATION"},{"attribute_name":"MGMT_METHYLATION_METHOD"},{"attribute_name":"MNP_CLASSIFICATION"},{"attribute_name":"MUTATION_COUNT"},{"attribute_name":"ONCOTREE_CODE"},{"attribute_name":"OS_MONTHS"},{"attribute_name":"OS_STATUS"},{"attribute_name":"POST_RECURRENCE_SURVIVAL_MONTHS"},{"attribute_name":"RADIATION_DOSE"},{"attribute_name":"RADIATION_OTHER"},{"attribute_name":"RADIOTHERAPY"},{"attribute_name":"RADIOTHERAPY_FRACTIONS"},{"attribute_name":"SAMPLE_CLASS"},{"attribute_name":"SAMPLE_COUNT"},{"attribute_name":"SAMPLE_TYPE"},{"attribute_name":"SEX"},{"attribute_name":"SOMATIC_STATUS"},{"attribute_name":"SURGERY NUMBER"},{"attribute_name":"SURGERY_INDICATION"},{"attribute_name":"TIME_TO_FIRST_RECURRENCE"},{"attribute_name":"TISSUE_SOURCE"},{"attribute_name":"TMB_NONSYNONYMOUS"},{"attribute_name":"TMZ_CYCLES"},{"attribute_name":"TMZ_TREATMENT"},{"attribute_name":"TRANSCRIPTIONAL_SUBTYPES"},{"attribute_name":"TUMOR_LATERALITY"},{"attribute_name":"TUMOR_LOCATION"},{"attribute_name":"TYPE_OF_SURGERY"},{"attribute_name":"WHOLE_EXOME_SEQUENCED"},{"attribute_name":"WHOLE_GENOME_SEQUENCED"},{"attribute_name":"WHO_CLASSIFICATION"}]}

◀ result
{"rows":[{"sample_type":"Primary Solid Tumor"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'difg_glass_2019'
  AND attribute_name = 'HYPERMUTATION_STATUS'
ORDER BY attribute_value
"
}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'difg_glass_2019'
  AND attribute_name = 'TMZ_TREATMENT'
ORDER BY attribute_value
"
}

◀ result
{"rows":[{"attribute_value":"No"},{"attribute_value":"Yes"}]}

◀ result
{"rows":[{},{"attribute_value":"No"},{"attribute_value":"Yes"}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH patient_tmz AS (
    SELECT DISTINCT patient_unique_id
    FROM clinical_data_derived
    WHERE cancer_study_identifier = 'difg_glass_2019'
      AND attribute_name = 'TMZ_TREATMENT'
      AND upper(attribute_value) = 'YES'
),
patient_hypermutation AS (
    SELECT DISTINCT patient_unique_id
    FROM clinical_data_derived
    WHERE cancer_study_identifier = 'difg_glass_2019'
      AND attribute_name = 'HYPERMUTATION_STATUS'
      AND upper(attribute_value) = 'YES'
)
SELECT 
    COUNT(DISTINCT p.patient_unique_id) as patients_with_tmz,
    COUNT(DISTINCT h.patient_unique_id) as patients_with_hypermutation,
    COUNT(DISTINCT CASE WHEN p.patient_unique_id IN (SELECT patient_unique_id FROM patient_hypermutation) THEN p.patient_unique_id END) as patients_tmz_and_hypermutation
FROM patient_tmz p
LEFT JOIN patient_hypermutation h ON 1=1
",
 "max_rows": 100
}

◀ result
{"rows":[{"patients_with_tmz":151,"patients_with_hypermutation":35,"patients_tmz_and_hypermutation":30}]}

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

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"difg_glass_2019","name":"Diffuse Glioma (GLASS Consortium, Nature 2019)","sampleCount":444,"studyViewUrl":"https://www.cbioportal.org/study?id=difg_glass_2019","metadata":{"clinicalAttributeIds":["1P19Q_STATUS","AGE_AT_DX","ALIQUOT_BARCODE","ALKYLATING_AGENT","ANEUPLOIDY_AMP_SCORE","ANEUPLOIDY_DEL_SCORE","ANEUPLOIDY_SCORE","CANCER_TYPE","CANCER_TYPE_DETAILED","CASE_PROJECT","CHEMO_OTHER","CHEMO_OTHER_CYCLES","COMMENTS","CONCURRENT_TMZ","EXTENT_OF_RESECTION","FRACTION_GENOME_ALTERED","GENE_PANEL","GRADE","HISTOLOGY","HYPERMUTATION_STATUS","IDH_CODEL_SUBTYPE","IDH_STATUS","INITIAL_GRADE","INITIAL_HISTOLOGY","MGMT_METHYLATION","MGMT_METHYLATION_METHOD","MNP_CLASSIFICATION","MUTATION_COUNT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","POST_RECURRENCE_SURVIVAL_MONTHS","RADIATION_DOSE","RADIATION_OTHER","RADIOTHERAPY","RADIOTHERAPY_FRACTIONS","SAMPLE_CLASS","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SOMATIC_STATUS","SURGERY NUMBER","SURGERY_INDICATION","TIME_TO_FIRST_RECURRENCE","TISSUE_SOURCE","TMB_NONSYNONYMOUS","TMZ_CYCLES","TMZ_TREATMENT","TRANSCRIPTIONAL_SUBTYPES","TUMOR_LATERALITY","TUMOR_LOCATION","TYPE_OF_SURGERY","WHOLE_EXOME_SEQUENCED","WHOLE_GENOME_SEQUENCED","WHO_CLASSIFICATION"],"molecularProfileIds":["difg_glass_2019_mutations","difg_glass_2019_rna_seq_mrna","difg_glass_2019_rna_seq_mrna_median_all_sample_Zscores"],"genericAssayProfiles":["difg_glass_2019_armlevel_cna"],"heatmapProfileIds":["difg_glass_2019_rna_seq_mrna_median_all_sample_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","generic_assay_armlevel_cna"]}}]}}

▶ navigate_to_study_view
{
 "studyIds": [
  "difg_glass_2019"
 ],
 "filterJson": {
  "clinicalDataFilters": [
   {
    "attributeId": "TMZ_TREATMENT",
    "values": [
     {
      "value": "Yes"
     }
    ]
   },
   {
    "attributeId": "HYPERMUTATION_STATUS",
    "values": [
     {
      "value": "Yes"
     }
    ]
   }
  ]
 }
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/study/summary?id=difg_glass_2019#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22TMZ_TREATMENT%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Yes%22%7D%5D%7D%2C%7B%22attributeId%22%3A%22HYPERMUTATION_STATUS%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Yes%22%7D%5D%7D%5D%7D","url":"https://www.cbioportal.org/study/summary?id=difg_glass_2019#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22TMZ_TREATMENT%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Yes%22%7D%5D%7D%2C%7B%22attributeId%22%3A%22HYPERMUTATION_STATUS%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Yes%22%7D%5D%7D%5D%7D","data":{"studyIds":["difg_glass_2019"],"studies":[{"studyId":"difg_glass_2019","name":"Diffuse Glioma (GLASS Consortium, Nature 2019)","sampleCount":444}],"tab":"summary","hasFilters":true,"hasPlotsConfig":false,"pageDescription":"Overview of all study data displayed as interactive charts summarizing available clinical and genomic data. Click within a chart to filter. Additional charts may be available in the Charts menu."}}

═ answer (success)
## Results

In the **GLASS Consortium (Nature 2019)** study, **30 patients developed hypermutation after TMZ treatment**.

### Summary
- **Total patients who received TMZ:** 151
- **Total patients with hypermutation status recorded:** 35  
- **Patients with both TMZ treatment AND hypermutation:** **30**

### Study Details
- **Study ID:** difg_glass_2019
- **Full Name:** Diffuse Glioma (GLASS Consortium, Nature 2019)
- **Cancer Type:** Diffuse Glioma (DIFG)
- **Total Samples:** 444
- **Reference:** Whole genome/exome sequencing of temporally separated diffuse glioma tumor/normal pairs from 222 adult patients

### Explore Further
[View the filtered cohort](https://www.cbioportal.org/study/summary?id=difg_glass_2019#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22TMZ_TREATMENT%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Yes%22%7D%5D%7D%2C%7B%22attributeId%22%3A%22HYPERMUTATION_STATUS%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Yes%22%7D%5D%7D%5D%7D) — This link opens cBioPortal showing all TMZ-treated patients in the GLASS study with hypermutation status, where you can explore additional clinical attributes, genetic alterations, and survival outcomes.