Q (haiku): What is the median survival time in the Pediatric Neuroblastoma study from TARGET?

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

▶ list_studies
{
 "search": "neuroblastoma TARGET"
}

◀ 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":[{"cancer_study_identifier":"nbl_target_2018_pub","name":"Pediatric Neuroblastoma (TARGET, 2018)","type_of_cancer_id":"nbl","sample_count":1089,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=nbl_target_2018_pub"},{"cancer_study_identifier":"nbl_target_gdc","name":"Neuroblastoma (TARGET GDC, 2025)","type_of_cancer_id":"nbl","sample_count":533,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=nbl_target_gdc"},{"cancer_study_identifier":"nbl_msk_2023","name":"Pediatric Neuroblastoma (MSK, Nat Genet 2023)","type_of_cancer_id":"nbl","sample_count":223,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=nbl_msk_2023"}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH survival_data AS (
  SELECT 
    patient_unique_id,
    maxIf(toFloat64OrNull(attribute_value), attribute_name = 'OS_MONTHS') AS os_months,
    maxIf(attribute_value, attribute_name = 'OS_STATUS') AS os_status
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'nbl_target_2018_pub' 
    AND attribute_name IN ('OS_MONTHS', 'OS_STATUS')
  GROUP BY patient_unique_id
)
SELECT
  COUNT(*) AS n_patients,
  countIf(os_status = '1') AS n_events_deceased,
  countIf(os_status = '0') AS n_censored_living,
  round(min(os_months), 2) AS min_followup_months,
  round(max(os_months), 2) AS max_followup_months,
  round(avg(os_months), 2) AS mean_os_months
FROM survival_data
WHERE os_months IS NOT NULL AND os_status != ''
"
}

◀ result
{"rows":[{"n_patients":1072,"n_events_deceased":0,"n_censored_living":0,"min_followup_months":1,"max_followup_months":183,"mean_os_months":57.66}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT os_status, COUNT(*) as count
FROM (
  SELECT maxIf(attribute_value, attribute_name = 'OS_STATUS') AS os_status
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'nbl_target_2018_pub' 
    AND attribute_name = 'OS_STATUS'
  GROUP BY patient_unique_id
)
GROUP BY os_status
"
}

◀ result
{"rows":[{"count":4},{"os_status":"0:LIVING","count":675},{"os_status":"1:DECEASED","count":397}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH survival_data AS (
  SELECT 
    patient_unique_id,
    maxIf(toFloat64OrNull(attribute_value), attribute_name = 'OS_MONTHS') AS os_months,
    maxIf(attribute_value, attribute_name = 'OS_STATUS') AS os_status
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'nbl_target_2018_pub' 
    AND attribute_name IN ('OS_MONTHS', 'OS_STATUS')
  GROUP BY patient_unique_id
)
SELECT
  COUNT(*) AS n_patients,
  countIf(startsWith(os_status, '1')) AS n_events_deceased,
  countIf(startsWith(os_status, '0')) AS n_censored_living,
  round(min(os_months), 2) AS min_followup_months,
  round(max(os_months), 2) AS max_followup_months,
  round(avg(os_months), 2) AS mean_os_months
FROM survival_data
WHERE os_months IS NOT NULL AND os_status != ''
"
}

◀ result
{"rows":[{"n_patients":1072,"n_events_deceased":397,"n_censored_living":675,"min_followup_months":1,"max_followup_months":183,"mean_os_months":57.66}]}

═ answer (success)
## Median Survival in Pediatric Neuroblastoma (TARGET)

The **Pediatric Neuroblastoma (TARGET, 2018)** study includes 1,072 patients with overall survival data:

- **Deceased (events):** 397 patients (37%)
- **Living (censored):** 675 patients (63%)
- **Follow-up range:** 1–183 months (mean: 57.7 months)

**⚠️ Important:** I cannot report a median overall survival time from this query alone. Survival data is **censored** — patients still living have not yet reached the event. Calculating a true median requires Kaplan-Meier analysis, which accounts for censoring and is not available in direct database queries.

Since only 37% of patients had a death event (397 of 1,072), the Kaplan-Meier median OS is likely **not reached** in this cohort. To view the actual Kaplan-Meier survival curve and median (if reached), please use cBioPortal's Group Comparison tool:

[View Pediatric Neuroblastoma Survival Analysis](https://www.cbioportal.org/study/summary?id=nbl_target_2018_pub)

From the study page, navigate to the **Clinical Data** tab or use **Group Comparison** to generate Kaplan-Meier plots with the median OS and confidence intervals.