Q (haiku): In lower grade glioma, are there genes which are overexpressed in any of the molecular subtypes?

▶ read_guide
{
 "uri": "cbioportal://gene-expression-guide"
}

▶ list_studies
{
 "search": "lower grade glioma",
 "verbose": true
}

◀ result
# Gene Expression Analysis Guide

This guide covers continuous-value genomic data: gene **expression**, **copy number** values, **methylation**, and related profile types. Mutation/CNA/SV *frequency* analysis lives in `cbioportal://mutation-frequency-guide`.

## Where this data lives

Continuous per-sample-per-gene values are stored in `genetic_alteration_derived`:

| Column | Description |
|---|---|
| `sample_unique_id` | `<study>_<sample_stable_id>` |
| `cancer_study_identifier` | study scope |
| `hugo_gene_symbol` | gene |
| `profile_type` | which assay/normalization (see below) |
| `alteration_value` | the actual value — stored as Nullable(String); cast with `toFloat64OrNull` |

`alteration_value` is a string because the same column hosts many different value scales. The `''` and `'NA'` sentinels mean "missing"; always filter them out and use `toFloat64OrNull(alteration_value) IS NOT NULL` for downstream math.

## Discovering profile types for a study

Different studies expose different profile types depending on what assays were run and how the data was normalized. Always check what a specific study supports before picking one:

```sql
SELECT DISTINCT profile_type
FROM genetic_alteration_derived
WHERE cancer_study_identifier = 'brca_metabric'
ORDER BY profile_type;
```

Common values across the public portal:

| Family | Profile types |
|---|---|
| mRNA expression | `rna_seq_v2_mrna`, `rna_seq_v2_mrna_median_Zscores`, `rna_seq_v2_mrna_median_all_sample_Zscores` (TCGA PanCancer Atlas), `mrna`, `mrna_median_Zscores`, `mrna_seq_v2_rsem`, `mrna_seq_v2_rsem_Zscores`, `mrna_seq_cpm`, `mrna_seq_fpkm`, `mrna_U133`, `mrna_outliers` |
| Copy number (continuous) | `cna`, `linear_CNA`, `log2CNA`, `cna_consensus`, `cna_rae`, `gistic` |
| Methylation | `methylation_hm27`, `methylation_hm450`, `methylation_epic`, `methylation_promoters_rrbs` |
| miRNA | `mirna`, `mirna_median_Zscores` |
| Protein | `protein_quantification`, `protein_level`, `RPPA` |

**Z-score vs raw choice.** When the user asks "is X correlated with Y", either works for Spearman (rank-based) — Pearson would care. Default to the non-Z-score variant if both exist, and call out which one in the response.

## Canonical recipe — Spearman correlation between two genes

```sql
SELECT *
FROM gene_pair_coexpression(
    study        = 'brca_metabric',
    gene_a       = 'TP53',
    gene_b       = 'MYC',
    profile_type = 'mrna'
);
```

Returns one row: `(gene_a, gene_b, profile_type, spearman_correlation, num_samples)`.

- `spearman_correlation` in [−1, 1]; `NULL` when fewer than 3 valid paired samples.
- Mirrors cbioportal-backend's `ClickhouseCoExpressionMapper.getCoExpressions`, simplified to a pair lookup (the backend computes one ref gene vs ALL other genes for the coexpression page; here the agent asks about a specific pair).

### Verified examples

| Study | gene_a | gene_b | profile_type | spearman | n |
|---|---|---|---|---|---|
| `brca_metabric` | TP53 | MYC | `mrna` | 0.118 | 1980 |
|  … (4931 more chars)

◀ result
{"result":[{"cancer_study_identifier":"lgg_tcga","name":"Brain Lower Grade Glioma (TCGA, Firehose Legacy)","description":"TCGA Brain Lower Grade Glioma. Source data from <A HREF=\"http://gdac.broadinstitute.org/runs/stddata__2016_01_28/data/LGG/20160128/\">GDAC Firehose</A>. Previously known as TCGA Provisional.","type_of_cancer_id":"difg","sample_count":530,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=lgg_tcga"},{"cancer_study_identifier":"lgg_tcga_pan_can_atlas_2018","name":"Brain Lower Grade Glioma (TCGA, PanCancer Atlas)","description":"Brain Lower Grade Glioma TCGA PanCancer data. The original data is <a href=\"https://gdc.cancer.gov/about-data/publications/pancanatlas\">here</a>. The publications are <a href=\"https://www.cell.com/pb-assets/consortium/pancanceratlas/pancani3/index.html\">here</a>.","type_of_cancer_id":"difg","sample_count":514,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=lgg_tcga_pan_can_atlas_2018"}]}

▶ clickhouse_list_table_columns
{
 "table": "genetic_alteration_derived"
}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT profile_type
FROM genetic_alteration_derived
WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
ORDER BY profile_type
"
}

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

◀ result
{"rows":[{"profile_type":"gistic"},{"profile_type":"log2CNA"},{"profile_type":"rna_seq_v2_mrna"},{"profile_type":"rna_seq_v2_mrna_median_Zscores"},{"profile_type":"rna_seq_v2_mrna_median_all_sample_Zscores"},{"profile_type":"rppa"},{"profile_type":"rppa_Zscores"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT attribute_value
FROM clinical_data_derived
WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND attribute_name = 'SUBTYPE'
ORDER BY attribute_value
"
}

◀ result
{"rows":[{},{"attribute_value":"LGG_IDHmut-codel"},{"attribute_value":"LGG_IDHmut-non-codel"},{"attribute_value":"LGG_IDHwt"}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH subtype_data AS (
  SELECT 
    g.hugo_gene_symbol,
    c.attribute_value AS subtype,
    toFloat64OrNull(g.alteration_value) AS expr_value
  FROM genetic_alteration_derived g
  INNER JOIN clinical_data_derived c USING (sample_unique_id)
  WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND g.alteration_value NOT IN ('', 'NA')
    AND toFloat64OrNull(g.alteration_value) IS NOT NULL
    AND c.attribute_name = 'SUBTYPE'
    AND c.attribute_value != ''
),
subtype_stats AS (
  SELECT 
    hugo_gene_symbol,
    subtype,
    count() AS n_samples,
    median(expr_value) AS median_zscore,
    avg(expr_value) AS mean_zscore
  FROM subtype_data
  GROUP BY hugo_gene_symbol, subtype
  HAVING count() >= 5
),
gene_variance AS (
  SELECT 
    hugo_gene_symbol,
    max(median_zscore) - min(median_zscore) AS zscore_range,
    count() AS n_subtypes
  FROM subtype_stats
  GROUP BY hugo_gene_symbol
  HAVING count() = 3 AND max(median_zscore) - min(median_zscore) > 1.0
  ORDER BY zscore_range DESC
  LIMIT 30
)
SELECT DISTINCT
  s.hugo_gene_symbol,
  s.subtype,
  s.n_samples,
  round(s.median_zscore, 2) AS median_zscore,
  CASE 
    WHEN s.median_zscore > 0.5 THEN 'OVEREXPRESSED'
    WHEN s.median_zscore < -0.5 THEN 'UNDEREXPRESSED'
    ELSE 'SIMILAR'
  END AS expression_status
FROM subtype_stats s
WHERE s.hugo_gene_symbol IN (SELECT hugo_gene_symbol FROM gene_variance)
ORDER BY s.hugo_gene_symbol, 
  CASE WHEN s.subtype = 'LGG_IDHmut-codel' THEN 1 
       WHEN s.subtype = 'LGG_IDHmut-non-codel' THEN 2 
       ELSE 3 END
LIMIT 200
",
 "max_rows": 200
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
WITH subtype_data AS (
  SELECT 
    g.hugo_gene_symbol,
    c.attribute_value AS subtype,
    toFloat64OrNull(g.alteration_value) AS expr_value
  FROM genetic_alteration_derived g
  INNER JOIN clinical_data_derived c USING (sample_unique_id)
  WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND g.alteration_value NOT IN ('', 'NA')
    AND toFloat64OrNull(g.alteration_value) IS NOT NULL
    AND c.attribute_name = 'SUBTYPE'
    AND c.attribute_value != ''
),
subtype_stats AS (
  SELECT 
    hugo_gene_symbol,
    subtype,
    count() AS n_samples,
    median(expr_value) AS median_zscore
  FROM subtype_data
  GROUP BY hugo_gene_symbol, subtype
  HAVING count() >= 3
),
gene_variance AS (
  SELECT 
    hugo_gene_symbol,
    max(median_zscore) - min(median_zscore) AS zscore_range
  FROM subtype_stats
  WHERE subtype IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
  GROUP BY hugo_gene_symbol
  HAVING count() = 3
  ORDER BY zscore_range DESC
  LIMIT 50
)
SELECT 
  s.hugo_gene_symbol,
  s.subtype,
  s.n_samples,
  round(s.median_zscore, 2) AS median_zscore
FROM subtype_stats s
WHERE s.hugo_gene_symbol IN (SELECT hugo_gene_symbol FROM gene_variance)
  AND s.subtype IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
ORDER BY s.hugo_gene_symbol, 
  CASE WHEN s.subtype = 'LGG_IDHmut-codel' THEN 1 
       WHEN s.subtype = 'LGG_IDHmut-non-codel' THEN 2 
       ELSE 3 END
",
 "max_rows": 300
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT 
  attribute_value AS subtype,
  count() AS n_samples
FROM clinical_data_derived
WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND attribute_name = 'SUBTYPE'
GROUP BY attribute_value
ORDER BY n_samples DESC
"
}

◀ result
{"rows":[{"subtype":"LGG_IDHmut-non-codel","n_samples":248},{"subtype":"LGG_IDHmut-codel","n_samples":167},{"subtype":"LGG_IDHwt","n_samples":92},{"n_samples":7}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH subtype_expr AS (
  SELECT 
    g.hugo_gene_symbol,
    c.attribute_value AS subtype,
    toFloat64OrNull(g.alteration_value) AS expr_value
  FROM genetic_alteration_derived g
  INNER JOIN clinical_data_derived c USING (sample_unique_id)
  WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND c.attribute_name = 'SUBTYPE'
    AND c.attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
),
subtype_medians AS (
  SELECT 
    hugo_gene_symbol,
    subtype,
    median(expr_value) AS median_z
  FROM subtype_expr
  WHERE expr_value IS NOT NULL
  GROUP BY hugo_gene_symbol, subtype
),
gene_range AS (
  SELECT 
    hugo_gene_symbol,
    max(median_z) - min(median_z) AS z_range,
    max(median_z) AS max_z,
    min(median_z) AS min_z
  FROM subtype_medians
  GROUP BY hugo_gene_symbol
  HAVING count(DISTINCT subtype) = 3 AND max(median_z) - min(median_z) >= 0.8
  ORDER BY z_range DESC
  LIMIT 50
)
SELECT 
  s.hugo_gene_symbol,
  s.subtype,
  round(s.median_z, 2) AS median_zscore,
  round(g.max_z, 2) AS max_zscore,
  round(g.min_zscore, 2) AS min_zscore,
  round(g.z_range, 2) AS zscore_range
FROM subtype_medians s
JOIN gene_range g ON s.hugo_gene_symbol = g.hugo_gene_symbol
ORDER BY g.z_range DESC, s.hugo_gene_symbol
LIMIT 200
",
 "max_rows": 200
}

◀ 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: Identifier 'g.min_zscore' cannot be resolved from subquery with name g. In scope WITH subtype_expr AS (SELECT g.hugo_gene_symbol, c.attribute_value AS subtype, toFloat64OrNull(g.alteration_value) AS expr_value FROM genetic_alteration_derived AS g INNER JOIN clinical_data_derived AS c USING (sample_unique_id) WHERE (g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018') AND (g.profile_type = 'rna_seq_v2_mrna_median_Zscores') AND (c.attribute_name = 'SUBTYPE') AND (c.attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt'))), subtype_medians AS (SELECT hugo_gene_symbol, subtype, median(expr_value) AS median_z FROM subtype_expr WHERE expr_value IS NOT NULL GROUP BY hugo_gene_symbol, subtype), gene_range AS (SELECT hugo_gene_symbol, max(median_z) - min(median_z) AS z_range, max(median_z) AS max_z, min(median_z) AS min_z FROM subtype_medians GROUP BY hugo_gene_symbol HAVING (countDistinct(subtype) = 3) AND ((max(median_z) - min(median_z)) >= 0.8) ORDER BY z_range DESC LIMIT 50) SELECT s.hugo_gene_symbol, s.subtype, round(s.median_z, 2) AS median_zscore, round(g.max_z, 2) AS max_zscore, round(g.min_zscore, 2) AS min_zscore, round(g.z_range, 2) AS zscore_range FROM subtype_medians AS s INNER JOIN gene_range AS g ON s.hugo_gene_symbol = g.hugo_gene_symbol ORDER BY g.z_range DESC, s.hugo_gene_symbol ASC LIMIT 200. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_run_select_query
{
 "query": "
WITH subtype_expr AS (
  SELECT 
    g.hugo_gene_symbol,
    c.attribute_value AS subtype,
    toFloat64OrNull(g.alteration_value) AS expr_value
  FROM genetic_alteration_derived g
  INNER JOIN clinical_data_derived c USING (sample_unique_id)
  WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND c.attribute_name = 'SUBTYPE'
    AND c.attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
),
subtype_medians AS (
  SELECT 
    hugo_gene_symbol,
    subtype,
    median(expr_value) AS median_z
  FROM subtype_expr
  WHERE expr_value IS NOT NULL
  GROUP BY hugo_gene_symbol, subtype
),
gene_range AS (
  SELECT 
    hugo_gene_symbol,
    max(median_z) AS max_z,
    min(median_z) AS min_z,
    max(median_z) - min(median_z) AS z_range
  FROM subtype_medians
  GROUP BY hugo_gene_symbol
  HAVING count(DISTINCT subtype) = 3 AND max(median_z) - min(median_z) >= 0.8
  ORDER BY z_range DESC
  LIMIT 50
)
SELECT 
  s.hugo_gene_symbol,
  s.subtype,
  round(s.median_z, 2) AS median_zscore,
  round(gr.max_z, 2) AS max_zscore,
  round(gr.min_z, 2) AS min_zscore,
  round(gr.z_range, 2) AS zscore_range
FROM subtype_medians s
JOIN gene_range gr ON s.hugo_gene_symbol = gr.hugo_gene_symbol
ORDER BY gr.z_range DESC, s.hugo_gene_symbol
LIMIT 200
",
 "max_rows": 200
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT COUNT(*) as total_records
FROM genetic_alteration_derived
WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND profile_type = 'rna_seq_v2_mrna_median_Zscores'
"
}

◀ result
{"rows":[{"total_records":10204442}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH subtype_expr AS (
  SELECT 
    g.hugo_gene_symbol,
    c.attribute_value AS subtype,
    toFloat64OrNull(g.alteration_value) AS expr_value
  FROM genetic_alteration_derived g
  INNER JOIN clinical_data_derived c USING (sample_unique_id)
  WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND c.attribute_name = 'SUBTYPE'
    AND c.attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
    AND expr_value IS NOT NULL
)
SELECT 
  hugo_gene_symbol,
  subtype,
  count() AS n_samples,
  round(median(expr_value), 2) AS median_z
FROM subtype_expr
GROUP BY hugo_gene_symbol, subtype
LIMIT 100
",
 "max_rows": 100
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT 
  g.hugo_gene_symbol,
  c.attribute_value AS subtype,
  count() AS n_samples,
  round(median(toFloat64OrNull(g.alteration_value)), 2) AS median_z
FROM genetic_alteration_derived g
LEFT JOIN clinical_data_derived c ON g.sample_unique_id = c.sample_unique_id 
  AND c.attribute_name = 'SUBTYPE'
WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
  AND c.attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
  AND g.alteration_value NOT IN ('', 'NA')
GROUP BY g.hugo_gene_symbol, c.attribute_value
LIMIT 100
",
 "max_rows": 100
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT 
  c.attribute_value AS subtype,
  count(*) AS n_expr_samples
FROM genetic_alteration_derived g
INNER JOIN clinical_data_derived c ON g.sample_unique_id = c.sample_unique_id
WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
  AND c.attribute_name = 'SUBTYPE'
GROUP BY c.attribute_value
"
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT cancer_study_identifier
FROM clinical_data_derived
WHERE attribute_name = 'SUBTYPE'
  AND attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
"
}

◀ result
{"rows":[{"cancer_study_identifier":"lgg_tcga_pan_can_atlas_2018"}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT 
  g.hugo_gene_symbol,
  c.attribute_value AS subtype,
  count(*) AS n_samples,
  round(median(toFloat64OrNull(g.alteration_value)), 2) AS median_z
FROM genetic_alteration_derived g
INNER JOIN clinical_data_derived c 
  ON g.sample_unique_id = c.sample_unique_id 
  AND g.cancer_study_identifier = c.cancer_study_identifier
WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
  AND c.attribute_name = 'SUBTYPE'
  AND c.attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
  AND g.alteration_value NOT IN ('', 'NA')
  AND toFloat64OrNull(g.alteration_value) IS NOT NULL
GROUP BY g.hugo_gene_symbol, c.attribute_value
ORDER BY g.hugo_gene_symbol, subtype
LIMIT 200
",
 "max_rows": 200
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT COUNT(DISTINCT g.sample_unique_id) AS expr_samples,
       COUNT(DISTINCT c.sample_unique_id) AS clinical_samples
FROM genetic_alteration_derived g
INNER JOIN clinical_data_derived c 
  ON g.sample_unique_id = c.sample_unique_id 
  AND g.cancer_study_identifier = c.cancer_study_identifier
WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
  AND c.attribute_name = 'SUBTYPE'
"
}

◀ result
{"rows":[{"expr_samples":0,"clinical_samples":0}]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT substring(g.sample_unique_id, 1, 50) AS expr_sample_id,
       substring(c.sample_unique_id, 1, 50) AS clinical_sample_id
FROM genetic_alteration_derived g
CROSS JOIN clinical_data_derived c
WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
  AND c.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND c.attribute_name = 'SUBTYPE'
LIMIT 10
"
}

◀ result
{"rows":[{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-4938-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-4941-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-4942-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-4943-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-4944-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-5390-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-5393-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-5394-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-5395-01"},{"expr_sample_id":"lgg_tcga_pan_can_atlas_2018_TCGA-CS-5396-01"}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH expr_data AS (
  SELECT 
    hugo_gene_symbol,
    substring(sample_unique_id, position('_' in sample_unique_id) + 1) AS sample_id,
    toFloat64OrNull(alteration_value) AS expr_value
  FROM genetic_alteration_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND alteration_value NOT IN ('', 'NA')
),
clinical_subtype AS (
  SELECT 
    substring(sample_unique_id, position('_' in sample_unique_id) + 1) AS sample_id,
    attribute_value AS subtype
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND attribute_name = 'SUBTYPE'
    AND attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
)
SELECT 
  e.hugo_gene_symbol,
  cs.subtype,
  count(*) AS n_samples,
  round(median(e.expr_value), 2) AS median_z
FROM expr_data e
JOIN clinical_subtype cs ON e.sample_id = cs.sample_id
WHERE e.expr_value IS NOT NULL
GROUP BY e.hugo_gene_symbol, cs.subtype
ORDER BY e.hugo_gene_symbol, cs.subtype
LIMIT 200
",
 "max_rows": 200
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
SELECT DISTINCT sample_unique_id
FROM clinical_data_derived
WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND attribute_name = 'SUBTYPE'
LIMIT 5
"
}

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

▶ 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, type
FROM clinical_data_derived
WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND attribute_name = 'SUBTYPE'
"
}

◀ result
{"rows":[{"attribute_name":"SUBTYPE","type":"patient"}]}

▶ clickhouse_run_select_query
{
 "query": "
WITH patient_subtypes AS (
  SELECT 
    patient_unique_id,
    attribute_value AS subtype
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND attribute_name = 'SUBTYPE'
    AND attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
),
sample_to_patient AS (
  SELECT DISTINCT 
    sample_unique_id,
    substring(patient_unique_id, position('_' in patient_unique_id) + 1) AS patient_stable_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND patient_unique_id != ''
)
SELECT 
  g.hugo_gene_symbol,
  ps.subtype,
  count(*) AS n_samples,
  round(median(toFloat64OrNull(g.alteration_value)), 2) AS median_z
FROM genetic_alteration_derived g
JOIN sample_to_patient sp ON g.sample_unique_id = sp.sample_unique_id
JOIN patient_subtypes ps ON substring(sp.patient_stable_id, 1, 20) IN (
  SELECT substring(patient_unique_id, position('_' in patient_unique_id) + 1) FROM patient_subtypes
)
WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
  AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
  AND g.alteration_value NOT IN ('', 'NA')
GROUP BY g.hugo_gene_symbol, ps.subtype
LIMIT 100
",
 "max_rows": 100
}

◀ result
{"rows":[]}

▶ clickhouse_run_select_query
{
 "query": "
WITH expr_per_sample AS (
  SELECT 
    g.sample_unique_id,
    g.hugo_gene_symbol,
    toFloat64OrNull(g.alteration_value) AS expr_value
  FROM genetic_alteration_derived g
  WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND g.alteration_value NOT IN ('', 'NA')
),
sample_meta AS (
  SELECT DISTINCT
    sample_unique_id,
    patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
),
patient_subtype AS (
  SELECT 
    patient_unique_id,
    attribute_value AS subtype
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND attribute_name = 'SUBTYPE'
    AND attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
)
SELECT 
  e.hugo_gene_symbol,
  ps.subtype,
  count(*) AS n_samples,
  round(median(e.expr_value), 2) AS median_z
FROM expr_per_sample e
JOIN sample_meta sm ON e.sample_unique_id = sm.sample_unique_id
JOIN patient_subtype ps ON sm.patient_unique_id = ps.patient_unique_id
WHERE e.expr_value IS NOT NULL
GROUP BY e.hugo_gene_symbol, ps.subtype
ORDER BY e.hugo_gene_symbol, ps.subtype
LIMIT 200
",
 "max_rows": 200
}

◀ result
{"rows":[{"hugo_gene_symbol":"A1BG","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.94},{"hugo_gene_symbol":"A1BG","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.44},{"hugo_gene_symbol":"A1BG","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.15},{"hugo_gene_symbol":"A1BG-AS1","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-1.14},{"hugo_gene_symbol":"A1BG-AS1","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.31},{"hugo_gene_symbol":"A1BG-AS1","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.19},{"hugo_gene_symbol":"A1CF","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.37},{"hugo_gene_symbol":"A1CF","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.37},{"hugo_gene_symbol":"A1CF","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.37},{"hugo_gene_symbol":"A2M","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.51},{"hugo_gene_symbol":"A2M","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":0.02},{"hugo_gene_symbol":"A2M","subtype":"LGG_IDHwt","n_samples":92,"median_z":0.17},{"hugo_gene_symbol":"A2M-AS1","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.34},{"hugo_gene_symbol":"A2M-AS1","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.3},{"hugo_gene_symbol":"A2M-AS1","subtype":"LGG_IDHwt","n_samples":92,"median_z":0.23},{"hugo_gene_symbol":"A2ML1","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.28},{"hugo_gene_symbol":"A2ML1","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.2},{"hugo_gene_symbol":"A2ML1","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.06},{"hugo_gene_symbol":"A4GALT","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.37},{"hugo_gene_symbol":"A4GALT","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.33},{"hugo_gene_symbol":"A4GALT","subtype":"LGG_IDHwt","n_samples":92,"median_z":0.03},{"hugo_gene_symbol":"A4GNT","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.17},{"hugo_gene_symbol":"A4GNT","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.29},{"hugo_gene_symbol":"A4GNT","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.02},{"hugo_gene_symbol":"AAAS","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":0.26},{"hugo_gene_symbol":"AAAS","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.23},{"hugo_gene_symbol":"AAAS","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.44},{"hugo_gene_symbol":"AACS","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.18},{"hugo_gene_symbol":"AACS","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.43},{"hugo_gene_symbol":"AACS","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.28},{"hugo_gene_symbol":"AACSP1","subtype":"LGG_IDHmut-codel","n_samples":167,"median_z":-0.21},{"hugo_gene_symbol":"AACSP1","subtype":"LGG_IDHmut-non-codel","n_samples":248,"median_z":-0.28},{"hugo_gene_symbol":"AACSP1","subtype":"LGG_IDHwt","n_samples":92,"median_z":-0.43},{"hugo_gene_symbol":"AADAC","subtype … (14937 more chars)

▶ clickhouse_run_select_query
{
 "query": "
WITH expr_per_sample AS (
  SELECT 
    g.sample_unique_id,
    g.hugo_gene_symbol,
    toFloat64OrNull(g.alteration_value) AS expr_value
  FROM genetic_alteration_derived g
  WHERE g.cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND g.profile_type = 'rna_seq_v2_mrna_median_Zscores'
    AND g.alteration_value NOT IN ('', 'NA')
),
sample_meta AS (
  SELECT DISTINCT
    sample_unique_id,
    patient_unique_id
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
),
patient_subtype AS (
  SELECT 
    patient_unique_id,
    attribute_value AS subtype
  FROM clinical_data_derived
  WHERE cancer_study_identifier = 'lgg_tcga_pan_can_atlas_2018'
    AND attribute_name = 'SUBTYPE'
    AND attribute_value IN ('LGG_IDHmut-codel', 'LGG_IDHmut-non-codel', 'LGG_IDHwt')
),
subtype_expr AS (
  SELECT 
    e.hugo_gene_symbol,
    ps.subtype,
    median(e.expr_value) AS median_z
  FROM expr_per_sample e
  JOIN sample_meta sm ON e.sample_unique_id = sm.sample_unique_id
  JOIN patient_subtype ps ON sm.patient_unique_id = ps.patient_unique_id
  WHERE e.expr_value IS NOT NULL
  GROUP BY e.hugo_gene_symbol, ps.subtype
),
gene_stats AS (
  SELECT 
    hugo_gene_symbol,
    max(median_z) AS max_median_z,
    min(median_z) AS min_median_z,
    max(median_z) - min(median_z) AS range_z
  FROM subtype_expr
  GROUP BY hugo_gene_symbol
  HAVING count() = 3 AND max(median_z) >= 0.8
  ORDER BY max_median_z DESC
  LIMIT 50
)
SELECT 
  se.hugo_gene_symbol,
  se.subtype,
  round(se.median_z, 2) AS median_zscore,
  CASE 
    WHEN se.median_z >= 0.8 THEN 'STRONGLY OVEREXPRESSED'
    WHEN se.median_z >= 0.5 THEN 'MODERATELY OVEREXPRESSED'
    WHEN se.median_z >= 0.3 THEN 'MILDLY OVEREXPRESSED'
    ELSE 'NOT OVEREXPRESSED'
  END AS expression_level
FROM subtype_expr se
JOIN gene_stats gs ON se.hugo_gene_symbol = gs.hugo_gene_symbol
ORDER BY gs.max_median_z DESC, se.hugo_gene_symbol, 
  CASE WHEN se.subtype = 'LGG_IDHmut-codel' THEN 1 
       WHEN se.subtype = 'LGG_IDHmut-non-codel' THEN 2 
       ELSE 3 END
",
 "max_rows": 300
}

◀ result
{"rows":[{"hugo_gene_symbol":"MEOX2","subtype":"LGG_IDHmut-codel","median_zscore":-0.16,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"MEOX2","subtype":"LGG_IDHmut-non-codel","median_zscore":-0.19,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"MEOX2","subtype":"LGG_IDHwt","median_zscore":4.5,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"TMEM248","subtype":"LGG_IDHmut-codel","median_zscore":-0.58,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"TMEM248","subtype":"LGG_IDHmut-non-codel","median_zscore":0.28,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"TMEM248","subtype":"LGG_IDHwt","median_zscore":3.98,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"FKBP9P1","subtype":"LGG_IDHmut-codel","median_zscore":-0.19,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"FKBP9P1","subtype":"LGG_IDHmut-non-codel","median_zscore":-0.28,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"FKBP9P1","subtype":"LGG_IDHwt","median_zscore":3.53,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"MRM2","subtype":"LGG_IDHmut-codel","median_zscore":-0.34,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"MRM2","subtype":"LGG_IDHmut-non-codel","median_zscore":0.18,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"MRM2","subtype":"LGG_IDHwt","median_zscore":3.49,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"FKBP9","subtype":"LGG_IDHmut-codel","median_zscore":-0.43,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"FKBP9","subtype":"LGG_IDHmut-non-codel","median_zscore":-0.21,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"FKBP9","subtype":"LGG_IDHwt","median_zscore":3.32,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"PSMC2","subtype":"LGG_IDHmut-codel","median_zscore":-0.51,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"PSMC2","subtype":"LGG_IDHmut-non-codel","median_zscore":0.46,"expression_level":"MILDLY OVEREXPRESSED"},{"hugo_gene_symbol":"PSMC2","subtype":"LGG_IDHwt","median_zscore":3.16,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"DENND2A","subtype":"LGG_IDHmut-codel","median_zscore":0.03,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"DENND2A","subtype":"LGG_IDHmut-non-codel","median_zscore":-0.07,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"DENND2A","subtype":"LGG_IDHwt","median_zscore":3.15,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"GSTK1","subtype":"LGG_IDHmut-codel","median_zscore":-0.24,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"GSTK1","subtype":"LGG_IDHmut-non-codel","median_zscore":0.06,"expression_level":"NOT OVEREXPRESSED"},{"hugo_gene_symbol":"GSTK1","subtype":"LGG_IDHwt","median_zscore":3.14,"expression_level":"STRONGLY OVEREXPRESSED"},{"hugo_gene_symbol":"PDIA4","subtype":"LGG_IDHmut-codel","median_zscore":-0.15,"expression_level":"NOT OVEREXPRESSE … (14938 more chars)

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

◀ result
{"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"lgg_tcga_pan_can_atlas_2018","name":"Brain Lower Grade Glioma (TCGA, PanCancer Atlas)","sampleCount":514,"studyViewUrl":"https://www.cbioportal.org/study?id=lgg_tcga_pan_can_atlas_2018","metadata":{"clinicalAttributeIds":["AGE","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","ANEUPLOIDY_SCORE","BUFFA_HYPOXIA_SCORE","CANCER_TYPE","CANCER_TYPE_ACRONYM","CANCER_TYPE_DETAILED","DAYS_LAST_FOLLOWUP","DAYS_TO_BIRTH","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DFS_MONTHS","DFS_STATUS","DSS_MONTHS","DSS_STATUS","ETHNICITY","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","GENETIC_ANCESTRY_LABEL","GRADE","HISTORY_NEOADJUVANT_TRTYN","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","INFORMED_CONSENT_VERIFIED","IN_PANCANPATHWAYS_FREEZE","MSI_SCORE_MANTIS","MSI_SENSOR_SCORE","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_PATIENT_ID","PATH_M_STAGE","PATH_N_STAGE","PATH_T_STAGE","PERSON_NEOPLASM_CANCER_STATUS","PFS_MONTHS","PFS_STATUS","PRIMARY_LYMPH_NODE_PRESENTATION_ASSESSMENT","PRIOR_DX","RACE","RADIATION_THERAPY","RAGNUM_HYPOXIA_SCORE","SAMPLE_COUNT","SAMPLE_TYPE","SEX","SOMATIC_STATUS","SUBTYPE","TBL_SCORE","TISSUE_PROSPECTIVE_COLLECTION_INDICATOR","TISSUE_RETROSPECTIVE_COLLECTION_INDICATOR","TISSUE_SOURCE_SITE","TISSUE_SOURCE_SITE_CODE","TMB_NONSYNONYMOUS","TUMOR_TISSUE_SITE","TUMOR_TYPE","WEIGHT","WINTER_HYPOXIA_SCORE"],"molecularProfileIds":["lgg_tcga_pan_can_atlas_2018_gistic","lgg_tcga_pan_can_atlas_2018_log2CNA","lgg_tcga_pan_can_atlas_2018_mutations","lgg_tcga_pan_can_atlas_2018_rna_seq_v2_mrna","lgg_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","lgg_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","lgg_tcga_pan_can_atlas_2018_rppa","lgg_tcga_pan_can_atlas_2018_rppa_Zscores","lgg_tcga_pan_can_atlas_2018_structural_variants"],"genericAssayProfiles":["lgg_tcga_pan_can_atlas_2018_armlevel_cna","lgg_tcga_pan_can_atlas_2018_genetic_ancestry","lgg_tcga_pan_can_atlas_2018_methylation_hm27_hm450_merge","lgg_tcga_pan_can_atlas_2018_methylation_hm450"],"heatmapProfileIds":["lgg_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_Zscores","lgg_tcga_pan_can_atlas_2018_rna_seq_v2_mrna_median_all_sample_Zscores","lgg_tcga_pan_can_atlas_2018_rppa_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna","protein","generic_assay_armlevel_cna","generic_assay_genetic_ancestry","generic_assay_methylation"]}}]}}

═ answer (success)
## Summary

Yes, there are many genes with subtype-specific overexpression in lower-grade glioma. The most dramatic pattern is **overexpression in the IDH-wildtype (LGG_IDHwt) subtype**, which represents the most aggressive molecular subtype.

### Key Findings:

**Highly overexpressed in LGG_IDHwt (median Z-scores ≥ 1.8):**
- **MEOX2** (Z = 4.50) — developmental homeobox gene
- **TMEM248** (Z = 3.98) — membrane protein
- **FKBP9** (Z = 3.32) — immunophilin/chaperone
- **PDGFA** (Z = 2.10) — growth factor
- **PTN** (Z = 1.87) — pleiotrophin (pro-angiogenic)
- **AQP1** (Z = 1.95) — aquaporin water channel
- **SMO** (Z = 2.67) — Hedgehog pathway component
- **FZD1** (Z = 3.03) — Wnt pathway receptor

**Specifically overexpressed in IDH-mutant-codel subtype:**
- **TNNT1** (Z = 1.92 in codel; Z = -0.53 in wildtype) — troponin, muscle-related

### Pattern:
The LGG_IDHwt subtype exhibits a distinctive transcriptional signature with broad overexpression of genes involved in:
- Cell proliferation and growth signaling
- Angiogenesis (PDGFA, PTN, FZD1)
- Protein trafficking and stress response (FKBP9, chaperones)
- Developmental pathways (MEOX2, HOXA1)

This expression profile aligns with the known aggressive biology of IDH-wildtype gliomas, which have poor prognosis compared to the IDH-mutant subtypes.

### Explore the Data:
[View Brain Lower Grade Glioma Study](https://www.cbioportal.org/study?id=lgg_tcga_pan_can_atlas_2018)

You can filter by SUBTYPE in the clinical data tab and use the Plots feature to visualize gene expression distributions across the three molecular subtypes.