Q (haiku): In the Breast Invasive Carcinoma TCGA study what are the top 5 down-regulated genes in TP53 mutated samples compared to non-mutated ones?

▶ read_guide
{
 "uri": "cbioportal://common-pitfalls"
}

▶ resolve_and_route
{
 "studyKeywords": [
  "TCGA",
  "breast",
  "invasive",
  "carcinoma"
 ]
}

◀ result
# Common Query Pitfalls Guide

## Overview
This guide highlights frequent mistakes when analyzing cBioPortal data and provides solutions to avoid them.

## Critical Pitfalls

### 1. 🚨 CRITICAL MUTATION FREQUENCY ERRORS

#### ❌ WRONG: Using study-wide totals for gene frequencies
```sql
-- INCORRECT - This gives wrong frequencies!
SELECT
    hugo_gene_symbol,
    COUNT(DISTINCT sample_unique_id) as altered_samples,
    (SELECT COUNT(DISTINCT sample_unique_id)
     FROM genomic_event_derived
     WHERE cancer_study_identifier = 'your_study_id') as total_samples
FROM genomic_event_derived
WHERE variant_type = 'mutation' AND cancer_study_identifier = 'your_study_id'
GROUP BY hugo_gene_symbol;
```
**Problem**: Different genes have different profiling coverage - you can't use study-wide totals!

#### ❌ WRONG: Not using gene-specific profiling denominators
```sql
-- INCORRECT - Missing gene-specific denominators
SELECT
    hugo_gene_symbol,
    COUNT(DISTINCT sample_unique_id) as altered_samples
FROM genomic_event_derived
WHERE variant_type = 'mutation'
GROUP BY hugo_gene_symbol;
-- Missing: WHERE ARE THE DENOMINATORS FOR EACH GENE?
```

#### ❌ WRONG: Skipping individual gene profiling queries
**Problem**: Failing to run separate profiling queries for EACH gene in results.
**Each gene has different coverage**: TP53 might be profiled in 25,040 samples, MUC16 in 23,000, etc.

#### ✅ CORRECT: Complete gene-specific workflow
```sql
-- STEP 1: Get altered counts per gene
SELECT
    hugo_gene_symbol,
    entrez_gene_id,
    COUNT(DISTINCT CASE WHEN off_panel = 0 THEN sample_unique_id END) AS numberOfAlteredSamplesOnPanel,
    COUNT(*) AS totalMutationEvents
FROM genomic_event_derived
WHERE variant_type = 'mutation' AND mutation_status != 'UNCALLED'
GROUP BY entrez_gene_id, hugo_gene_symbol
ORDER BY numberOfAlteredSamplesOnPanel DESC;

-- STEP 2: FOR EACH GENE, run this profiling query:
SELECT COUNT(DISTINCT stgp.sample_unique_id) AS numberOfProfiledSamples
FROM sample_to_gene_panel_derived stgp
JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
JOIN gene g ON gpl.gene_id = g.entrez_gene_id
WHERE stgp.alteration_type = 'MUTATION_EXTENDED'
  AND g.hugo_gene_symbol = 'TP53'  -- Replace with each gene from Step 1
  AND stgp.cancer_study_identifier = 'your_study_id';

-- STEP 3: Calculate frequency = numberOfAlteredSamplesOnPanel / numberOfProfiledSamples * 100
```

#### 🚨 WORKFLOW REQUIREMENTS VIOLATIONS:
- **Missing denominator columns**: Must show gene-specific profiled samples per row
- **Wrong table format**: Should be | Gene | # Mutations | # Samples | Profiled Samples | Sample % |
- **Not replacing gene symbols**: Must replace 'TP53' with actual gene for each query
- **Using study totals**: Never use genomic_event_derived for total sample counts

### 2. 🚨 OFF-PANEL MUTATION INCLUSION

#### ❌ Wrong: Including off-panel mutations
```sql
-- INCORRECT - Includes mutations outside gene panels
SELEC … (37891 more chars)

◀ result
{"success":true,"message":"Found 5 matching studies. Pick the best match (prefer TCGA → prefer PanCancer Atlas), use its metadata to call the appropriate navigation tool(s) and generate URLs immediately. Present other studies as alternatives.","data":{"totalCount":5,"studiesWithMetadata":[{"studyId":"brca_tcga","name":"Breast Invasive Carcinoma (TCGA, Firehose Legacy)","sampleCount":1108,"studyViewUrl":"https://www.cbioportal.org/study?id=brca_tcga","metadata":{"clinicalAttributeIds":["AGE","AJCC_METASTASIS_PATHOLOGIC_PM","AJCC_NODES_PATHOLOGIC_PN","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","AJCC_TUMOR_PATHOLOGIC_PT","BRACHYTHERAPY_TOTAL_DOSE_POINT_A","CANCER_TYPE","CANCER_TYPE_DETAILED","CENT17_COPY_NUMBER","CLINICAL_STAGE","CLIN_M_STAGE","CLIN_N_STAGE","CLIN_T_STAGE","DAYS_TO_COLLECTION","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DAYS_TO_PATIENT_PROGRESSION_FREE","DAYS_TO_SPECIMEN_COLLECTION","DAYS_TO_TUMOR_PROGRESSION","DFS_MONTHS","DFS_STATUS","DISEASE_CODE","ER_POSITIVITY_SCALE_OTHER","ER_POSITIVITY_SCALE_USED","ER_STATUS_BY_IHC","ER_STATUS_IHC_PERCENT_POSITIVE","ETHNICITY","EXTRANODAL_INVOLVEMENT","FIRST_SURGICAL_PROCEDURE_OTHER","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","HER2_AND_CENT17_CELLS_COUNT","HER2_AND_CENT17_SCALE_OTHER","HER2_CENT17_COUNTED_CELLS_COUNT","HER2_CENT17_RATIO","HER2_COPY_NUMBER","HER2_FISH_METHOD","HER2_FISH_STATUS","HER2_IHC_PERCENT_POSITIVE","HER2_IHC_SCORE","HER2_POSITIVITY_METHOD_TEXT","HER2_POSITIVITY_SCALE_OTHER","HISTOLOGICAL_DIAGNOSIS","HISTOLOGICAL_SUBTYPE","HISTORY_NEOADJUVANT_TRTYN","HISTORY_OTHER_MALIGNANCY","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","IHC_HER2","IHC_SCORE","INFORMED_CONSENT_VERIFIED","INITIAL_PATHOLOGIC_DX_YEAR","IS_FFPE","LONGEST_DIMENSION","LYMPH_NODES_EXAMINED","LYMPH_NODES_EXAMINED_HE_COUNT","LYMPH_NODES_EXAMINED_IHC_COUNT","LYMPH_NODE_EXAMINED_COUNT","MARGIN_STATUS_REEXCISION","MENOPAUSE_STATUS","METASTATIC_SITE_OTHER","METASTATIC_SITE_PATIENT","METASTATIC_TUMOR_INDICATOR","METHOD_OF_INITIAL_SAMPLE_PROCUREMENT","METHOD_OF_INITIAL_SAMPLE_PROCUREMENT_OTHER","METHOD_OF_SAMPLE_PROCUREMENT","MICROMET_DETECTION_BY_IHC","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","NTE_CENT17_HER2_OTHER_SCALE","NTE_CENT_17_HER2_RATIO","NTE_CENT_17_SIGNAL_NUMBER","NTE_ER_IHC_INTENSITY_SCORE","NTE_ER_POSITIVITY_DEFINE_METHOD","NTE_ER_POSITIVITY_OTHER_SCALE","NTE_ER_STATUS","NTE_ER_STATUS_IHC_POSITIVE","NTE_HER2_FISH_DEFINE_METHOD","NTE_HER2_FISH_STATUS","NTE_HER2_POSITIVITY_IHC_SCORE","NTE_HER2_POSITIVITY_METHOD","NTE_HER2_POSITIVITY_OTHER_SCALE","NTE_HER2_SIGNAL_NUMBER","NTE_HER2_STATUS","NTE_HER2_STATUS_IHC_POSITIVE","NTE_PR_IHC_INTENSITY_SCORE","NTE_PR_POSITIVITY_DEFINE_METHOD","NTE_PR_POSITIVITY_OTHER_SCALE","NTE_PR_STATUS_BY_IHC","NTE_PR_STATUS_IHC_POSITIVE","OCT_EMBEDDED","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_METHOD_OF_SAMPLE_PROCUREMENT","OTHER_PATIENT_ID","OTHER_SAMPLE_ID","PATHOLOGY_REPORT_FILE_NAME","PATHOLOGY_REPORT_UUID","PATH_MARGIN","PHARMACEUTICAL_TX_ADJUVAN … (11342 more chars)

▶ clickhouse_run_select_query
{
 "query": "
-- Step 1: Identify TP53 mutated vs non-mutated samples in BRCA TCGA
WITH tp53_status AS (
  SELECT DISTINCT 
    sample_unique_id,
    CASE 
      WHEN sample_unique_id IN (
        SELECT DISTINCT sample_unique_id 
        FROM genomic_event_derived 
        WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018' 
          AND hugo_gene_symbol = 'TP53' 
          AND variant_type = 'mutation' 
          AND mutation_status != 'UNCALLED'
      ) THEN 'TP53_mutated'
      ELSE 'TP53_wildtype'
    END as tp53_status
  FROM genomic_event_derived
  WHERE cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
    AND variant_type IN ('mutation', 'cna')
),
-- Step 2: Get mRNA expression data and TP53 status
expression_data AS (
  SELECT 
    g.hugo_gene_symbol,
    g.sample_unique_id,
    t.tp53_status,
    CAST(g.alteration_value AS Float64) as z_score
  FROM genetic_alteration_derived g
  JOIN tp53_status t ON g.sample_unique_id = t.sample_unique_id
  WHERE g.cancer_study_identifier = 'brca_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_all_sample_Zscores'
    AND g.alteration_value IS NOT NULL
    AND g.alteration_value != ''
)
-- Step 3: Calculate mean expression by gene and TP53 status
SELECT 
  hugo_gene_symbol,
  COUNT(CASE WHEN tp53_status = 'TP53_mutated' THEN 1 END) as tp53_mut_count,
  COUNT(CASE WHEN tp53_status = 'TP53_wildtype' THEN 1 END) as tp53_wt_count,
  avg(CASE WHEN tp53_status = 'TP53_mutated' THEN z_score END) as tp53_mut_mean_zscore,
  avg(CASE WHEN tp53_status = 'TP53_wildtype' THEN z_score END) as tp53_wt_mean_zscore,
  (avg(CASE WHEN tp53_status = 'TP53_mutated' THEN z_score END) - 
   avg(CASE WHEN tp53_status = 'TP53_wildtype' THEN z_score END)) as mean_zscore_diff
FROM expression_data
GROUP BY hugo_gene_symbol
ORDER BY mean_zscore_diff ASC
LIMIT 50
",
 "max_rows": 100
}

◀ result
{"rows":[{"hugo_gene_symbol":"AGR3","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-1.0889971181556204,"tp53_wt_mean_zscore":0.27375180055401654,"mean_zscore_diff":-1.3627489187096369},{"hugo_gene_symbol":"SRARP","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-1.1275674351585023,"tp53_wt_mean_zscore":0.21484806094182854,"mean_zscore_diff":-1.342415496100331},{"hugo_gene_symbol":"TBC1D9","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.8261645533141215,"tp53_wt_mean_zscore":0.39253448753462594,"mean_zscore_diff":-1.2186990408487475},{"hugo_gene_symbol":"ABCC8","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-1.0157181556195958,"tp53_wt_mean_zscore":0.1892671745152356,"mean_zscore_diff":-1.2049853301348314},{"hugo_gene_symbol":"CT62","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.9696149855907769,"tp53_wt_mean_zscore":0.22250831024930773,"mean_zscore_diff":-1.1921232958400845},{"hugo_gene_symbol":"THSD4","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.7970538904899137,"tp53_wt_mean_zscore":0.3727879501385039,"mean_zscore_diff":-1.1698418406284177},{"hugo_gene_symbol":"TFF1","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.9482475504322772,"tp53_wt_mean_zscore":0.2205815789473684,"mean_zscore_diff":-1.1688291293796456},{"hugo_gene_symbol":"CIRBP","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.8014066282420751,"tp53_wt_mean_zscore":0.361842243767313,"mean_zscore_diff":-1.163248872009388},{"hugo_gene_symbol":"ARSG","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.7892017291066284,"tp53_wt_mean_zscore":0.37396191135734064,"mean_zscore_diff":-1.1631636404639691},{"hugo_gene_symbol":"ANKRA2","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.7893625360230547,"tp53_wt_mean_zscore":0.372779085872576,"mean_zscore_diff":-1.1621416218956306},{"hugo_gene_symbol":"DELE1","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.7910674351585016,"tp53_wt_mean_zscore":0.37070277008310276,"mean_zscore_diff":-1.1617702052416043},{"hugo_gene_symbol":"BORCS7","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.7852775216138326,"tp53_wt_mean_zscore":0.37136371191135714,"mean_zscore_diff":-1.1566412335251897},{"hugo_gene_symbol":"C5AR2","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.791935158501441,"tp53_wt_mean_zscore":0.3613536011080332,"mean_zscore_diff":-1.1532887596094743},{"hugo_gene_symbol":"ESR1","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.7818461095100866,"tp53_wt_mean_zscore":0.37000415512465323,"mean_zscore_diff":-1.15185026463474},{"hugo_gene_symbol":"GATA3","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0.7776746397694521,"tp53_wt_mean_zscore":0.36893919667590036,"mean_zscore_diff":-1.1466138364453524},{"hugo_gene_symbol":"SCUBE2","tp53_mut_count":347,"tp53_wt_count":722,"tp53_mut_mean_zscore":-0. … (6683 more chars)

▶ navigate_to_group_comparison
{
 "studyIds": [
  "brca_tcga_pan_can_atlas_2018"
 ],
 "groups": [
  {
   "name": "TP53 Mutated",
   "studyViewFilter": {
    "geneFilters": [
     {
      "molecularProfileIds": [
       "brca_tcga_pan_can_atlas_2018_mutations"
      ],
      "geneQueries": [
       [
        {
         "hugoGeneSymbol": "TP53"
        }
       ]
      ]
     }
    ]
   }
  },
  {
   "name": "TP53 Wild-Type",
   "isUnselected": true
  }
 ]
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/comparison/overlap?comparisonId=6ab4a135e497aad53593bef6","url":"https://www.cbioportal.org/comparison/overlap?comparisonId=6ab4a135e497aad53593bef6","data":{"description":"Group comparison (2 custom groups)","studies":["brca_tcga_pan_can_atlas_2018"],"totalGroups":2,"groups":[{"name":"TP53 Mutated","sampleCount":347},{"name":"TP53 Wild-Type","sampleCount":737}],"studyViewUrl":"https://www.cbioportal.org/study?id=brca_tcga_pan_can_atlas_2018","groupUrls":[{"groupName":"TP53 Mutated","url":"https://www.cbioportal.org/study?id=brca_tcga_pan_can_atlas_2018#filterJson=%7B%22geneFilters%22%3A%5B%7B%22molecularProfileIds%22%3A%5B%22brca_tcga_pan_can_atlas_2018_mutations%22%5D%2C%22geneQueries%22%3A%5B%5B%7B%22hugoGeneSymbol%22%3A%22TP53%22%7D%5D%5D%7D%5D%7D"}],"pageDescription":"Diagram showing sample and patient overlap across TP53 Mutated (347) vs TP53 Wild-Type (737)."}}

═ answer (success)
Perfect! Here are the navigation links for interactive exploration:

### **Links for Further Analysis**

**[View TP53 Mutated vs Wild-Type Comparison](https://www.cbioportal.org/comparison/overlap?comparisonId=6ab4a135e497aad53593bef6)**
- 347 TP53-mutated samples vs 737 TP53 wild-type samples
- Click the **mRNA** tab to see expression differences across all genes
- Compare survival, clinical attributes, and alteration patterns between groups

**[Browse Breast Cancer Cohort (TCGA PanCancer Atlas)](https://www.cbioportal.org/study?id=brca_tcga_pan_can_atlas_2018)**
- 1,084 total breast cancer samples
- Filter by TP53 status or explore expression/mutation profiles

---

### **Key Findings Summary**

- **AGR3** (Anterior Gradient 3) and **SRARP** show the most dramatic down-regulation, differing by ~1.36 Z-score units
- **ESR1** (Estrogen Receptor 1) and **GATA3** (luminal transcription factors) also rank among the top down-regulated genes, suggesting TP53 mutations correlate with altered luminal differentiation
- These genes may reflect a shift toward a more mesenchymal or basal-like phenotype in TP53-mutated breast cancers