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

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

▶ list_studies
{
 "search": "MSK-CHORD"
}

◀ 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 … (7083 more chars)

◀ result
{"result":[{"cancer_study_identifier":"msk_chord_2024","name":"MSK-CHORD (MSK, Nature 2024)","type_of_cancer_id":"mixed","sample_count":25040,"has_guide":true,"url":"https://www.cbioportal.org/study/summary?id=msk_chord_2024"}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH primary_samples AS (
    SELECT DISTINCT patient_unique_id
    FROM clinical_data_derived
    WHERE
        cancer_study_identifier = 'msk_chord_2024'
        AND attribute_name = 'SAMPLE_TYPE'
        AND attribute_value = 'Primary'
),
metastatic_samples AS (
    SELECT DISTINCT patient_unique_id
    FROM clinical_data_derived
    WHERE
        cancer_study_identifier = 'msk_chord_2024'
        AND attribute_name = 'SAMPLE_TYPE'
        AND attribute_value = 'Metastasis'
)
SELECT
    COUNT(*) as patients_with_both_primary_and_metastatic
FROM primary_samples p
INNER JOIN metastatic_samples m
    ON p.patient_unique_id = m.patient_unique_id;
",
 "max_rows": 10
}

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

═ answer (success)
**Result:**

In the **MSK-CHORD (MSK, Nature 2024)** study, **26 unique patients** have both primary and metastatic tumor samples.

This represents patients who have paired genomic data from their primary tumor and at least one metastatic site, which is valuable for studying tumor evolution and metastatic progression.

Would you like to explore these patients further, or see additional breakdowns (e.g., sample counts, cancer types represented)?