Q (sonnet): 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 … (7083 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
    cs.cancer_study_identifier,
    cs.name,
    cs.type_of_cancer_id,
    cs.sample_count,
    cs.mrna_expression_sample_count,
    cs.rna_seq_sample_count,
    cs.mrna_microarray_sample_count
FROM cancer_study cs
WHERE cs.type_of_cancer_id IN (
    SELECT code FROM (
        SELECT 'ccrcc' AS code UNION ALL SELECT 'mrc' UNION ALL SELECT 'mtscc' UNION ALL
        SELECT 'nccrcc' UNION ALL SELECT 'raml' UNION ALL SELECT 'rcc' UNION ALL
        SELECT 'rnet' UNION ALL SELECT 'rocy' UNION ALL SELECT 'rscc' UNION ALL
        SELECT 'sccrcc' UNION ALL SELECT 'ccprc' UNION ALL SELECT 'cdrcc' UNION ALL
        SELECT 'chrcc' UNION ALL SELECT 'fhrcc' UNION ALL SELECT 'nrcc' UNION ALL
        SELECT 'prcc' UNION ALL SELECT 'srcc' UNION ALL SELECT 'trcc' UNION ALL SELECT 'urcc'
    )
)
AND cs.mrna_expression_sample_count > 0
ORDER BY cs.mrna_expression_sample_count DESC
"
}

◀ result
{"rows":[{"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,"rna_seq_sample_count":534,"mrna_microarray_sample_count":72},{"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,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"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,"rna_seq_sample_count":510,"mrna_microarray_sample_count":0},{"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,"rna_seq_sample_count":417,"mrna_microarray_sample_count":56},{"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,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"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,"rna_seq_sample_count":291,"mrna_microarray_sample_count":16},{"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,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"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,"rna_seq_sample_count":283,"mrna_microarray_sample_count":0},{"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,"rna_seq_sample_count":0,"mrna_microarray_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,"rna_seq_sample_count":66,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"kich_tcga_pub","name":"Kidney Chromophobe (TCGA, Cancer Cell 2014)","type_of_cancer_id":"chrcc","sample_count":66,"mrna_expression_sample_count":66,"rna_seq_sample_count":66,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"chrcc_tcga_gdc","name":"Chromophobe Renal Cell Carcinoma (TCGA GDC, 2025)","type_of_cancer_id":"chrcc","sample_count":66,"mrna_expression_sample_count":66,"rna_seq_sample_count":0,"mrna_microarray_sample_c … (571 more chars)

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

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"kirc_tcga_pan_can_atlas_2018","name":"Kidney Renal Clear Cell Carcinoma (TCGA, PanCancer Atlas)","sampleCount":512,"studyViewUrl":"https://www.cbioportal.org/study?id=kirc_tcga_pan_can_atlas_2018","metadata":{"clinicalAttributeIds":["AGE","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","ANEUPLOIDY_SCORE","BUFFA_HYPOXIA_SCORE","CANCER_TYPE","CANCER_TYPE_ACRONYM","CANCER_TYPE_DETAILED","DAYS_LAST_FOLLOWUP","DAYS_TO_BIRTH","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DFS_MONTHS","DFS_STATUS","DSS_MONTHS","DSS_STATUS","ETHNICITY","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","GENETIC_ANCESTRY_LABEL","GRADE","HISTORY_NEOADJUVANT_TRTYN","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","INFORMED_CONSENT_VERIFIED","IN_PANCANPATHWAYS_FREEZE","MSI_SCORE_MANTIS","MSI_SENSOR_SCORE","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_PATIENT_ID","PATH_M_STAGE","PATH_N_STAGE","PATH_T_STAGE","PERSON_NEOPLASM_CANCER_STATUS","PFS_MONTHS","PFS_STATUS","PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT","PRIOR_DX","RACE","RADIATION_THERAPY","RAGNUM_HYPOXIA_SCORE","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SOMATIC_STATUS","SUBTYPE","TBL_SCORE","TISSUE_PROSPECTIVE_COLLECTION_INDICATOR","TISSUE_RETROSPECTIVE_COLLECTION_INDICATOR","TISSUE_SOURCE_SITE","TISSUE_SOURCE_SITE_CODE","TMB_NONSYNONYMOUS","TUMOR_TISSUE_SITE","TUMOR_TYPE","WEIGHT","WINTER_HYPOXIA_SCORE"],"molecularProfileIds":["kirc_tcga_pan_can_atlas_2018_gistic","kirc_tcga_pan_can_atlas_2018_log2CNA","kirc_tcga_pan_can_atlas_2018_mutations","kirc_tcga_pan_can_atlas_2018_rna_seq_v2_mrna","kirc_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","kirc_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","kirc_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","kirc_tcga_pan_can_atlas_2018_rppa","kirc_tcga_pan_can_atlas_2018_rppa_Zscores","kirc_tcga_pan_can_atlas_2018_structural_variants"],"genericAssayProfiles":["kirc_tcga_pan_can_atlas_2018_armlevel_cna","kirc_tcga_pan_can_atlas_2018_genetic_ancestry","kirc_tcga_pan_can_atlas_2018_methylation_hm27_hm450_merge","kirc_tcga_pan_can_atlas_2018_methylation_hm450"],"heatmapProfileIds":["kirc_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","kirc_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","kirc_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_ref_normal_Zscores","kirc_tcga_pan_can_atlas_2018_rppa_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","protein","generic_assay_armlevel_cna","generic_assay_genetic_ancestry","generic_assay_methylation"]}}]}}

═ answer (success)
[Explore Kidney Renal Clear Cell Carcinoma (TCGA, PanCancer Atlas) cohort](https://www.cbioportal.org/study?id=kirc_tcga_pan_can_atlas_2018) — 512 samples, RNA-Seq expression available (`rna_seq_v2_mrna` profile) alongside mutation, CNA, RPPA, methylation, and structural variant data.

If you'd like, I can generate direct links for any of the other renal studies listed above, or set up an expression-focused comparison (e.g., plots tab, gene-specific expression) for a particular gene or subtype.