Q (haiku): How many patients have a shallow deletion for SMARCA4 in the POG study?

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

▶ resolve_and_route
{
 "studyKeywords": [
  "POG"
 ]
}

◀ 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
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"pog570_bcgsc_2020","name":"Pan-cancer Analysis of Advanced and Metastatic Tumors (BCGSC, Nature Cancer 2020)","sampleCount":570,"studyViewUrl":"https://www.cbioportal.org/study?id=pog570_bcgsc_2020","metadata":{"clinicalAttributeIds":["AGE","ANALYSIS_COHORT","BIOPSY_COHORT","BIOPSY_SITE","CANCER_TYPE","CANCER_TYPE_DETAILED","GENDER","HRD_SCORE","ICI","ICI_BEST_RESPONSE","ICI_DURABLE_CLINICAL_BENEFIT","ICI_MUTATION_CATEGORY","MSI_SCORE","MUTATION_COUNT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","PRIMARY_SITE","SAMPLE_COUNT","SAMPLE_TYPE","SOMATIC_STATUS","TMB_NONSYNONYMOUS","TREATED_WITH_ICI","TREATMENT_CATEGORY","TUMOR_CONTENT","T_CELLS_CD4_MEMORY_ACTIVATED","T_CELLS_CD4_MEMORY_RESTING","T_CELLS_CD4_NAIVE","T_CELLS_CD8","T_CELLS_FOLLICULAR_HELPER","T_CELLS_GAMMA_DELTA","T_CELLS_REGULATORY_TREGS"],"molecularProfileIds":["pog570_bcgsc_2020_gistic","pog570_bcgsc_2020_mutations","pog570_bcgsc_2020_rna_seq_mrna","pog570_bcgsc_2020_rna_seq_mrna_median_all_sample_Zscores","pog570_bcgsc_2020_structural_variants"],"genericAssayProfiles":["pog570_bcgsc_2020_immune_cell_signature"],"heatmapProfileIds":["pog570_bcgsc_2020_rna_seq_mrna_median_all_sample_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","generic_assay_immune_cell_signature"]}}]}}

▶ clickhouse_list_table_columns
{
 "table": "genomic_event_derived"
}

◀ result
{"columns":[{"name":"sample_unique_id","type":"String"},{"name":"hugo_gene_symbol","type":"String","comment":"HUGO gene symbol (e.g., TP53, KRAS, BRAF). Use for gene-specific queries."},{"name":"entrez_gene_id","type":"Int32"},{"name":"gene_panel_stable_id","type":"LowCardinality(String)"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"genetic_profile_stable_id","type":"LowCardinality(String)"},{"name":"variant_type","type":"LowCardinality(String)","comment":"Type of genomic event: \"mutation\" for SNVs/indels, \"cna\" for copy number alterations, \"structural_variant\" for SVs. Always filter by this."},{"name":"mutation_variant","type":"String","comment":"Protein change notation (e.g., p.V600E, p.R175H). Use for specific variant queries. \"NA\" for non-mutation events."},{"name":"mutation_type","type":"LowCardinality(String)"},{"name":"mutation_status","type":"LowCardinality(String)","comment":"For mutations: Somatic, Germline, UNKNOWN, or UNCALLED. Filter mutation_status != \"UNCALLED\" to exclude uncertain calls. Include all other statuses."},{"name":"driver_filter","type":"LowCardinality(String)"},{"name":"driver_filter_annotation","type":"String"},{"name":"driver_tiers_filter","type":"LowCardinality(String)"},{"name":"driver_tiers_filter_annotation","type":"String"},{"name":"cna_alteration","type":"Nullable(Int8)","comment":"Copy number alteration: only 2 = amplification (AMP) and -2 = deep deletion (HOMDEL) are stored. Shallow deletion (-1), diploid (0) and gain (1) are not in this table; query genetic_alteration_derived WHERE profile_type = 'gistic' (alteration_value is a String, e.g. '-1'). NULL for non-CNA events."},{"name":"cna_cytoband","type":"String"},{"name":"sv_event_info","type":"String"},{"name":"patient_unique_id","type":"String"},{"name":"off_panel","type":"Bool","comment":"Boolean: 1 = mutation outside gene panel coverage (off-panel), 0 = within panel (on-panel). Filter off_panel = 0 for reliable frequency calculations."}]}

▶ clickhouse_list_table_columns
{
 "table": "genetic_alteration_derived"
}

◀ result
{"columns":[{"name":"sample_unique_id","type":"String"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"hugo_gene_symbol","type":"String"},{"name":"profile_type","type":"LowCardinality(String)"},{"name":"alteration_value","type":"Nullable(String)"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT
    COUNT(DISTINCT patient_unique_id) as patient_count,
    COUNT(DISTINCT sample_unique_id) as sample_count
FROM genetic_alteration_derived ged
WHERE
    cancer_study_identifier = 'pog570_bcgsc_2020'
    AND hugo_gene_symbol = 'SMARCA4'
    AND profile_type = 'gistic'
    AND alteration_value = '-1'
"
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 47\n Code: 47. DB::Exception: Unknown expression or function identifier `patient_unique_id` in scope SELECT COUNTDistinct(patient_unique_id) AS patient_count, COUNTDistinct(sample_unique_id) AS sample_count FROM genetic_alteration_derived AS ged WHERE (cancer_study_identifier = 'pog570_bcgsc_2020') AND (hugo_gene_symbol = 'SMARCA4') AND (profile_type = 'gistic') AND (alteration_value = '-1'). Maybe you meant: ['sample_unique_id']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
SELECT
    COUNT(DISTINCT cd.patient_unique_id) as patient_count,
    COUNT(DISTINCT ged.sample_unique_id) as sample_count
FROM genetic_alteration_derived ged
JOIN clinical_data_derived cd 
    ON ged.sample_unique_id = cd.sample_unique_id
    AND ged.cancer_study_identifier = cd.cancer_study_identifier
WHERE
    ged.cancer_study_identifier = 'pog570_bcgsc_2020'
    AND ged.hugo_gene_symbol = 'SMARCA4'
    AND ged.profile_type = 'gistic'
    AND ged.alteration_value = '-1'
"
}

◀ result
{"rows":[{"patient_count":111,"sample_count":111}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT
    COUNT(DISTINCT patient_unique_id) as total_patients
FROM clinical_data_derived
WHERE cancer_study_identifier = 'pog570_bcgsc_2020'
"
}

◀ result
{"rows":[{"total_patients":570}]}

▶ navigate_to_results_view
{
 "studyIds": [
  "pog570_bcgsc_2020"
 ],
 "genes": [
  "SMARCA4"
 ],
 "tab": "oncoprint",
 "profileFilter": "gistic"
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/results/oncoprint?cancer_study_list=pog570_bcgsc_2020&gene_list=SMARCA4&case_set_id=pog570_bcgsc_2020_all&profileFilter=gistic","url":"https://www.cbioportal.org/results/oncoprint?cancer_study_list=pog570_bcgsc_2020&gene_list=SMARCA4&case_set_id=pog570_bcgsc_2020_all&profileFilter=gistic","data":{"studyIds":["pog570_bcgsc_2020"],"studies":[{"studyId":"pog570_bcgsc_2020","name":"Pan-cancer Analysis of Advanced and Metastatic Tumors (BCGSC, Nature Cancer 2020)","sampleCount":570}],"genes":["SMARCA4"],"caseSetId":"pog570_bcgsc_2020_all","pageDescription":"Visual summary of alterations in queried genes across samples."}}

═ answer (success)
[View SMARCA4 alterations in POG (OncoPrint)](https://www.cbioportal.org/results/oncoprint?cancer_study_list=pog570_bcgsc_2020&gene_list=SMARCA4&case_set_id=pog570_bcgsc_2020_all&profileFilter=gistic) — Visual summary of all SMARCA4 copy number alterations across the 570 POG samples, including the 111 with shallow deletions.