Q (sonnet-5.5): can you show me a study with longitudinal data and a patient that has multiple samples over time?

▶ read_guide
{
 "uri": "cbioportal://sample-filtering-guide"
}

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

◀ result
# Sample and Study Filtering Guide

## Overview
Proper filtering is essential for meaningful cBioPortal analysis. This guide covers filtering by studies, sample types, and other criteria.

## Study-Level Filtering

### 1. Always Filter by Study
Every query should specify a study to ensure consistent results:

```sql
-- Always include study filtering
SELECT *
FROM your_table
WHERE
    cancer_study_identifier = 'your_study_id'
    -- Additional filters...
```

### 2. Find Available Studies

```sql
-- Discover available studies
SELECT
    cancer_study_identifier,
    name,
    description,
    type_of_cancer_id
FROM cancer_study
ORDER BY cancer_study_identifier;
```

### 3. Study Information

```sql
-- Get detailed study information
SELECT
    cs.cancer_study_identifier,
    cs.name as study_name,
    cs.description,
    cs.sample_count,
    COUNT(DISTINCT p.internal_id) as patient_count
FROM cancer_study cs
LEFT JOIN patient p ON cs.cancer_study_id = p.cancer_study_id
WHERE
    cs.cancer_study_identifier = 'your_study_id'
GROUP BY cs.cancer_study_identifier, cs.name, cs.description, cs.sample_count;
```

### 4. Find Studies by Available Data Types

Use this when the user asks *"which studies have mutation and copy-number data for X"*, *"studies with expression for Y"*, *"is there any imaging data"*, or any *"studies with Z data"* question. `cancer_study` has one precomputed sample count per data type — filter on them. No join and no schema exploration needed. These are the same counts as the portal's study list and its "Data type" filter.

**Canonical query — studies with ALL requested data types for a cancer type:**

```sql
SELECT
    cancer_study_identifier,
    name,
    sample_count,
    mutation_sample_count,
    cna_sample_count
FROM cancer_study
WHERE type_of_cancer_id = 'luad'          -- ← OncoTree code from search_oncotree
  AND mutation_sample_count > 0           -- ← one condition per required data type
  AND cna_sample_count > 0
ORDER BY sample_count DESC;
```

**Data type → column** (a count of 0 means the study doesn't have that data):

| Data type in the user's question | Column (portal "Data type" filter label) |
|---|---|
| samples in the study | `sample_count` |
| mutation / mutations | `mutation_sample_count` ("Mutations") |
| copy-number / CNA / amplification / deletion | `cna_sample_count` ("CNA") |
| structural variant / fusion | `structural_variant_sample_count` |
| RNA / mRNA / gene expression (any platform) | `mrna_expression_sample_count` — use this for "which studies have expression data" |
| specifically the portal's "RNA-Seq" filter | `rna_seq_sample_count` ("RNA-Seq") — counts only the `<study>_rna_seq_v2_mrna` list; 0 for many GDC and iAtlas studies that do have RNA-Seq |
| specifically the portal's "RNA (microarray)" filter | `mrna_microarray_sample_count` ("RNA (microarray)") — counts only the `<study>_mrna` list |
| microRNA | `mirna_sample_count` ("miRNA") |
| protein / RPPA | `rppa_sample_count` ("RPPA") |
| mas … (7866 more chars)

◀ 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_list_table_columns
{
 "table": "clinical_event_data_derived"
}

◀ result
{"columns":[{"name":"patient_unique_id","type":"String"},{"name":"key","type":"String"},{"name":"value","type":"String"},{"name":"start_date","type":"Int32"},{"name":"stop_date","type":"Int32"},{"name":"event_type","type":"LowCardinality(String)"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, uniqExact(patient_unique_id) AS pts, count() AS events, uniqExact(event_type) AS types FROM clinical_event_data_derived GROUP BY cancer_study_identifier ORDER BY pts DESC LIMIT 15"
}

◀ result
{"rows":[{"cancer_study_identifier":"msk_met_2021","pts":25674,"events":327885,"types":4},{"cancer_study_identifier":"msk_chord_2024","pts":24950,"events":13498721,"types":7},{"cancer_study_identifier":"mycf_msk_2026","pts":3417,"events":2338073,"types":11},{"cancer_study_identifier":"pancan_pcawg_2020","pts":2237,"events":2237,"types":1},{"cancer_study_identifier":"aml_target_gdc","pts":2189,"events":6536,"types":1},{"cancer_study_identifier":"bll_target_gdc","pts":1587,"events":4753,"types":1},{"cancer_study_identifier":"nsclc_ctdx_msk_2022","pts":1127,"events":2163,"types":2},{"cancer_study_identifier":"nbl_target_gdc","pts":1120,"events":3358,"types":1},{"cancer_study_identifier":"brca_tcga_gdc","pts":1097,"events":13448,"types":2},{"cancer_study_identifier":"brca_tcga_pan_can_atlas_2018","pts":1083,"events":40820,"types":3},{"cancer_study_identifier":"cll_broad_2022","pts":1009,"events":8655,"types":5},{"cancer_study_identifier":"glioma_mskcc_2019","pts":796,"events":6079,"types":1},{"cancer_study_identifier":"wt_target_gdc","pts":652,"events":1955,"types":1},{"cancer_study_identifier":"pancan_hcmi_2025","pts":637,"events":14917,"types":2},{"cancer_study_identifier":"gbm_tcga_gdc","pts":600,"events":8237,"types":2}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT patient_unique_id, uniqExact(sample_unique_id) AS n_samples, groupUniqArray(sample_unique_id) AS samples FROM clinical_data_derived WHERE cancer_study_identifier='glioma_mskcc_2019' GROUP BY patient_unique_id HAVING n_samples>=3 ORDER BY n_samples DESC LIMIT 5"
}

◀ result
{"rows":[{"patient_unique_id":"glioma_mskcc_2019_P-0007415","n_samples":5,"samples":["","glioma_mskcc_2019_P-0007415-T01-IM5","glioma_mskcc_2019_P-0007415-T02-IM5","glioma_mskcc_2019_P-0007415-T04-IM6","glioma_mskcc_2019_P-0007415-T03-IM6"]},{"patient_unique_id":"glioma_mskcc_2019_glioma_mskcc_2019_130","n_samples":4,"samples":["","glioma_mskcc_2019_TRF050483","glioma_mskcc_2019_TRF030123","glioma_mskcc_2019_TRF064189"]},{"patient_unique_id":"glioma_mskcc_2019_P-0002386","n_samples":4,"samples":["","glioma_mskcc_2019_P-0002386-T01-IM3","glioma_mskcc_2019_P-0002386-T03-IM5","glioma_mskcc_2019_P-0002386-T02-IM5"]},{"patient_unique_id":"glioma_mskcc_2019_P-0006410","n_samples":4,"samples":["","glioma_mskcc_2019_P-0006410-T03-IM5","glioma_mskcc_2019_P-0006410-T02-IM5","glioma_mskcc_2019_P-0006410-T01-IM5"]},{"patient_unique_id":"glioma_mskcc_2019_P-0002265","n_samples":4,"samples":["","glioma_mskcc_2019_P-0002265-T04-IM5","glioma_mskcc_2019_P-0002265-T01-IM3","glioma_mskcc_2019_P-0002265-T02-IM5"]}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT event_type, count() c, uniqExact(patient_unique_id) p FROM clinical_event_data_derived WHERE cancer_study_identifier='glioma_mskcc_2019' GROUP BY event_type"
}

◀ result
{"rows":[{"event_type":"Treatment","c":6079,"p":796}]}

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

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"glioma_mskcc_2019","name":"Glioma (MSK, Clin Cancer Res 2019)","sampleCount":1004,"studyViewUrl":"https://www.cbioportal.org/study?id=glioma_mskcc_2019","metadata":{"clinicalAttributeIds":["ACQUIRED_AFTER_ALKALATOR_TREATMENT","ACQUIRED_AFTER_ANY_SYSTEMIC_TREATMENT","ACQUIRED_AFTER_RADIOTHERAPY","ACTIONABLE_LESION1","AGE","CANCER_TYPE","CANCER_TYPE_DETAILED","ENHANCING","FRACTION_GENOME_ALTERED","GENE_PANEL","HIGHEST_ONCOKB_LEVEL","HISTOLOGY","MGMT_STATUS","MUTATION_COUNT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","PATIENT_DISPLAY_NAME","PFS_MONTHS","PFS_STATUS","PRIOR_LINES_OF_THERAPY","RECEIVED_TARGETED_TX","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SOMATIC_STATUS","SURGERY_DATE_RELATIVE_TO_DX_MONTHS","TMB_NONSYNONYMOUS","TMZ_HYPERMUTATED","TUMOR_SURGERY_NUMBER","WHO_CLASSIFICATION_OF_DX_TUMOR","WHO_GRADE"],"molecularProfileIds":["glioma_mskcc_2019_gistic","glioma_mskcc_2019_mutations","glioma_mskcc_2019_structural_variants"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations"]}}]}}

▶ navigate_to_patient_view
{
 "studyIds": [
  "glioma_mskcc_2019"
 ],
 "patientId": "P-0007415"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/patient/summary?studyId=glioma_mskcc_2019&caseId=P-0007415","url":"https://www.cbioportal.org/patient/summary?studyId=glioma_mskcc_2019&caseId=P-0007415","data":{"patientUrls":[{"studyId":"glioma_mskcc_2019","studyName":"Glioma (MSK, Clin Cancer Res 2019)","url":"https://www.cbioportal.org/patient/summary?studyId=glioma_mskcc_2019&caseId=P-0007415"}],"patientId":"P-0007415","tab":"summary","hasMultipleUrls":false,"pageDescription":"Patient overview including patient history timeline (if available) and tables of mutations, structural variants, and copy number alterations."}}

═ answer (success)
**Study:** [Glioma (MSK, Clin Cancer Res 2019)](https://www.cbioportal.org/study?id=glioma_mskcc_2019) (`glioma_mskcc_2019`). It has 1,004 samples. The study includes treatment timeline data for 796 patients (6,079 treatment events). It also has repeat-surgery attributes such as `TUMOR_SURGERY_NUMBER` and `SURGERY_DATE_RELATIVE_TO_DX_MONTHS`.

**Example patient:** [P-0007415](https://www.cbioportal.org/patient/summary?studyId=glioma_mskcc_2019&caseId=P-0007415) has 4 tumor samples:
- P-0007415-T01-IM5
- P-0007415-T02-IM5
- P-0007415-T03-IM6
- P-0007415-T04-IM6

The patient page shows the treatment timeline and the mutations, structural variants and copy-number alterations for each sample. The samples appear to come from sequential surgeries, but I only checked their IDs and did not check the surgery dates.

Other patients in this study with 4 samples include P-0002386, P-0006410 and P-0002265.

Other studies with timeline data include `msk_chord_2024` (about 25,000 patients, with treatment and other events) and `msk_met_2021`. I didn't check which of their patients have several samples. Tell me if you'd like an example from one of them.