Q (haiku): How many unique patients have both primary and metastatic samples in the MSK-CHORD Study?

▶ 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_run_select_query
{
 "query": "
WITH sample_types AS (
  SELECT DISTINCT
    patient_unique_id,
    attribute_value AS sample_type
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'msk_chord_2024'
    AND attribute_name = 'SAMPLE_TYPE'
)
SELECT COUNT(DISTINCT patient_unique_id) AS patients_with_both
FROM sample_types
GROUP BY patient_unique_id
HAVING COUNT(DISTINCT sample_type) >= 2
  AND countIf(sample_type ILIKE 'Primary') > 0
  AND countIf(sample_type ILIKE 'Metastasis') > 0
",
 "max_rows": 100
}

◀ result
{"rows":[{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1},{"patients_with_both":1}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH sample_types AS (
  SELECT DISTINCT
    patient_unique_id,
    attribute_value AS sample_type
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'msk_chord_2024'
    AND attribute_name = 'SAMPLE_TYPE'
),
patients_with_both AS (
  SELECT patient_unique_id
  FROM sample_types
  GROUP BY patient_unique_id
  HAVING countIf(sample_type ILIKE 'Primary') > 0
    AND countIf(sample_type ILIKE 'Metastasis') > 0
)
SELECT COUNT(DISTINCT patient_unique_id) AS unique_patients_with_both_types
FROM patients_with_both
",
 "max_rows": 100
}

◀ result
{"rows":[{"unique_patients_with_both_types":26}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Verify by showing the breakdown
WITH sample_types AS (
  SELECT DISTINCT
    patient_unique_id,
    attribute_value AS sample_type
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'msk_chord_2024'
    AND attribute_name = 'SAMPLE_TYPE'
)
SELECT
  sample_type,
  COUNT(DISTINCT patient_unique_id) AS patients_with_this_type
FROM sample_types
GROUP BY sample_type
ORDER BY patients_with_this_type DESC
",
 "max_rows": 100
}

◀ result
{"rows":[{"sample_type":"Primary","patients_with_this_type":15872},{"sample_type":"Metastasis","patients_with_this_type":8872},{"sample_type":"Unknown","patients_with_this_type":136},{"sample_type":"Local Recurrence","patients_with_this_type":98}]}

═ answer (success)
## Answer

**26 unique patients** have both primary and metastatic samples in the MSK-CHORD Study.

### Context
- **Primary samples:** 15,872 patients
- **Metastatic samples:** 8,872 patients  
- **Patients with both sample types:** 26

This means that while the study contains many patients with either primary or metastatic samples (or both), only a small subset (26 patients) have multiple samples representing both disease states, enabling longitudinal or comparative genomic analysis between primary tumors and metastatic lesions.