Q (haiku): Which studies have RNA expression for renal cancer?

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

▶ search_oncotree
{
 "search_term": "renal"
}

◀ 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
{"result":[{"code":"CCRCC","name":"Renal Clear Cell Carcinoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > CCRCC"},{"code":"MRC","name":"Renal Medullary Carcinoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > MRC"},{"code":"MTSCC","name":"Renal Mucinous Tubular Spindle Cell Carcinoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > MTSCC"},{"code":"NCCRCC","name":"Renal Non-Clear Cell Carcinoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC"},{"code":"RAML","name":"Renal Angiomyolipoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > RAML"},{"code":"RCC","name":"Renal Cell Carcinoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC"},{"code":"RNET","name":"Renal Neuroendocrine Tumor","score":60,"mainType":"Renal Neuroendocrine Tumor","tissue":"Kidney","hierarchy":"KIDNEY > RNET"},{"code":"ROCY","name":"Renal Oncocytoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > ROCY"},{"code":"RSCC","name":"Renal Small Cell Carcinoma","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > RSCC"},{"code":"SCCRCC","name":"Renal Clear Cell Carcinoma with Sarcomatoid Features","score":60,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > CCRCC > SCCRCC"},{"code":"ADRENAL_GLAND","name":"Adrenal Gland","score":50,"mainType":"Adrenal Gland Cancer","tissue":"Adrenal Gland","hierarchy":"ADRENAL_GLAND"},{"code":"CCPRC","name":"Clear Cell Papillary Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > CCPRC"},{"code":"CDRCC","name":"Collecting Duct Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > CDRCC"},{"code":"CHRCC","name":"Chromophobe Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > CHRCC"},{"code":"FHRCC","name":"FH-Deficient Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > FHRCC"},{"code":"NRCC","name":"NUTM1 Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NRCC"},{"code":"PRCC","name":"Papillary Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > PRCC"},{"code":"SRCC","name":"Sarcomatoid Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tissue":"Kidney","hierarchy":"KIDNEY > RCC > NCCRCC > SRCC"},{"code":"TRCC","name":"Translocation-Associated Renal Cell Carcinoma","score":50,"mainType":"Renal Cell Carcinoma","tis … (675 more chars)

▶ clickhouse_run_select_query
{
 "query": "
SELECT
    cancer_study_identifier,
    name,
    sample_count,
    mrna_expression_sample_count,
    rna_seq_sample_count,
    mrna_microarray_sample_count
FROM cancer_study
WHERE type_of_cancer_id IN ('RCC', 'CCRCC', 'NCCRCC', 'PRCC', 'CHRCC')
  AND mrna_expression_sample_count > 0
ORDER BY mrna_expression_sample_count DESC
"
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT
    cancer_study_identifier,
    name,
    type_of_cancer_id,
    sample_count,
    mrna_expression_sample_count,
    rna_seq_sample_count,
    mrna_microarray_sample_count
FROM cancer_study
WHERE type_of_cancer_id LIKE '%RCC%' OR type_of_cancer_id = 'KIDNEY'
ORDER BY mrna_expression_sample_count DESC, sample_count DESC
"
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT
    cancer_study_identifier,
    name,
    type_of_cancer_id,
    sample_count,
    mrna_expression_sample_count
FROM cancer_study
WHERE name ILIKE '%renal%' OR name ILIKE '%kidney%' OR type_of_cancer_id ILIKE '%rcc%'
ORDER BY sample_count DESC
"
}

◀ result
{"rows":[{"cancer_study_identifier":"ccrcc_sjuh_2023","name":"Renal Cell Carcinoma (St. James, Clin Cancer Res 2023)","type_of_cancer_id":"ccrcc","sample_count":943,"mrna_expression_sample_count":0},{"cancer_study_identifier":"kirc_tcga","name":"Kidney Renal Clear Cell Carcinoma (TCGA, Firehose Legacy)","type_of_cancer_id":"ccrcc","sample_count":538,"mrna_expression_sample_count":535},{"cancer_study_identifier":"ccrcc_tcga_gdc","name":"Renal Clear Cell Carcinoma (TCGA GDC, 2025)","type_of_cancer_id":"ccrcc","sample_count":537,"mrna_expression_sample_count":534},{"cancer_study_identifier":"kirc_tcga_pan_can_atlas_2018","name":"Kidney Renal Clear Cell Carcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"ccrcc","sample_count":512,"mrna_expression_sample_count":510},{"cancer_study_identifier":"kirc_tcga_pub","name":"Kidney Renal Clear Cell Carcinoma (TCGA, Nature 2013)","type_of_cancer_id":"ccrcc","sample_count":446,"mrna_expression_sample_count":426},{"cancer_study_identifier":"rcc_cptac_gdc","name":"Renal Cell Carcinoma (CPTAC GDC, 2025)","type_of_cancer_id":"rcc","sample_count":354,"mrna_expression_sample_count":353},{"cancer_study_identifier":"kirp_tcga","name":"Kidney Renal Papillary Cell Carcinoma (TCGA, Firehose Legacy)","type_of_cancer_id":"prcc","sample_count":293,"mrna_expression_sample_count":291},{"cancer_study_identifier":"prcc_tcga_gdc","name":"Papillary Renal Cell Carcinoma (TCGA GDC, 2025)","type_of_cancer_id":"prcc","sample_count":292,"mrna_expression_sample_count":291},{"cancer_study_identifier":"kirp_tcga_pan_can_atlas_2018","name":"Kidney Renal Papillary Cell Carcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"prcc","sample_count":283,"mrna_expression_sample_count":283},{"cancer_study_identifier":"rcc_iatlas_immotion150_2018","name":"Renal Cell Carcinoma (IMmotion150 Clinical Trial, Nat Med. 2018) - iAtlas Harmonized","type_of_cancer_id":"rcc","sample_count":263,"mrna_expression_sample_count":263},{"cancer_study_identifier":"nccrcc_genentech_2014","name":"Renal Non-Clear Cell Carcinoma (Genentech, Nat Genet 2014)","type_of_cancer_id":"nccrcc","sample_count":146,"mrna_expression_sample_count":0},{"cancer_study_identifier":"kich_tcga","name":"Kidney Chromophobe (TCGA, Firehose Legacy)","type_of_cancer_id":"chrcc","sample_count":113,"mrna_expression_sample_count":66},{"cancer_study_identifier":"ccrcc_utokyo_2013","name":"Renal Clear Cell Carcinoma (UTokyo, Nat Genet 2013)","type_of_cancer_id":"ccrcc","sample_count":106,"mrna_expression_sample_count":0},{"cancer_study_identifier":"kirc_bgi","name":"Kidney Renal Clear Cell Carcinoma (BGI, Nat Genet 2012)","type_of_cancer_id":"ccrcc","sample_count":98,"mrna_expression_sample_count":0},{"cancer_study_identifier":"ccrcc_irc_2014","name":"Kidney Renal Clear Cell Carcinoma (IRC, Nat Genet 2014)","type_of_cancer_id":"ccrcc","sample_count":79,"mrna_expression_sample_count":0},{"cancer_study_identifier":"kich_tcga_pub","name":"Kidney Chromophobe (TCGA, Cancer Cell 2014)","type_of_cancer … (1068 more chars)

═ answer (success)
Perfect! Here are the **renal cancer studies with RNA expression data**:

| Study | Type | Samples | RNA Expression |
|-------|------|---------|-----------------|
| [Kidney Renal Clear Cell Carcinoma (TCGA, Firehose Legacy)](https://www.cbioportal.org/study/summary?id=kirc_tcga) | CCRCC | 538 | 535 |
| [Renal Clear Cell Carcinoma (TCGA GDC, 2025)](https://www.cbioportal.org/study/summary?id=ccrcc_tcga_gdc) | CCRCC | 537 | 534 |
| [Kidney Renal Clear Cell Carcinoma (TCGA, PanCancer Atlas)](https://www.cbioportal.org/study/summary?id=kirc_tcga_pan_can_atlas_2018) | CCRCC | 512 | 510 |
| [Kidney Renal Clear Cell Carcinoma (TCGA, Nature 2013)](https://www.cbioportal.org/study/summary?id=kirc_tcga_pub) | CCRCC | 446 | 426 |
| [Renal Cell Carcinoma (CPTAC GDC, 2025)](https://www.cbioportal.org/study/summary?id=rcc_cptac_gdc) | Mixed RCC | 354 | 353 |
| [Kidney Renal Papillary Cell Carcinoma (TCGA, Firehose Legacy)](https://www.cbioportal.org/study/summary?id=kirp_tcga) | PRCC | 293 | 291 |
| [Papillary Renal Cell Carcinoma (TCGA GDC, 2025)](https://www.cbioportal.org/study/summary?id=prcc_tcga_gdc) | PRCC | 292 | 291 |
| [Kidney Renal Papillary Cell Carcinoma (TCGA, PanCancer Atlas)](https://www.cbioportal.org/study/summary?id=kirp_tcga_pan_can_atlas_2018) | PRCC | 283 | 283 |
| [Renal Cell Carcinoma (IMmotion150 Clinical Trial, Nat Med. 2018) - iAtlas Harmonized](https://www.cbioportal.org/study/summary?id=rcc_iatlas_immotion150_2018) | Mixed RCC | 263 | 263 |
| [Kidney Chromophobe (TCGA, Firehose Legacy)](https://www.cbioportal.org/study/summary?id=kich_tcga) | CHRCC | 113 | 66 |
| [Kidney Chromophobe (TCGA, Cancer Cell 2014)](https://www.cbioportal.org/study/summary?id=kich_tcga_pub) | CHRCC | 66 | 66 |
| [Chromophobe Renal Cell Carcinoma (TCGA GDC, 2025)](https://www.cbioportal.org/study/summary?id=chrcc_tcga_gdc) | CHRCC | 66 | 66 |
| [Kidney Chromophobe (TCGA, PanCancer Atlas)](https://www.cbioportal.org/study/summary?id=kich_tcga_pan_can_atlas_2018) | CHRCC | 65 | 65 |
| [Metastatic Renal Clear Cell Carcinoma (Phase 1 Biomarker Trial, Clin Cancer Res. 2016) - iAtlas Harmonized](https://www.cbioportal.org/study/summary?id=ccrcc_iatlas_choueiri_2016) | CCRCC | 16 | 16 |

**Summary:** **14 studies** have RNA expression data. The largest are:
- **TCGA clear cell RCC** studies (510–535 samples)
- **TCGA papillary RCC** studies (283–291 samples) 
- **CPTAC RCC** (353 samples)
- **IMmotion150 trial** (263 samples, immunotherapy biomarker study)
- **TCGA chromophobe** studies (65–66 samples)

The [TCGA PanCancer Atlas](https://www.cbioportal.org/study/summary?id=kirc_tcga_pan_can_atlas_2018) version is recommended for clear cell carcinoma if you want a comprehensive TCGA reference with 510 samples profiled.