Q (sonnet-5.5): Is there a lower grade glioma study with race data?

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

▶ list_studies
{
 "search": "lower grade glioma"
}

◀ 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":"lgg_tcga","name":"Brain Lower Grade Glioma (TCGA, Firehose Legacy)","type_of_cancer_id":"difg","sample_count":530,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=lgg_tcga"},{"cancer_study_identifier":"lgg_tcga_pan_can_atlas_2018","name":"Brain Lower Grade Glioma (TCGA, PanCancer Atlas)","type_of_cancer_id":"difg","sample_count":514,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=lgg_tcga_pan_can_atlas_2018"}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, attribute_name, count(DISTINCT patient_unique_id) n FROM clinical_data_derived WHERE cancer_study_identifier IN ('lgg_tcga','lgg_tcga_pan_can_atlas_2018') AND upper(attribute_name) LIKE '%RACE%' GROUP BY 1,2"
}

▶ clickhouse_run_select_query
{
 "query": "SELECT * FROM clinical_attribute_counts(study='lgg_tcga_pan_can_atlas_2018', attribute='RACE') ORDER BY count DESC"
}

▶ clickhouse_run_select_query
{
 "query": "SELECT * FROM clinical_attribute_counts(study='lgg_tcga', attribute='RACE') ORDER BY count DESC"
}

◀ result
{"rows":[{"cancer_study_identifier":"lgg_tcga_pan_can_atlas_2018","attribute_name":"RACE","n":514},{"cancer_study_identifier":"lgg_tcga","attribute_name":"RACE","n":516}]}

◀ result
{"rows":[{"value":"White","count":474,"pct_of_study":92.2,"level":"patient"},{"value":"Black or African American","count":21,"pct_of_study":4.1,"level":"patient"},{"value":"NA","count":11,"pct_of_study":2.1,"level":"patient"},{"value":"Asian","count":7,"pct_of_study":1.4,"level":"patient"},{"value":"American Indian or Alaska Native","count":1,"pct_of_study":0.2,"level":"patient"}]}

◀ result
{"rows":[{"value":"WHITE","count":475,"pct_of_study":92.1,"level":"patient"},{"value":"BLACK OR AFRICAN AMERICAN","count":21,"pct_of_study":4.1,"level":"patient"},{"value":"NA","count":11,"pct_of_study":2.1,"level":"patient"},{"value":"ASIAN","count":8,"pct_of_study":1.6,"level":"patient"},{"value":"AMERICAN INDIAN OR ALASKA NATIVE","count":1,"pct_of_study":0.2,"level":"patient"}]}

═ answer (success)
Yes. Two lower grade glioma studies have race data. Both are TCGA cohorts, and race is recorded for most patients.

**[Brain Lower Grade Glioma (TCGA, PanCancer Atlas)](https://www.cbioportal.org/study/summary?id=lgg_tcga_pan_can_atlas_2018)** (`lgg_tcga_pan_can_atlas_2018`, 514 patients)
| Race | Patients | % |
|---|---|---|
| White | 474 | 92.2% |
| Black or African American | 21 | 4.1% |
| Asian | 7 | 1.4% |
| American Indian or Alaska Native | 1 | 0.2% |
| Not available | 11 | 2.1% |

**[Brain Lower Grade Glioma (TCGA, Firehose Legacy)](https://www.cbioportal.org/study/summary?id=lgg_tcga)** (`lgg_tcga`, 530 samples, 516 patients with a race entry)
| Race | Patients | % |
|---|---|---|
| White | 475 | 92.1% |
| Black or African American | 21 | 4.1% |
| Asian | 8 | 1.6% |
| American Indian or Alaska Native | 1 | 0.2% |
| Not available | 11 | 2.1% |

The two studies largely overlap, so don't add their counts together. The PanCancer Atlas version is the more current one.