Q (haiku): What are the top 5 most common primary diagnosis sites in the MSK-CHORD Study?

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

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

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

◀ result
{"columns":[{"name":"attr_id","type":"String"},{"name":"display_name","type":"String"},{"name":"description","type":"String"},{"name":"datatype","type":"String"},{"name":"patient_attribute","type":"Int32"},{"name":"priority","type":"String"},{"name":"cancer_study_id","type":"Int64"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attr_id, display_name, description
FROM clinical_attribute_meta
WHERE cancer_study_id = (
    SELECT cancer_study_id
    FROM cancer_study
    WHERE cancer_study_identifier = 'msk_chord_2024'
)
AND (
    upper(attr_id) LIKE '%PRIMARY%'
    OR upper(attr_id) LIKE '%SITE%'
    OR upper(attr_id) LIKE '%LOCATION%'
    OR upper(attr_id) LIKE '%DIAGNOSIS%'
)
ORDER BY attr_id
",
 "max_rows": 100
}

◀ result
{"rows":[{"attr_id":"DIAGNOSIS_DESCRIPTION","display_name":"Diagnosis Description","description":"Diagnosis Description"},{"attr_id":"METASTATIC_SITE","display_name":"Metastatic Site","description":"Location of profiled metastatic site"},{"attr_id":"PRIMARY_SITE","display_name":"Primary Tumor Site","description":"The organ sub-division term assigned to the primary tumor location of the sample. Source: MPath"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT 
    attribute_value AS primary_site,
    COUNT(DISTINCT sample_unique_id) AS sample_count,
    COUNT(DISTINCT patient_unique_id) AS patient_count,
    ROUND((COUNT(DISTINCT sample_unique_id) / (SELECT COUNT(DISTINCT sample_unique_id) FROM clinical_data_derived WHERE cancer_study_identifier = 'msk_chord_2024' AND attribute_name = 'PRIMARY_SITE')) * 100, 1) AS pct_of_profiled
FROM clinical_data_derived
WHERE cancer_study_identifier = 'msk_chord_2024'
    AND attribute_name = 'PRIMARY_SITE'
    AND attribute_value != ''
GROUP BY attribute_value
ORDER BY sample_count DESC
LIMIT 5
",
 "max_rows": 100
}

◀ result
{"rows":[{"primary_site":"Lung","sample_count":7773,"patient_count":7773,"pct_of_profiled":31},{"primary_site":"Breast","sample_count":5358,"patient_count":5358,"pct_of_profiled":21.4},{"primary_site":"Prostate","sample_count":3208,"patient_count":3208,"pct_of_profiled":12.8},{"primary_site":"Pancreas","sample_count":3104,"patient_count":3104,"pct_of_profiled":12.4},{"primary_site":"Colon","sample_count":2516,"patient_count":2516,"pct_of_profiled":10}]}

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

◀ 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"]}}]}}

▶ navigate_to_study_view
{
 "studyIds": [
  "msk_chord_2024"
 ],
 "tab": "clinicalData"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/study/clinicalData?id=msk_chord_2024","url":"https://www.cbioportal.org/study/clinicalData?id=msk_chord_2024","data":{"studyIds":["msk_chord_2024"],"studies":[{"studyId":"msk_chord_2024","name":"MSK-CHORD (MSK, Nature 2024)","sampleCount":25040}],"tab":"clinicalData","hasFilters":false,"hasPlotsConfig":false,"pageDescription":"Table of clinical attributes per sample."}}

═ answer (success)
## Top 5 Most Common Primary Diagnosis Sites in MSK-CHORD Study

The **MSK-CHORD (MSK, Nature 2024)** study includes 25,040 sequenced samples with PRIMARY_SITE data from 7,773 patients. The top 5 most common primary tumor sites are:

| Rank | Primary Site | Samples | % of Profiled |
|------|--------------|---------|---------------|
| 1 | **Lung** | 7,773 | 31.0% |
| 2 | **Breast** | 5,358 | 21.4% |
| 3 | **Prostate** | 3,208 | 12.8% |
| 4 | **Pancreas** | 3,104 | 12.4% |
| 5 | **Colon** | 2,516 | 10.0% |

These five sites account for **87.6%** of all samples with primary site information.

[View the MSK-CHORD clinical data](https://www.cbioportal.org/study/clinicalData?id=msk_chord_2024) — click on "PRIMARY_SITE" in the data table to see the full distribution and filter by site.