Q (haiku): what are the most common events in her2- breast cancer?

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

▶ search_oncotree
{
 "search_term": "breast cancer HER2"
}

◀ 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":[]}

▶ search_oncotree
{
 "search_term": "breast"
}

▶ list_studies
{
 "search": "breast HER2",
 "limit": 10
}

◀ result
{"result":[{"code":"BREAST","name":"Breast","score":100,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST"},{"code":"BA","name":"Breast Angiosarcoma","score":60,"mainType":"Breast Sarcoma","tissue":"Breast","hierarchy":"BREAST > PBS > BA"},{"code":"BFN","name":"Breast Fibroepithelial Neoplasms","score":60,"mainType":"Breast Sarcoma","tissue":"Breast","hierarchy":"BREAST > BFN"},{"code":"BIALCL","name":"Breast Implant-Associated Anaplastic Large-Cell Lymphoma","score":60,"mainType":"Mature T and NK Neoplasms","tissue":"Lymphoid","hierarchy":"LYMPH > LNM > NHL > MTNN > ALCL > BIALCL"},{"code":"BNNOS","name":"Breast Neoplasm, NOS","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BNNOS"},{"code":"BRCANOS","name":"Breast Invasive Cancer, NOS","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > BRCANOS"},{"code":"BRCNOS","name":"Breast Invasive Carcinoma, NOS","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > BRCNOS"},{"code":"BRSRCC","name":"Breast Carcinoma with Signet Ring","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > BRSRCC"},{"code":"CSNOS","name":"Breast Invasive Carcinosarcoma, NOS","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > CSNOS"},{"code":"DCIS","name":"Breast Ductal Carcinoma In Situ","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > DCIS"},{"code":"IDC","name":"Breast Invasive Ductal Carcinoma","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > IDC"},{"code":"ILC","name":"Breast Invasive Lobular Carcinoma","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > ILC"},{"code":"IMMC","name":"Breast Invasive Mixed Mucinous Carcinoma","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > IMMC"},{"code":"LCIS","name":"Breast Lobular Carcinoma In Situ","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > LCIS"},{"code":"MDLC","name":"Breast Mixed Ductal and Lobular Carcinoma","score":60,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > MDLC"},{"code":"PBS","name":"Breast Sarcoma","score":60,"mainType":"Breast Sarcoma","tissue":"Breast","hierarchy":"BREAST > PBS"},{"code":"ACBC","name":"Adenoid Cystic Breast Cancer","score":50,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRCA > ACBC"},{"code":"BLPT","name":"Borderline Phyllodes Tumor of the Breast","score":50,"mainType":"Breast Sarcoma","tissue":"Breast","hierarchy":"BREAST > BFN > PT > BLPT"},{"code":"BPT","name":"Benign Phyllodes Tumor of the Breast","score":50,"mainType":"Breast Sarcoma","tissue":"Breast","hierarchy":"BREAST > BFN > PT > BPT"},{"code":"BRAME","name":"Adenomyoepithelioma of the Breast","score":50,"mainType":"Breast Cancer","tissue":"Breast","hierarchy":"BREAST > BRAME"},{"code":"BRCA","name":"Invasiv … (682 more chars)

◀ result
{"result":[{"cancer_study_identifier":"msk_met_2021","name":"MSK MetTropism (MSK, Cell 2021)","type_of_cancer_id":"mixed","sample_count":25775,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=msk_met_2021"},{"cancer_study_identifier":"brca_msk_erbb2_2026","name":"HER2-Low and HER2-Null Metastatic Breast Cancers treated with T-DXd (MSK, Clinical Cancer Research 2026)","type_of_cancer_id":"breast","sample_count":272,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=brca_msk_erbb2_2026"},{"cancer_study_identifier":"brca_mapk_hp_msk_2021","name":"MAPK on resistance to anti-HER2 therapy for breast cancer (MSK, Nat Commun. 2022)","type_of_cancer_id":"brca","sample_count":145,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=brca_mapk_hp_msk_2021"}]}

▶ clickhouse_list_tables
{}

◀ result
{"tables":[{"name":"allele_specific_copy_number"},{"name":"alteration_driver_annotation"},{"name":"cancer_study"},{"name":"cancer_study_query_preferences"},{"name":"cancer_study_tags"},{"name":"cbioportal_sequence_state"},{"name":"clinical_attribute_counts"},{"name":"clinical_attribute_meta"},{"name":"clinical_data_derived"},{"name":"clinical_event"},{"name":"clinical_event_data"},{"name":"clinical_event_data_derived"},{"name":"clinical_event_derived"},{"name":"clinical_patient"},{"name":"clinical_sample"},{"name":"cna_event"},{"name":"cna_panel_gene_coverage"},{"name":"cna_wes_coverage"},{"name":"co_altered_genes_in_study"},{"name":"copy_number_seg"},{"name":"copy_number_seg_file"},{"name":"gene"},{"name":"gene_alias"},{"name":"gene_alteration_frequency_by_cancer_type"},{"name":"gene_cna_distribution_in_study"},{"name":"gene_mutation_frequency_by_cancer_type"},{"name":"gene_mutation_frequency_in_studies"},{"name":"gene_mutation_frequency_in_study"},{"name":"gene_mutation_variants_in_study"},{"name":"gene_pair_coexpression"},{"name":"gene_panel"},{"name":"gene_panel_list"},{"name":"gene_panel_to_gene_derived"},{"name":"generic_assay_data_derived"},{"name":"generic_assay_meta_derived"},{"name":"generic_assay_profile_entity_derived"},{"name":"generic_entity_properties"},{"name":"geneset"},{"name":"geneset_gene"},{"name":"geneset_hierarchy_leaf"},{"name":"geneset_hierarchy_node"},{"name":"genetic_alteration"},{"name":"genetic_alteration_derived"},{"name":"genetic_entity"},{"name":"genetic_profile"},{"name":"genetic_profile_link"},{"name":"genetic_profile_samples"},{"name":"genomic_event_derived"},{"name":"gistic"},{"name":"gistic_to_gene"},{"name":"info"},{"name":"mut_sig"},{"name":"mutation"},{"name":"mutation_count_by_keyword"},{"name":"mutation_derived"},{"name":"mutation_event"},{"name":"mutation_panel_gene_coverage"},{"name":"mutation_wes_coverage"},{"name":"patient"},{"name":"reference_genome"},{"name":"reference_genome_gene"},{"name":"resource_definition"},{"name":"resource_patient"},{"name":"resource_sample"},{"name":"resource_study"},{"name":"sample"},{"name":"sample_cna_event"},{"name":"sample_derived"},{"name":"sample_list"},{"name":"sample_list_list"},{"name":"sample_profile"},{"name":"sample_to_gene_panel_derived"},{"name":"structural_variant"},{"name":"sv_panel_gene_coverage"},{"name":"sv_wes_coverage"},{"name":"top_cna_genes_in_study"},{"name":"top_mutated_genes_in_cohort"},{"name":"top_mutated_genes_in_study"},{"name":"top_sv_genes_in_study"},{"name":"treatment_counts_in_study"},{"name":"treatment_regimens_in_study"},{"name":"type_of_cancer"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT cancer_study_identifier, name, type_of_cancer_id
FROM cancer_study
WHERE type_of_cancer_id IN ('breast', 'brca')
ORDER BY cancer_study_identifier
LIMIT 20
",
 "max_rows": 50
}

◀ result
{"rows":[{"cancer_study_identifier":"brca_aurora_2023","name":"Metastatic Breast Cancer (AURORA US Network, Nat Cancer 2023)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_bccrc","name":"Breast Invasive Carcinoma (British Columbia, Nature 2012)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_bccrc_xenograft_2014","name":"Breast Cancer Xenografts (British Columbia, Nature 2015)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_broad","name":"Breast Invasive Carcinoma (Broad, Nature 2012)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_cptac_2020","name":"Proteogenomic landscape of breast cancer (CPTAC, Cell 2020)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_dfci_2020","name":"Metastatic Breast Cancer (DFCI, Cancer Discov 2020)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_dldccc_2022","name":"Triple Negative Breast Cancer (DLDCCC, Cancer Discov 2022)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_fuscc_2020","name":"Triple-Negative Breast Cancer (FUSCC, Cell Research 2020)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_hta9_htan_2022","name":"Breast Cancer (HTAN, 2022)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_iatlas_anders_2022","name":"Metastatic Triple Negative Breast Cancer (UNC Phase II Trial, J Immunother Cancer 2022) - iAtlas Harmonized","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_igr_2015","name":"Metastatic Breast Cancer (INSERM, PLoS Med 2016)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_jup_msk_2020","name":"Juvenile Papillomatosis and Breast Cancer (MSK,J Pathol. 2015)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_mapk_hp_msk_2021","name":"MAPK on resistance to anti-HER2 therapy for breast cancer (MSK, Nat Commun. 2022)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_mbcproject_2022","name":"The Metastatic Breast Cancer Project (Provisional, December 2021)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_mbcproject_wagle_2017","name":"The Metastatic Breast Cancer Project (Archived, 2020)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_metabric","name":"Breast Cancer (METABRIC, Nature 2012 & Nat Commun 2016)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_msk_2025","name":"Breast Carcinoma (MSK, NPJ Precis Oncol 2025)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_msk_erbb2_2026","name":"HER2-Low and HER2-Null Metastatic Breast Cancers treated with T-DXd (MSK, Clinical Cancer Research 2026)","type_of_cancer_id":"breast"},{"cancer_study_identifier":"brca_mskcc_2019","name":"Breast Cancer (MSK, NPJ Breast Cancer 2019)","type_of_cancer_id":"brca"},{"cancer_study_identifier":"brca_pareja_msk_2020","name":"Breast Cancer (MSK, Clinical Cancer Res 2020)","type_of_cancer_id":"brca"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT cancer_study_identifier, attribute_name
FROM clinical_data_derived
WHERE cancer_study_identifier LIKE 'brca_%'
  AND (upper(attribute_name) LIKE '%HER2%' OR upper(attribute_name) LIKE '%ER%' OR upper(attribute_name) LIKE '%HORMONE%')
ORDER BY cancer_study_identifier, attribute_name
LIMIT 50
",
 "max_rows": 100
}

◀ result
{"rows":[{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"CANCER_TYPE"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"CANCER_TYPE_DETAILED"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"FRACTION_GENOME_ALTERED"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"NUMBER_PROLIFERATING_CELLS"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PERCENT_INFLAM_INFILTRATION"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PERCENT_LYMPHOCYTE_INFILTRATION"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PERCENT_NECROSIS"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PERCENT_NORMAL_CELLS"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PERCENT_STROMAL_CELLS"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PERCENT_TUMOR_CELLS"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PERCENT_TUMOR_NUCLEI"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRESERVATION"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRIM_ER"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRIM_ER_PCT"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRIM_HER2"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRIM_HER2_COPY"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRIM_HER2_COPYNUM"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRIM_HER2_FISH"},{"cancer_study_identifier":"brca_aurora_2023","attribute_name":"PRIM_HER2_INTERPRETATION"},{"cancer_study_identifier":"brca_bccrc","attribute_name":"CANCER_TYPE"},{"cancer_study_identifier":"brca_bccrc","attribute_name":"CANCER_TYPE_DETAILED"},{"cancer_study_identifier":"brca_bccrc","attribute_name":"ER_STATUS"},{"cancer_study_identifier":"brca_bccrc","attribute_name":"IHC_HER2"},{"cancer_study_identifier":"brca_bccrc_xenograft_2014","attribute_name":"CANCER_TYPE"},{"cancer_study_identifier":"brca_bccrc_xenograft_2014","attribute_name":"CANCER_TYPE_DETAILED"},{"cancer_study_identifier":"brca_bccrc_xenograft_2014","attribute_name":"FRACTION_GENOME_ALTERED"},{"cancer_study_identifier":"brca_broad","attribute_name":"CANCER_TYPE"},{"cancer_study_identifier":"brca_broad","attribute_name":"CANCER_TYPE_DETAILED"},{"cancer_study_identifier":"brca_broad","attribute_name":"ER_STATUS"},{"cancer_study_identifier":"brca_broad","attribute_name":"HER2_AMPLIFICATION_STATUS"},{"cancer_study_identifier":"brca_broad","attribute_name":"HER2_STATUS"},{"cancer_study_identifier":"brca_cptac_2020","attribute_name":"CANCER TYPE"},{"cancer_study_identifier":"brca_cptac_2020","attribute_name":"CANCER_TYPE_DETAILED"},{"cancer_study_identifier":"brca_cptac_2020","attribute_name":"CIBERSORT_ABSOLUTE_SCORE"},{"cancer_study_identifier":"brca_cptac_2020","attribute_name":"ERBB2_GENE_AMPLIFIED"},{"cancer_study_identifier":"brca_cptac_2020","attribute_name":"ERBB2_PROTEOGENOMIC … (1175 more chars)

▶ clickhouse_run_select_query
{
 "query": "
-- First, check what HER2 values are available in AURORA
SELECT DISTINCT upper(attribute_value) as her2_status, COUNT(*) as count
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND attribute_name = 'PRIM_HER2_INTERPRETATION'
GROUP BY upper(attribute_value)
ORDER BY count DESC
",
 "max_rows": 20
}

◀ result
{"rows":[{"her2_status":"NEGATIVE","count":37},{"her2_status":"POSITIVE","count":10},{"her2_status":"UNKNOWN","count":8}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get most common clinical events in HER2-negative breast cancer patients
WITH her2_neg_patients AS (
  SELECT DISTINCT patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'brca_aurora_2023'
    AND attribute_name = 'PRIM_HER2_INTERPRETATION'
    AND upper(attribute_value) = 'NEGATIVE'
)
SELECT 
  lower(event_type) as event_type,
  COUNT(DISTINCT patient_unique_id) as patient_count,
  COUNT(*) as event_count
FROM clinical_event_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND patient_unique_id IN (SELECT patient_unique_id FROM her2_neg_patients)
GROUP BY event_type
ORDER BY event_count DESC
",
 "max_rows": 20
}

◀ 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 WITH her2_neg_patients AS (SELECT DISTINCT patient_unique_id FROM clinical_data_derived WHERE (cancer_study_identifier = 'brca_aurora_2023') AND (attribute_name = 'PRIM_HER2_INTERPRETATION') AND (upper(attribute_value) = 'NEGATIVE')) SELECT lower(event_type) AS event_type, COUNTDistinct(patient_unique_id) AS patient_count, COUNT(*) AS event_count FROM clinical_event_derived WHERE (cancer_study_identifier = 'brca_aurora_2023') AND (patient_unique_id IN (SELECT patient_unique_id FROM her2_neg_patients)) GROUP BY event_type ORDER BY event_count DESC. Maybe you meant: ['patient_stable_id']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_list_table_columns
{
 "table": "clinical_event_derived"
}

◀ result
{"columns":[{"name":"clinical_event_id","type":"Int64"},{"name":"patient_id","type":"Nullable(Int64)"},{"name":"patient_stable_id","type":"String"},{"name":"start_date","type":"Nullable(Int64)"},{"name":"stop_date","type":"Nullable(Int64)"},{"name":"event_type","type":"LowCardinality(String)"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"}]}

▶ clickhouse_list_table_columns
{
 "table": "clinical_data_derived"
}

◀ result
{"columns":[{"name":"internal_id","type":"Int32"},{"name":"sample_unique_id","type":"String","comment":"Globally unique sample ID: cancer_study_identifier + \"_\" + sample.stable_id. Empty for patient-level attributes. Use this for sample filtering and joins."},{"name":"patient_unique_id","type":"String","comment":"Globally unique patient ID: cancer_study_identifier + \"_\" + patient.stable_id. Present for both sample and patient-level attributes."},{"name":"attribute_name","type":"LowCardinality(String)","comment":"Clinical attribute name (e.g., SAMPLE_TYPE, CANCER_TYPE, AGE, OS_MONTHS). Use with attribute_value for filtering. AGE may be floored or capped for de-identification (e.g. all children recorded as 18, or everyone 89+ recorded as 89 or 90): before age statistics check for a pile-up at the min/max, and if present compute age from DAYS_TO_BIRTH (-days / 365.25)."},{"name":"attribute_value","type":"String","comment":"Value of the clinical attribute (String). For SAMPLE_TYPE: Primary, Metastasis, Local Recurrence, Unknown. Missing values are empty strings, so use toFloat64OrNull(attribute_value) for numeric comparisons — CAST fails on them."},{"name":"cancer_study_identifier","type":"LowCardinality(String)"},{"name":"type","type":"LowCardinality(String)","comment":"Data level: \"sample\" for sample-level attributes (e.g., SAMPLE_TYPE), \"patient\" for patient-level attributes (e.g., AGE, OS_MONTHS)."}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get most common clinical events in HER2-negative breast cancer patients
WITH her2_neg_patients AS (
  SELECT DISTINCT patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'brca_aurora_2023'
    AND attribute_name = 'PRIM_HER2_INTERPRETATION'
    AND upper(attribute_value) = 'NEGATIVE'
)
SELECT 
  lower(event_type) as event_type,
  COUNT(DISTINCT patient_stable_id) as patient_count,
  COUNT(*) as event_count
FROM clinical_event_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND patient_stable_id IN (
    SELECT DISTINCT substring(patient_unique_id, length('brca_aurora_2023_') + 1) 
    FROM her2_neg_patients
  )
GROUP BY event_type
ORDER BY event_count DESC
",
 "max_rows": 20
}

◀ result
{"rows":[{"event_type":"treatment","patient_count":37,"event_count":220},{"event_type":"diagnosis","patient_count":37,"event_count":172},{"event_type":"specimen","patient_count":36,"event_count":102},{"event_type":"surgery","patient_count":34,"event_count":40},{"event_type":"follow up","patient_count":37,"event_count":37}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get most common treatments in HER2-negative breast cancer patients
WITH her2_neg_patients AS (
  SELECT DISTINCT patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'brca_aurora_2023'
    AND attribute_name = 'PRIM_HER2_INTERPRETATION'
    AND upper(attribute_value) = 'NEGATIVE'
)
SELECT 
  key,
  value,
  COUNT(DISTINCT patient_stable_id) as patient_count,
  COUNT(*) as event_count
FROM clinical_event_data_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND lower(event_type) = 'treatment'
  AND patient_stable_id IN (
    SELECT DISTINCT substring(patient_unique_id, length('brca_aurora_2023_') + 1) 
    FROM her2_neg_patients
  )
GROUP BY key, value
ORDER BY patient_count DESC
LIMIT 30
",
 "max_rows": 50
}

◀ 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_stable_id` in scope WITH her2_neg_patients AS (SELECT DISTINCT patient_unique_id FROM clinical_data_derived WHERE (cancer_study_identifier = 'brca_aurora_2023') AND (attribute_name = 'PRIM_HER2_INTERPRETATION') AND (upper(attribute_value) = 'NEGATIVE')) SELECT key, value, COUNTDistinct(patient_stable_id) AS patient_count, COUNT(*) AS event_count FROM clinical_event_data_derived WHERE (cancer_study_identifier = 'brca_aurora_2023') AND (lower(event_type) = 'treatment') AND (patient_stable_id IN (SELECT DISTINCT substring(patient_unique_id, length('brca_aurora_2023_') + 1) FROM her2_neg_patients)) GROUP BY key, value ORDER BY patient_count DESC LIMIT 30. Maybe you meant: ['patient_unique_id']. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_list_table_columns
{
 "table": "clinical_event_data_derived"
}

◀ result
{"columns":[{"name":"patient_unique_id","type":"String"},{"name":"key","type":"String"},{"name":"value","type":"String"},{"name":"start_date","type":"Int32"},{"name":"stop_date","type":"Int32"},{"name":"event_type","type":"LowCardinality(String)"},{"name":"cancer_study_identifier","type":"LowCardinality(String)"}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get most common treatments in HER2-negative breast cancer patients
WITH her2_neg_patients AS (
  SELECT DISTINCT patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'brca_aurora_2023'
    AND attribute_name = 'PRIM_HER2_INTERPRETATION'
    AND upper(attribute_value) = 'NEGATIVE'
)
SELECT 
  key,
  value,
  COUNT(DISTINCT patient_unique_id) as patient_count,
  COUNT(*) as event_count
FROM clinical_event_data_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND lower(event_type) = 'treatment'
  AND patient_unique_id IN (SELECT patient_unique_id FROM her2_neg_patients)
GROUP BY key, value
ORDER BY patient_count DESC
LIMIT 30
",
 "max_rows": 50
}

◀ result
{"rows":[{"key":"Treatment_SAMPLE_TYPE","value":"Metastatic Treatment","patient_count":36,"event_count":153},{"key":"COMMENT","value":"Date Estimated","patient_count":34,"event_count":67},{"key":"Treatment_SAMPLE_TYPE","value":"Primary Treatment","patient_count":34,"event_count":67},{"key":"Treatment_TYPE","value":"Metastatic Chemotherapy","patient_count":32,"event_count":98},{"key":"RESPONSE","value":"Progression","patient_count":28,"event_count":93},{"key":"Treatment_TYPE","value":"Adjuvent Adj Chemotherapy","patient_count":25,"event_count":25},{"key":"Treatment_TYPE","value":"Metastatic Hormonal Targeted","patient_count":24,"event_count":55},{"key":"RESPONSE_FAIL_REASON","value":"Yes - local progression- spread of disease in a previously diagnosed organ site","patient_count":23,"event_count":61},{"key":"Treatment_TYPE","value":"Adjuvent Adj Hormonal Therapy","patient_count":17,"event_count":17},{"key":"AGENT","value":"Capecitabine/ Xeloda","patient_count":16,"event_count":16},{"key":"RESPONSE_FAIL_REASON","value":"No","patient_count":16,"event_count":26},{"key":"RESPONSE","value":"Unknown","patient_count":15,"event_count":19},{"key":"AGENT","value":"Anthracycline and Taxane based","patient_count":15,"event_count":15},{"key":"Treatment_TYPE","value":"Neoadjuvent NeoAdj Chemotherapy","patient_count":15,"event_count":16},{"key":"RESPONSE_FAIL_REASON","value":"Yes - distant progression- development of disease in a previously undiagnosed organ site","patient_count":14,"event_count":24},{"key":"RESPONSE_FAIL_REASON","value":"Yes - both local and distant progression","patient_count":14,"event_count":20},{"key":"AGENT","value":"Tamoxifen","patient_count":13,"event_count":16},{"key":"AGENT","value":"Paclitaxel/ Taxol","patient_count":12,"event_count":14},{"key":"RESPONSE_NON_FAIL_REASON","value":"Toxicity/side effects","patient_count":11,"event_count":16},{"key":"AGENT","value":"Eribulin/ Halaven","patient_count":11,"event_count":11},{"key":"RESPONSE","value":"Mixed response","patient_count":9,"event_count":11},{"key":"RESPONSE_FAIL_REASON","value":"Unknown","patient_count":8,"event_count":11},{"key":"RESPONSE","value":"Some improvement","patient_count":7,"event_count":14},{"key":"AGENT","value":"Anthracyline based","patient_count":7,"event_count":7},{"key":"RESPONSE","value":"Stable disease","patient_count":7,"event_count":10},{"key":"AGENT","value":"Gemcitabine/ Gemzar","patient_count":6,"event_count":6},{"key":"AGENT","value":"Carboplatin,Gemcitabine/ Gemzar","patient_count":6,"event_count":6},{"key":"AGENT","value":"Carboplatin","patient_count":6,"event_count":6},{"key":"AGENT","value":"Doxorubicin liposomal injection/ Doxil","patient_count":6,"event_count":6},{"key":"AGENT","value":"Fulvestrant/ Faslodex","patient_count":6,"event_count":6}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get most common treatment AGENTS in HER2-negative breast cancer patients
WITH her2_neg_patients AS (
  SELECT DISTINCT patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'brca_aurora_2023'
    AND attribute_name = 'PRIM_HER2_INTERPRETATION'
    AND upper(attribute_value) = 'NEGATIVE'
)
SELECT 
  value as drug_agent,
  COUNT(DISTINCT patient_unique_id) as n_patients,
  COUNT(*) as n_events,
  ROUND(COUNT(DISTINCT patient_unique_id) * 100.0 / 37, 1) as pct_her2neg_patients
FROM clinical_event_data_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND lower(event_type) = 'treatment'
  AND key = 'AGENT'
  AND patient_unique_id IN (SELECT patient_unique_id FROM her2_neg_patients)
GROUP BY value
ORDER BY n_patients DESC
LIMIT 20
",
 "max_rows": 30
}

◀ result
{"rows":[{"drug_agent":"Capecitabine/ Xeloda","n_patients":16,"n_events":16,"pct_her2neg_patients":43.2},{"drug_agent":"Anthracycline and Taxane based","n_patients":15,"n_events":15,"pct_her2neg_patients":40.5},{"drug_agent":"Tamoxifen","n_patients":13,"n_events":16,"pct_her2neg_patients":35.1},{"drug_agent":"Paclitaxel/ Taxol","n_patients":12,"n_events":14,"pct_her2neg_patients":32.4},{"drug_agent":"Eribulin/ Halaven","n_patients":11,"n_events":11,"pct_her2neg_patients":29.7},{"drug_agent":"Anthracyline based","n_patients":7,"n_events":7,"pct_her2neg_patients":18.9},{"drug_agent":"Fulvestrant/ Faslodex","n_patients":6,"n_events":6,"pct_her2neg_patients":16.2},{"drug_agent":"Gemcitabine/ Gemzar","n_patients":6,"n_events":6,"pct_her2neg_patients":16.2},{"drug_agent":"Carboplatin,Gemcitabine/ Gemzar","n_patients":6,"n_events":6,"pct_her2neg_patients":16.2},{"drug_agent":"Carboplatin","n_patients":6,"n_events":6,"pct_her2neg_patients":16.2},{"drug_agent":"Doxorubicin liposomal injection/ Doxil","n_patients":6,"n_events":6,"pct_her2neg_patients":16.2},{"drug_agent":"Bevacizumab/ Avastin","n_patients":5,"n_events":7,"pct_her2neg_patients":13.5},{"drug_agent":"Taxane based","n_patients":5,"n_events":5,"pct_her2neg_patients":13.5},{"drug_agent":"Paclitaxel, albumin-bound/ Abraxane","n_patients":5,"n_events":5,"pct_her2neg_patients":13.5},{"drug_agent":"Exemestane/ Aromasin","n_patients":4,"n_events":5,"pct_her2neg_patients":10.8},{"drug_agent":"Vinorelbine/ Navelbine","n_patients":4,"n_events":4,"pct_her2neg_patients":10.8},{"drug_agent":"Letrozole","n_patients":3,"n_events":3,"pct_her2neg_patients":8.1},{"drug_agent":"Everolimus/ Afinitor, Exemestane/ Aromasin","n_patients":3,"n_events":3,"pct_her2neg_patients":8.1},{"drug_agent":"Other_Cisplatin/Paclitaxel","n_patients":2,"n_events":2,"pct_her2neg_patients":5.4},{"drug_agent":"Other - Carboplatin","n_patients":2,"n_events":2,"pct_her2neg_patients":5.4}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get treatment types and clinical characteristics in HER2-negative patients
WITH her2_neg_patients AS (
  SELECT DISTINCT patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'brca_aurora_2023'
    AND attribute_name = 'PRIM_HER2_INTERPRETATION'
    AND upper(attribute_value) = 'NEGATIVE'
)
SELECT 
  'Treatment Type' as category,
  value as detail,
  COUNT(DISTINCT patient_unique_id) as n_patients,
  ROUND(COUNT(DISTINCT patient_unique_id) * 100.0 / 37, 1) as pct
FROM clinical_event_data_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND lower(event_type) = 'treatment'
  AND key = 'Treatment_TYPE'
  AND patient_unique_id IN (SELECT patient_unique_id FROM her2_neg_patients)
GROUP BY value
ORDER BY n_patients DESC

UNION ALL

SELECT
  'ER Status' as category,
  attribute_value as detail,
  COUNT(DISTINCT patient_unique_id) as n_patients,
  ROUND(COUNT(DISTINCT patient_unique_id) * 100.0 / 37, 1) as pct
FROM clinical_data_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND attribute_name = 'PRIM_ER'
  AND patient_unique_id IN (
    SELECT patient_unique_id FROM clinical_data_derived
    WHERE cancer_study_identifier = 'brca_aurora_2023'
      AND attribute_name = 'PRIM_HER2_INTERPRETATION'
      AND upper(attribute_value) = 'NEGATIVE'
  )
GROUP BY attribute_value
ORDER BY n_patients DESC
",
 "max_rows": 50
}

◀ result
{"rows":[{"category":"ER Status","detail":"Negative","n_patients":19,"pct":51.4},{"category":"ER Status","detail":"Positive","n_patients":17,"pct":45.9},{"category":"ER Status","detail":"Unknown","n_patients":1,"pct":2.7},{"category":"Treatment Type","detail":"Metastatic Chemotherapy","n_patients":32,"pct":86.5},{"category":"Treatment Type","detail":"Adjuvent Adj Chemotherapy","n_patients":25,"pct":67.6},{"category":"Treatment Type","detail":"Metastatic Hormonal Targeted","n_patients":24,"pct":64.9},{"category":"Treatment Type","detail":"Adjuvent Adj Hormonal Therapy","n_patients":17,"pct":45.9},{"category":"Treatment Type","detail":"Neoadjuvent NeoAdj Chemotherapy","n_patients":15,"pct":40.5},{"category":"Treatment Type","detail":"Adjuvent Adj Chemotherapy 2","n_patients":4,"pct":10.8},{"category":"Treatment Type","detail":"Adjuvent Adj Hormonal Therapy 2","n_patients":3,"pct":8.1},{"category":"Treatment Type","detail":"Adjuvent Adj Chemotherapy 4","n_patients":1,"pct":2.7},{"category":"Treatment Type","detail":"Adjuvent Adj Chemotherapy 3","n_patients":1,"pct":2.7}]}

▶ clickhouse_run_select_query
{
 "query": "
-- Get treatment response patterns in HER2-negative patients
WITH her2_neg_patients AS (
  SELECT DISTINCT patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'brca_aurora_2023'
    AND attribute_name = 'PRIM_HER2_INTERPRETATION'
    AND upper(attribute_value) = 'NEGATIVE'
)
SELECT 
  value as treatment_response,
  COUNT(DISTINCT patient_unique_id) as n_patients,
  COUNT(*) as n_events,
  ROUND(COUNT(DISTINCT patient_unique_id) * 100.0 / 37, 1) as pct_her2neg_patients
FROM clinical_event_data_derived
WHERE cancer_study_identifier = 'brca_aurora_2023'
  AND lower(event_type) = 'treatment'
  AND key = 'RESPONSE'
  AND patient_unique_id IN (SELECT patient_unique_id FROM her2_neg_patients)
GROUP BY value
ORDER BY n_patients DESC
",
 "max_rows": 20
}

◀ result
{"rows":[{"treatment_response":"Progression","n_patients":28,"n_events":93,"pct_her2neg_patients":75.7},{"treatment_response":"Unknown","n_patients":15,"n_events":19,"pct_her2neg_patients":40.5},{"treatment_response":"Mixed response","n_patients":9,"n_events":11,"pct_her2neg_patients":24.3},{"treatment_response":"Stable disease","n_patients":7,"n_events":10,"pct_her2neg_patients":18.9},{"treatment_response":"Some improvement","n_patients":7,"n_events":14,"pct_her2neg_patients":18.9},{"treatment_response":"Ongoing","n_patients":1,"n_events":1,"pct_her2neg_patients":2.7}]}

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

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"brca_aurora_2023","name":"Metastatic Breast Cancer (AURORA US Network, Nat Cancer 2023)","sampleCount":155,"studyViewUrl":"https://www.cbioportal.org/study?id=brca_aurora_2023","metadata":{"clinicalAttributeIds":["ADJ_TXT","AGE","CANCER_TYPE","CANCER_TYPE_DETAILED","DEM_ETHNICITY","DEM_FAMILY","DEM_RACE","DFS_MONTHS","DFS_STATUS","FRACTION_GENOME_ALTERED","HISTOLOGY","METASTATIC_SITE","MUTATIONAL_SOURCE","MUTATION_COUNT","NUMBER_PROLIFERATING_CELLS","ONCOTREE_CODE","OS_STATUS","PARTICIPATING_SITE","PERCENT_INFLAM_INFILTRATION","PERCENT_LYMPHOCYTE_INFILTRATION","PERCENT_NECROSIS","PERCENT_NORMAL_CELLS","PERCENT_STROMAL_CELLS","PERCENT_TUMOR_CELLS","PERCENT_TUMOR_NUCLEI","PORTION_BARCODES","PRESERVATION","PRIM2","PRIM_ER","PRIM_ER_PCT","PRIM_HER2","PRIM_HER2_COPY","PRIM_HER2_COPYNUM","PRIM_HER2_FISH","PRIM_HER2_INTERPRETATION","PRIM_M","PRIM_N","PRIM_ONCOTYPE","PRIM_ONCOTYPE_SOURCE","PRIM_PR","PRIM_PR_PCT","PRIM_STAGE_DX","PRIM_T","RADS_LOCAL_YN","RADS_METASTATIC_YN","SAMPLE_COUNT","SAMPLE_DISPLAY_NAME","SAMPLE_TYPE","SEX","SLIDE_BARCODE","SURGPATH","YPM","YPN","YPT","YSTAGE_AT_PSTAGING"],"molecularProfileIds":["brca_aurora_2023_log2CNA","brca_aurora_2023_mutations","brca_aurora_2023_rna_seq_v2_mrna_median_all_sample_Zscores"],"genericAssayProfiles":["brca_aurora_2023_methylation_hm450"],"heatmapProfileIds":["brca_aurora_2023_rna_seq_v2_mrna_median_all_sample_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","generic_assay_methylation"]}}]}}

▶ navigate_to_study_view
{
 "studyIds": [
  "brca_aurora_2023"
 ],
 "filterJson": {
  "clinicalDataFilters": [
   {
    "attributeId": "PRIM_HER2_INTERPRETATION",
    "values": [
     {
      "value": "Negative"
     }
    ]
   }
  ]
 }
}

◀ result
{"success":true,"message":"Navigating to https://www.cbioportal.org/study/summary?id=brca_aurora_2023#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22PRIM_HER2_INTERPRETATION%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Negative%22%7D%5D%7D%5D%7D","url":"https://www.cbioportal.org/study/summary?id=brca_aurora_2023#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22PRIM_HER2_INTERPRETATION%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Negative%22%7D%5D%7D%5D%7D","data":{"studyIds":["brca_aurora_2023"],"studies":[{"studyId":"brca_aurora_2023","name":"Metastatic Breast Cancer (AURORA US Network, Nat Cancer 2023)","sampleCount":155}],"tab":"summary","hasFilters":true,"hasPlotsConfig":false,"pageDescription":"Overview of all study data displayed as interactive charts summarizing available clinical and genomic data. Click within a chart to filter. Additional charts may be available in the Charts menu."}}

═ answer (success)
Perfect! Now I have comprehensive information. Let me create a final summary with all the data:

## Most Common Events in HER2-Negative Breast Cancer

Based on analysis of the **AURORA metastatic breast cancer cohort** (n=37 HER2-negative patients), here are the most prevalent events:

### **Overall Event Distribution**

| Event Type | Patients | Events |
|---|---|---|
| **Treatment** | 37 (100%) | 220 |
| **Diagnosis** | 37 (100%) | 172 |
| **Specimen** | 36 (97%) | 102 |
| **Surgery** | 34 (92%) | 40 |
| **Follow-up** | 37 (100%) | 37 |

### **Most Common Drug Treatments**

The most frequently used chemotherapy and targeted agents in HER2-negative patients:

| Drug/Regimen | Patients | % of HER2-Neg |
|---|---|---|
| **Capecitabine/Xeloda** | 16 | 43.2% |
| **Anthracycline + Taxane** | 15 | 40.5% |
| **Tamoxifen** | 13 | 35.1% |
| **Paclitaxel/Taxol** | 12 | 32.4% |
| **Eribulin/Halaven** | 11 | 29.7% |
| **Fulvestrant/Faslodex** | 6 | 16.2% |
| **Gemcitabine/Gemzar** | 6 | 16.2% |

### **Treatment Patterns**

**By Treatment Type:**
- **Metastatic Chemotherapy**: 32 patients (86.5%)
- **Adjuvant Chemotherapy**: 25 patients (67.6%)
- **Metastatic Hormonal Therapy**: 24 patients (64.9%)
- **Adjuvant Hormonal Therapy**: 17 patients (45.9%)
- **Neoadjuvant Chemotherapy**: 15 patients (40.5%)

### **Treatment Response**

- **Disease Progression**: 28 patients (75.7%) — by far the most common outcome
- **Unknown Response**: 15 patients (40.5%)
- **Mixed/Partial Response**: 9 patients (24.3%)
- **Stable Disease**: 7 patients (18.9%)

### **Clinical Characteristics**

Among HER2-negative patients:
- **ER-Negative**: 19 (51.4%) — indicating triple-negative phenotype
- **ER-Positive**: 17 (45.9%) — hormone-receptor-positive, HER2-negative subtype

---

### [View HER2-Negative Breast Cancer Patients (AURORA Study)](https://www.cbioportal.org/study/summary?id=brca_aurora_2023#filterJson=%7B%22clinicalDataFilters%22%3A%5B%7B%22attributeId%22%3A%22PRIM_HER2_INTERPRETATION%22%2C%22values%22%3A%5B%7B%22value%22%3A%22Negative%22%7D%5D%7D%5D%7D)

**Key Insight:** Treatment responses are dominated by **disease progression (76% of patients)**, reflecting the aggressive nature of metastatic HER2-negative breast cancer. Chemotherapy (especially capecitabine and taxane-based regimens) combined with hormonal therapy (especially in ER-positive HER2-negative cases) represents the most common therapeutic approach in this metastatic cohort.