Q (haiku): What fraction of patients were older than five when diagnosed according to the Pediatric Neuroblastoma study from TARGET?

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

▶ search_oncotree
{
 "search_term": "neuroblastoma"
}

▶ list_studies
{
 "search": "TARGET neuroblastoma",
 "verbose": true
}

◀ 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":[{"code":"NBL","name":"Neuroblastoma","score":80,"mainType":"Peripheral Nervous System","tissue":"Peripheral Nervous System","hierarchy":"PNS > NBL"},{"code":"GNBL","name":"Ganglioneuroblastoma","score":50,"mainType":"Peripheral Nervous System","tissue":"Peripheral Nervous System","hierarchy":"PNS > GNBL"},{"code":"ONBL","name":"Olfactory Neuroblastoma","score":50,"mainType":"Embryonal Tumor","tissue":"CNS/Brain","hierarchy":"BRAIN > EMBT > ONBL"}]}

◀ result
{"result":[{"cancer_study_identifier":"nbl_target_gdc","name":"Neuroblastoma (TARGET GDC, 2025)","description":"TARGET Neuroblastoma. Source data from <A HREF=\"https://gdc.cancer.gov\">NCI GDC</A> and generated in Aug 2025 using <A HREF=\"https://cda.readthedocs.io/en/latest/\">Cancer Data Aggregator</A>.","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_target_2018_pub","name":"Pediatric Neuroblastoma (TARGET, 2018)","description":"Whole genome or whole exome sequencing on 1089 NBL samples. Comprehensive profiling of 1089 NBL samples. <p>TARGET data is intended exclusively for biomedical research using pediatric data (i.e., the research objectives cannot be accomplished using data from adults) that focus on the development of more effective treatments, diagnostic tests, or prognostic markers for childhood cancers. Moreover, TARGET data can be used for research relevant to the biology, causes, treatment and late complications of treatment of pediatric cancers, but is not intended for the sole purposes of methods and/or tool development (please see <a href=\"https://ocg.cancer.gov/programs/target/using-target-data\">Using TARGET Data</a> section of the OCG website). If you are interested in using TARGET data for publication or other research purposes, you must follow the <a href=\"https://ocg.cancer.gov/programs/target/target-publication-guidelines\">TARGET Publication Guidelines</a>.</p>","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_msk_2023","name":"Pediatric Neuroblastoma (MSK, Nat Genet 2023)","description":"Targeted sequencing of 223 pediatric neuroblastoma tumor/normal pairs via MSK-IMPACT.","type_of_cancer_id":"nbl","sample_count":223,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=nbl_msk_2023"}]}

▶ 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": "
SELECT DISTINCT attribute_name, attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'nbl_target_2018_pub'
  AND (attribute_name LIKE '%AGE%' OR attribute_name LIKE '%DIAGNOSIS%')
LIMIT 100
"
}

◀ result
{"rows":[{"attribute_name":"AGE","attribute_value":"5"},{"attribute_name":"AGE","attribute_value":"3"},{"attribute_name":"AGE","attribute_value":"1"},{"attribute_name":"AGE","attribute_value":"2"},{"attribute_name":"AGE","attribute_value":"7"},{"attribute_name":"AGE","attribute_value":"4"},{"attribute_name":"AGE","attribute_value":"6"},{"attribute_name":"AGE","attribute_value":"13"},{"attribute_name":"AGE","attribute_value":"9"},{"attribute_name":"AGE","attribute_value":"11"},{"attribute_name":"AGE","attribute_value":"8"},{"attribute_name":"AGE","attribute_value":"12"},{"attribute_name":"AGE","attribute_value":"15"},{"attribute_name":"AGE","attribute_value":"14"},{"attribute_name":"AGE","attribute_value":"10"},{"attribute_name":"AGE","attribute_value":"19"},{"attribute_name":"AGE","attribute_value":"0"},{"attribute_name":"AGE","attribute_value":"18"},{"attribute_name":"AGE","attribute_value":"16"},{"attribute_name":"AGE","attribute_value":"17"},{"attribute_name":"AGE","attribute_value":"21"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"1530"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"911"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"280"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"224"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"579"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"45"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"307"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"2436"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"10"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"1689"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"1454"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"291"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"1002"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"194"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"2048"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"4594"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"710"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"474"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"471"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"956"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"184"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"8"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"21"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"700"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"7"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"611"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"520"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"564"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"1261"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"3095"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"161"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"1837"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"1040"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"58"},{"attribute_name":"AGE_IN_DAYS","attribute_value":"6 … (2530 more chars)

▶ clickhouse_run_select_query
{
 "query": "
WITH age_data AS (
  SELECT 
    patient_unique_id,
    anyIf(toFloat64OrNull(attribute_value), attribute_name = 'AGE') AS age_years,
    anyIf(toFloat64OrNull(attribute_value), attribute_name = 'AGE_IN_DAYS') AS age_days
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'nbl_target_2018_pub'
    AND attribute_name IN ('AGE', 'AGE_IN_DAYS')
  GROUP BY patient_unique_id
)
SELECT 
  COUNT(*) AS total_patients,
  COUNT(CASE WHEN age_days IS NOT NULL THEN 1 END) AS patients_with_age_days,
  COUNT(CASE WHEN age_days IS NOT NULL AND (age_days / 365.25) > 5 THEN 1 END) AS patients_older_than_5,
  ROUND(100.0 * COUNT(CASE WHEN age_days IS NOT NULL AND (age_days / 365.25) > 5 THEN 1 END) / 
        COUNT(CASE WHEN age_days IS NOT NULL THEN 1 END), 2) AS fraction_pct
FROM age_data
WHERE age_days IS NOT NULL
",
 "max_rows": 10
}

◀ result
{"rows":[{"total_patients":1076,"patients_with_age_days":1076,"patients_older_than_5":125,"fraction_pct":11.62}]}

═ answer (success)
## Answer

Based on the Pediatric Neuroblastoma (TARGET, 2018) study data:

**11.62% (125/1,076 patients)** were older than 5 years old when diagnosed.

### Note on Data Accuracy

I used `AGE_IN_DAYS` to compute exact age (dividing by 365.25) rather than the `AGE` attribute in years. This is because pediatric TARGET studies commonly floor the `AGE` field for de-identification purposes. The `AGE_IN_DAYS` attribute provides more precise age calculation and is the recommended approach for pediatric cohorts as noted in the cBioPortal clinical data guide.