Live ClickHouse latency check — 2026-09-29

SHOW TABLES

DESCRIBE TABLE cancer_study

DESCRIBE TABLE genomic_event_derived

DESCRIBE TABLE sample_to_gene_panel_derived

DESCRIBE TABLE clinical_data_derived

DESCRIBE TABLE sample_derived

DESCRIBE TABLE gene

DESCRIBE TABLE genetic_profile

SELECT version(), timezone()

SELECT name, value FROM system.settings LIMIT 1000

SELECT name, total_rows, total_bytes FROM system.tables WHERE database=currentDatabase() ORDER BY name

SELECT hugo_gene_symbol, entrez_gene_id FROM gene WHERE hugo_gene_symbol='TP53'

SELECT cancer_study_identifier FROM cancer_study WHERE cancer_study_identifier='msk_chord_2024'

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='TP53', alteration='mutation')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'TP53'
      AND ged.off_panel = 0
      AND (
        ('mutation' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('mutation' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('mutation' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('mutation' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'TP53' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'mutation' = 'mutation',           'MUTATION_EXTENDED',
          'mutation' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'mutation' = 'mutation',           'MUTATION_EXTENDED',
          'mutation' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='TP53', alteration='amplification')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'TP53'
      AND ged.off_panel = 0
      AND (
        ('amplification' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('amplification' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('amplification' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('amplification' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'TP53' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'amplification' = 'mutation',           'MUTATION_EXTENDED',
          'amplification' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'amplification' = 'mutation',           'MUTATION_EXTENDED',
          'amplification' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='TP53', alteration='deep_deletion')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'TP53'
      AND ged.off_panel = 0
      AND (
        ('deep_deletion' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('deep_deletion' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('deep_deletion' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('deep_deletion' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'TP53' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'deep_deletion' = 'mutation',           'MUTATION_EXTENDED',
          'deep_deletion' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'deep_deletion' = 'mutation',           'MUTATION_EXTENDED',
          'deep_deletion' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='TP53', alteration='structural_variant')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'TP53'
      AND ged.off_panel = 0
      AND (
        ('structural_variant' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('structural_variant' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('structural_variant' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('structural_variant' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'TP53' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'structural_variant' = 'mutation',           'MUTATION_EXTENDED',
          'structural_variant' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'structural_variant' = 'mutation',           'MUTATION_EXTENDED',
          'structural_variant' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='ERBB2', alteration='mutation')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'ERBB2'
      AND ged.off_panel = 0
      AND (
        ('mutation' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('mutation' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('mutation' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('mutation' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'ERBB2' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'mutation' = 'mutation',           'MUTATION_EXTENDED',
          'mutation' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'mutation' = 'mutation',           'MUTATION_EXTENDED',
          'mutation' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'mutation' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='ERBB2', alteration='amplification')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'ERBB2'
      AND ged.off_panel = 0
      AND (
        ('amplification' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('amplification' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('amplification' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('amplification' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'ERBB2' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'amplification' = 'mutation',           'MUTATION_EXTENDED',
          'amplification' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'amplification' = 'mutation',           'MUTATION_EXTENDED',
          'amplification' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'amplification' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='ERBB2', alteration='deep_deletion')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'ERBB2'
      AND ged.off_panel = 0
      AND (
        ('deep_deletion' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('deep_deletion' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('deep_deletion' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('deep_deletion' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'ERBB2' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'deep_deletion' = 'mutation',           'MUTATION_EXTENDED',
          'deep_deletion' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'deep_deletion' = 'mutation',           'MUTATION_EXTENDED',
          'deep_deletion' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'deep_deletion' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

SELECT * FROM gene_alteration_frequency_by_cancer_type(preference='all_studies_non_redundant', gene='ERBB2', alteration='structural_variant')

WITH cohort AS (
    SELECT cancer_study_identifier
    FROM cancer_study_query_preferences
    WHERE preference_name = 'all_studies_non_redundant'
),
sample_cancer_type AS (
    SELECT cd.sample_unique_id, cd.attribute_value AS cancer_type
    FROM clinical_data_derived cd
    JOIN cohort c USING (cancer_study_identifier)
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
altered AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT ged.sample_unique_id) AS altered_samples
    FROM genomic_event_derived ged
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    WHERE ged.hugo_gene_symbol = 'ERBB2'
      AND ged.off_panel = 0
      AND (
        ('structural_variant' = 'mutation'
            AND ged.variant_type = 'mutation'
            AND ged.mutation_status != 'UNCALLED')
        OR ('structural_variant' = 'amplification'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = 2)
        OR ('structural_variant' = 'deep_deletion'
            AND ged.variant_type = 'cna'
            AND ged.cna_alteration = -2)
        OR ('structural_variant' = 'structural_variant'
            AND ged.variant_type = 'structural_variant')
      )
    GROUP BY sct.cancer_type
),
profiled_samples_for_gene AS (
    -- Map the user-facing alteration token to the alteration_type stored
    -- on sample_to_gene_panel_derived. Same gene-in-panel-or-WES branch
    -- as the mutation view, but with the matching alteration_type filter.
    SELECT stgp.sample_unique_id, stgp.cancer_study_identifier
    FROM sample_to_gene_panel_derived stgp
    JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    -- IN-subquery instead of JOIN gene: resolves the symbol to its entrez
    -- id(s) once, rather than joining every panel row against the gene
    -- table before filtering. COUNT(DISTINCT) downstream makes the result
    -- identical even for symbols with several (or duplicated) gene rows.
    -- IS NOT NULL keeps NULL keys unmatched (as the equality JOIN did) even
    -- under transform_null_in=1.
    WHERE gpl.gene_id IN (
        SELECT entrez_gene_id FROM gene
        WHERE hugo_gene_symbol = 'ERBB2' AND entrez_gene_id IS NOT NULL)
      AND stgp.alteration_type = multiIf(
          'structural_variant' = 'mutation',           'MUTATION_EXTENDED',
          'structural_variant' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
    UNION ALL
    SELECT sample_unique_id, cancer_study_identifier
    FROM sample_to_gene_panel_derived
    WHERE gene_panel_id = 'WES'
      AND alteration_type = multiIf(
          'structural_variant' = 'mutation',           'MUTATION_EXTENDED',
          'structural_variant' = 'amplification',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'deep_deletion',      'COPY_NUMBER_ALTERATION',
          'structural_variant' = 'structural_variant', 'STRUCTURAL_VARIANT',
          '')
),
profiled AS (
    SELECT sct.cancer_type,
           COUNT(DISTINCT p.sample_unique_id) AS profiled_samples
    FROM profiled_samples_for_gene p
    JOIN cohort c USING (cancer_study_identifier)
    JOIN sample_cancer_type sct USING (sample_unique_id)
    GROUP BY sct.cancer_type
)
SELECT a.cancer_type,
       a.altered_samples,
       p.profiled_samples,
       ROUND(a.altered_samples * 100.0 / NULLIF(p.profiled_samples, 0), 1) AS frequency_pct
FROM altered a
JOIN profiled p USING (cancer_type)
WHERE p.profiled_samples >= 50

WITH
        events AS (
            SELECT sample_unique_id,
                   (variant_type = 'mutation' AND mutation_status != 'UNCALLED') AS is_mutation,
                   (variant_type = 'cna' AND cna_alteration = 2) AS is_amplification,
                   (variant_type = 'cna' AND cna_alteration = -2) AS is_deep_deletion,
                   (variant_type = 'structural_variant' AND mutation_status != 'UNCALLED') AS is_structural_variant
            FROM genomic_event_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
              AND hugo_gene_symbol IN ('TP53')
              AND off_panel = 0
        ),
        profiled AS (
            SELECT stgp.sample_unique_id AS sample_unique_id,
                   stgp.alteration_type AS alteration_type
            FROM sample_to_gene_panel_derived stgp
            JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
            JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
            JOIN gene ge ON gpl.gene_id = ge.entrez_gene_id
            WHERE stgp.cancer_study_identifier IN ('msk_chord_2024')
              AND ge.hugo_gene_symbol IN ('TP53')
              AND (stgp.alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT') OR (stgp.alteration_type = 'COPY_NUMBER_ALTERATION' AND stgp.genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
            UNION ALL
            SELECT sample_unique_id, alteration_type
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
              AND gene_panel_id = 'WES'
              AND (alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT') OR (alteration_type = 'COPY_NUMBER_ALTERATION' AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
        )
        SELECT *
        FROM (
            SELECT groupUniqArray(hugo_gene_symbol) AS matched_genes
            FROM gene WHERE hugo_gene_symbol IN ('TP53')
        ) AS gene_match
        CROSS JOIN (
            SELECT groupUniqArray(cancer_study_identifier) AS matched_studies
            FROM cancer_study WHERE cancer_study_identifier IN ('msk_chord_2024')
        ) AS study_match
        CROSS JOIN (
            SELECT uniqExactIf(sample_unique_id, is_mutation) AS altered_mutation,
                   uniqExactIf(sample_unique_id, is_amplification) AS altered_amplification,
                   uniqExactIf(sample_unique_id, is_deep_deletion) AS altered_deep_deletion,
                   uniqExactIf(sample_unique_id, is_structural_variant)
                       AS altered_structural_variant,
                   uniqExactIf(sample_unique_id, is_mutation OR is_amplification
                       OR is_deep_deletion OR is_structural_variant) AS altered_any
            FROM events
        ) AS altered
        CROSS JOIN (
            SELECT uniqExactIf(sample_unique_id, alteration_type = 'MUTATION_EXTENDED')
                       AS profiled_MUTATION_EXTENDED,
                   uniqExactIf(sample_unique_id, alteration_type = 'COPY_NUMBER_ALTERATION')
                       AS profiled_COPY_NUMBER_ALTERATION,
                   uniqExactIf(sample_unique_id, alteration_type = 'STRUCTURAL_VARIANT')
                       AS profiled_STRUCTURAL_VARIANT,
                   uniqExact(sample_unique_id) AS profiled_ANY
            FROM profiled
        ) AS profiled_counts

WITH
        altered AS (
            SELECT hugo_gene_symbol, COUNT(DISTINCT sample_unique_id) AS altered_samples
            FROM genomic_event_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
              AND off_panel = 0
              AND (variant_type = 'mutation' AND mutation_status != 'UNCALLED')
            GROUP BY hugo_gene_symbol
            ORDER BY altered_samples DESC, hugo_gene_symbol ASC
            LIMIT 20
        ),
        wes_samples AS (
            SELECT DISTINCT sample_unique_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
              AND gene_panel_id = 'WES'
              AND (alteration_type IN ('MUTATION_EXTENDED'))
        ),
        panel_profiled AS (
            SELECT ge.hugo_gene_symbol AS hugo_gene_symbol,
                   COUNT(DISTINCT stgp.sample_unique_id) AS n
            FROM sample_to_gene_panel_derived stgp
            JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
            JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
            JOIN gene ge ON gpl.gene_id = ge.entrez_gene_id
            WHERE stgp.cancer_study_identifier IN ('msk_chord_2024')
              AND (stgp.alteration_type IN ('MUTATION_EXTENDED'))
              AND stgp.sample_unique_id NOT IN (SELECT sample_unique_id FROM wes_samples)
              AND ge.hugo_gene_symbol IN (SELECT hugo_gene_symbol FROM altered)
            GROUP BY ge.hugo_gene_symbol
        )
        SELECT a.hugo_gene_symbol AS hugo_gene_symbol,
               a.altered_samples AS altered_samples,
               (SELECT count() FROM wes_samples) + COALESCE(p.n, 0) AS profiled_samples
        FROM altered a
        LEFT JOIN panel_profiled p ON a.hugo_gene_symbol = p.hugo_gene_symbol
        ORDER BY altered_samples DESC, hugo_gene_symbol ASC

WITH
        profiled AS (
            SELECT cancer_study_identifier, alteration_type AS profile_type,
                   sample_unique_id, gene_panel_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
            UNION ALL
            SELECT cancer_study_identifier, 'COPY_NUMBER_ALTERATION_DISCRETE' AS profile_type,
                   sample_unique_id, gene_panel_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
              AND ((alteration_type = 'COPY_NUMBER_ALTERATION' AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
            UNION ALL
            SELECT cancer_study_identifier, 'ANY_MUT_CNA_SV' AS profile_type,
                   sample_unique_id, gene_panel_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
              AND (alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT') OR (alteration_type = 'COPY_NUMBER_ALTERATION' AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
        ),
        sample_patient AS (
            SELECT sample_unique_id, patient_unique_id
            FROM sample_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
        )
        SELECT * FROM (
            SELECT cancer_study_identifier, 'ALL_SAMPLES' AS profile_type,
                   COUNT(DISTINCT sample_unique_id) AS samples,
                   COUNT(DISTINCT patient_unique_id) AS patients,
                   toUInt64(0) AS wes_samples
            FROM sample_derived
            WHERE cancer_study_identifier IN ('msk_chord_2024')
            GROUP BY cancer_study_identifier
            UNION ALL
            SELECT p.cancer_study_identifier, p.profile_type,
                   COUNT(DISTINCT p.sample_unique_id),
                   COUNT(DISTINCT nullIf(sp.patient_unique_id, '')),
                   COUNT(DISTINCT if(p.gene_panel_id = 'WES', p.sample_unique_id, NULL))
            FROM profiled p
            LEFT JOIN sample_patient sp ON p.sample_unique_id = sp.sample_unique_id
            GROUP BY p.cancer_study_identifier, p.profile_type
        )
        ORDER BY cancer_study_identifier, profile_type = 'ALL_SAMPLES' DESC,
                 profile_type = 'ANY_MUT_CNA_SV' DESC, samples DESC, profile_type

WITH
        events AS (
            SELECT sample_unique_id,
                   (variant_type = 'mutation' AND mutation_status != 'UNCALLED') AS is_mutation,
                   (variant_type = 'cna' AND cna_alteration = 2) AS is_amplification,
                   (variant_type = 'cna' AND cna_alteration = -2) AS is_deep_deletion,
                   (variant_type = 'structural_variant' AND mutation_status != 'UNCALLED') AS is_structural_variant
            FROM genomic_event_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND hugo_gene_symbol IN ('TP53')
              AND off_panel = 0
        ),
        profiled AS (
            SELECT stgp.sample_unique_id AS sample_unique_id,
                   stgp.alteration_type AS alteration_type
            FROM sample_to_gene_panel_derived stgp
            JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
            JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
            JOIN gene ge ON gpl.gene_id = ge.entrez_gene_id
            WHERE stgp.cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND ge.hugo_gene_symbol IN ('TP53')
              AND (stgp.alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT') OR (stgp.alteration_type = 'COPY_NUMBER_ALTERATION' AND stgp.genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
            UNION ALL
            SELECT sample_unique_id, alteration_type
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND gene_panel_id = 'WES'
              AND (alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT') OR (alteration_type = 'COPY_NUMBER_ALTERATION' AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
        )
        SELECT *
        FROM (
            SELECT groupUniqArray(hugo_gene_symbol) AS matched_genes
            FROM gene WHERE hugo_gene_symbol IN ('TP53')
        ) AS gene_match
        CROSS JOIN (
            SELECT groupUniqArray(cancer_study_identifier) AS matched_studies
            FROM cancer_study WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
        ) AS study_match
        CROSS JOIN (
            SELECT uniqExactIf(sample_unique_id, is_mutation) AS altered_mutation,
                   uniqExactIf(sample_unique_id, is_amplification) AS altered_amplification,
                   uniqExactIf(sample_unique_id, is_deep_deletion) AS altered_deep_deletion,
                   uniqExactIf(sample_unique_id, is_structural_variant)
                       AS altered_structural_variant,
                   uniqExactIf(sample_unique_id, is_mutation OR is_amplification
                       OR is_deep_deletion OR is_structural_variant) AS altered_any
            FROM events
        ) AS altered
        CROSS JOIN (
            SELECT uniqExactIf(sample_unique_id, alteration_type = 'MUTATION_EXTENDED')
                       AS profiled_MUTATION_EXTENDED,
                   uniqExactIf(sample_unique_id, alteration_type = 'COPY_NUMBER_ALTERATION')
                       AS profiled_COPY_NUMBER_ALTERATION,
                   uniqExactIf(sample_unique_id, alteration_type = 'STRUCTURAL_VARIANT')
                       AS profiled_STRUCTURAL_VARIANT,
                   uniqExact(sample_unique_id) AS profiled_ANY
            FROM profiled
        ) AS profiled_counts

WITH
        altered AS (
            SELECT hugo_gene_symbol, COUNT(DISTINCT sample_unique_id) AS altered_samples
            FROM genomic_event_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND off_panel = 0
              AND (variant_type = 'mutation' AND mutation_status != 'UNCALLED')
            GROUP BY hugo_gene_symbol
            ORDER BY altered_samples DESC, hugo_gene_symbol ASC
            LIMIT 20
        ),
        wes_samples AS (
            SELECT DISTINCT sample_unique_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND gene_panel_id = 'WES'
              AND (alteration_type IN ('MUTATION_EXTENDED'))
        ),
        panel_profiled AS (
            SELECT ge.hugo_gene_symbol AS hugo_gene_symbol,
                   COUNT(DISTINCT stgp.sample_unique_id) AS n
            FROM sample_to_gene_panel_derived stgp
            JOIN gene_panel gp ON stgp.gene_panel_id = gp.stable_id
            JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
            JOIN gene ge ON gpl.gene_id = ge.entrez_gene_id
            WHERE stgp.cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND (stgp.alteration_type IN ('MUTATION_EXTENDED'))
              AND stgp.sample_unique_id NOT IN (SELECT sample_unique_id FROM wes_samples)
              AND ge.hugo_gene_symbol IN (SELECT hugo_gene_symbol FROM altered)
            GROUP BY ge.hugo_gene_symbol
        )
        SELECT a.hugo_gene_symbol AS hugo_gene_symbol,
               a.altered_samples AS altered_samples,
               (SELECT count() FROM wes_samples) + COALESCE(p.n, 0) AS profiled_samples
        FROM altered a
        LEFT JOIN panel_profiled p ON a.hugo_gene_symbol = p.hugo_gene_symbol
        ORDER BY altered_samples DESC, hugo_gene_symbol ASC

WITH
        profiled AS (
            SELECT cancer_study_identifier, alteration_type AS profile_type,
                   sample_unique_id, gene_panel_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
            UNION ALL
            SELECT cancer_study_identifier, 'COPY_NUMBER_ALTERATION_DISCRETE' AS profile_type,
                   sample_unique_id, gene_panel_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND ((alteration_type = 'COPY_NUMBER_ALTERATION' AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
            UNION ALL
            SELECT cancer_study_identifier, 'ANY_MUT_CNA_SV' AS profile_type,
                   sample_unique_id, gene_panel_id
            FROM sample_to_gene_panel_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
              AND (alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT') OR (alteration_type = 'COPY_NUMBER_ALTERATION' AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE')))
        ),
        sample_patient AS (
            SELECT sample_unique_id, patient_unique_id
            FROM sample_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
        )
        SELECT * FROM (
            SELECT cancer_study_identifier, 'ALL_SAMPLES' AS profile_type,
                   COUNT(DISTINCT sample_unique_id) AS samples,
                   COUNT(DISTINCT patient_unique_id) AS patients,
                   toUInt64(0) AS wes_samples
            FROM sample_derived
            WHERE cancer_study_identifier IN ('brca_tcga_pan_can_atlas_2018')
            GROUP BY cancer_study_identifier
            UNION ALL
            SELECT p.cancer_study_identifier, p.profile_type,
                   COUNT(DISTINCT p.sample_unique_id),
                   COUNT(DISTINCT nullIf(sp.patient_unique_id, '')),
                   COUNT(DISTINCT if(p.gene_panel_id = 'WES', p.sample_unique_id, NULL))
            FROM profiled p
            LEFT JOIN sample_patient sp ON p.sample_unique_id = sp.sample_unique_id
            GROUP BY p.cancer_study_identifier, p.profile_type
        )
        ORDER BY cancer_study_identifier, profile_type = 'ALL_SAMPLES' DESC,
                 profile_type = 'ANY_MUT_CNA_SV' DESC, samples DESC, profile_type

SELECT * FROM (
            SELECT cancer_type, 'TP53' AS hugo_gene_symbol,
                   altered_samples, profiled_samples
            FROM gene_alteration_frequency_by_cancer_type(
                preference='all_studies_non_redundant', gene='TP53',
                alteration='mutation')
            )
        ORDER BY altered_samples / profiled_samples DESC, altered_samples DESC, cancer_type ASC
        LIMIT 20

SELECT * FROM (
            SELECT cancer_type, 'TP53' AS hugo_gene_symbol,
                   altered_samples, profiled_samples
            FROM gene_alteration_frequency_by_cancer_type(
                preference='pan_cancer_tcga', gene='TP53',
                alteration='mutation')
            )
        ORDER BY altered_samples / profiled_samples DESC, altered_samples DESC, cancer_type ASC
        LIMIT 20

WITH
alteration_to_profile AS (
    SELECT t.1 AS alteration_type, t.2 AS profile_type
    FROM (
        SELECT arrayJoin([
            ('mutation',           'MUTATION_EXTENDED'),
            ('amplification',      'COPY_NUMBER_ALTERATION'),
            ('deep_deletion',      'COPY_NUMBER_ALTERATION'),
            ('structural_variant', 'STRUCTURAL_VARIANT'),
            ('any',                'ANY')
        ]) AS t
    )
),
events AS (
    SELECT cancer_study_identifier,
           hugo_gene_symbol,
           sample_unique_id,
           multiIf(
               variant_type = 'mutation' AND mutation_status != 'UNCALLED', 'mutation',
               variant_type = 'cna' AND cna_alteration = 2,                  'amplification',
               variant_type = 'cna' AND cna_alteration = -2,                 'deep_deletion',
               variant_type = 'structural_variant' AND mutation_status != 'UNCALLED',
                                                                             'structural_variant',
               '') AS event_type
    FROM (SELECT * FROM genomic_event_derived WHERE cancer_study_identifier='msk_chord_2024')
    WHERE off_panel = 0
),
altered AS (
    -- event_type, not alteration_type: in ClickHouse a SELECT alias shadows
    -- the source column in WHERE, so `'any' AS alteration_type` below would
    -- turn a WHERE on alteration_type into a constant.
    SELECT cancer_study_identifier, hugo_gene_symbol, event_type AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples,
           COUNT(*) AS altered_events
    FROM events
    WHERE event_type != ''
    GROUP BY cancer_study_identifier, hugo_gene_symbol, event_type
    UNION ALL
    SELECT cancer_study_identifier, hugo_gene_symbol, 'any' AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples,
           COUNT(*) AS altered_events
    FROM events
    WHERE event_type != ''
    GROUP BY cancer_study_identifier, hugo_gene_symbol
),
typed_profile_rows AS (
    -- Same profile filters as the {mutation,cna,sv}_*_coverage views:
    -- CNA rows only from DISCRETE profiles.
    SELECT cancer_study_identifier, alteration_type AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='msk_chord_2024')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT')
       OR (alteration_type = 'COPY_NUMBER_ALTERATION'
           AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE'))
),
profile_rows AS (
    SELECT cancer_study_identifier, profile_type, sample_unique_id, gene_panel_id
    FROM typed_profile_rows
    UNION ALL
    SELECT cancer_study_identifier, 'ANY' AS profile_type, sample_unique_id, gene_panel_id
    FROM typed_profile_rows
),
sample_buckets AS (
    -- One row per sample per profile type: its panel-set signature.
    SELECT cancer_study_identifier, profile_type, sample_unique_id,
           arraySort(groupUniqArray(gene_panel_id)) AS panels
    FROM profile_rows
    GROUP BY cancer_study_identifier, profile_type, sample_unique_id
),
wes_profiled AS (
    SELECT cancer_study_identifier, profile_type, COUNT(*) AS n
    FROM sample_buckets
    WHERE has(panels, 'WES')
    GROUP BY cancer_study_identifier, profile_type
),
signature_counts AS (
    SELECT cancer_study_identifier, profile_type, panels, COUNT(*) AS n
    FROM sample_buckets
    WHERE NOT has(panels, 'WES')
    GROUP BY cancer_study_identifier, profile_type, panels
),
panel_genes AS (
    -- Same panel -> gene chain as mutation_panel_gene_coverage.
    SELECT gp.stable_id AS gene_panel_id, g.hugo_gene_symbol AS hugo_gene_symbol
    FROM gene_panel gp
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    JOIN gene g ON gpl.gene_id = g.entrez_gene_id
),
panel_profiled AS (
    SELECT cancer_study_identifier, profile_type, hugo_gene_symbol, SUM(n) AS n
    FROM (
        -- DISTINCT: a gene listed by two panels of one signature counts once.
        SELECT DISTINCT s.cancer_study_identifier, s.profile_type, s.panels, s.n, pg.hugo_gene_symbol
        FROM (
            SELECT cancer_study_identifier, profile_type, panels, n,
                   arrayJoin(panels) AS gene_panel_id
            FROM signature_counts
        ) s
        JOIN panel_genes pg ON s.gene_panel_id = pg.gene_panel_id
    )
    GROUP BY cancer_study_identifier, profile_type, hugo_gene_symbol
)
SELECT a.cancer_study_identifier,
       a.hugo_gene_symbol,
       a.alteration_type,
       a.altered_samples,
       COALESCE(w.n, 0) + COALESCE(p.n, 0) AS profiled_samples,
       a.altered_events,
       now() AS built_at
FROM altered a
JOIN alteration_to_profile m ON a.alteration_type = m.alteration_type
LEFT JOIN wes_profiled w
    ON w.cancer_study_identifier = a.cancer_study_identifier
   AND w.profile_type = m.profile_type
LEFT JOIN panel_profiled p
    ON p.cancer_study_identifier = a.cancer_study_identifier
   AND p.profile_type = m.profile_type
   AND p.hugo_gene_symbol = a.hugo_gene_symbol

WITH
alteration_to_profile AS (
    SELECT t.1 AS alteration_type, t.2 AS profile_type
    FROM (
        SELECT arrayJoin([
            ('mutation',           'MUTATION_EXTENDED'),
            ('amplification',      'COPY_NUMBER_ALTERATION'),
            ('deep_deletion',      'COPY_NUMBER_ALTERATION'),
            ('structural_variant', 'STRUCTURAL_VARIANT'),
            ('any',                'ANY')
        ]) AS t
    )
),
events AS (
    SELECT cancer_study_identifier,
           hugo_gene_symbol,
           sample_unique_id,
           multiIf(
               variant_type = 'mutation' AND mutation_status != 'UNCALLED', 'mutation',
               variant_type = 'cna' AND cna_alteration = 2,                  'amplification',
               variant_type = 'cna' AND cna_alteration = -2,                 'deep_deletion',
               variant_type = 'structural_variant' AND mutation_status != 'UNCALLED',
                                                                             'structural_variant',
               '') AS event_type
    FROM (SELECT * FROM genomic_event_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    WHERE off_panel = 0
),
altered AS (
    -- event_type, not alteration_type: in ClickHouse a SELECT alias shadows
    -- the source column in WHERE, so `'any' AS alteration_type` below would
    -- turn a WHERE on alteration_type into a constant.
    SELECT cancer_study_identifier, hugo_gene_symbol, event_type AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples,
           COUNT(*) AS altered_events
    FROM events
    WHERE event_type != ''
    GROUP BY cancer_study_identifier, hugo_gene_symbol, event_type
    UNION ALL
    SELECT cancer_study_identifier, hugo_gene_symbol, 'any' AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples,
           COUNT(*) AS altered_events
    FROM events
    WHERE event_type != ''
    GROUP BY cancer_study_identifier, hugo_gene_symbol
),
typed_profile_rows AS (
    -- Same profile filters as the {mutation,cna,sv}_*_coverage views:
    -- CNA rows only from DISCRETE profiles.
    SELECT cancer_study_identifier, alteration_type AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT')
       OR (alteration_type = 'COPY_NUMBER_ALTERATION'
           AND genetic_profile_id IN (SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE'))
),
profile_rows AS (
    SELECT cancer_study_identifier, profile_type, sample_unique_id, gene_panel_id
    FROM typed_profile_rows
    UNION ALL
    SELECT cancer_study_identifier, 'ANY' AS profile_type, sample_unique_id, gene_panel_id
    FROM typed_profile_rows
),
sample_buckets AS (
    -- One row per sample per profile type: its panel-set signature.
    SELECT cancer_study_identifier, profile_type, sample_unique_id,
           arraySort(groupUniqArray(gene_panel_id)) AS panels
    FROM profile_rows
    GROUP BY cancer_study_identifier, profile_type, sample_unique_id
),
wes_profiled AS (
    SELECT cancer_study_identifier, profile_type, COUNT(*) AS n
    FROM sample_buckets
    WHERE has(panels, 'WES')
    GROUP BY cancer_study_identifier, profile_type
),
signature_counts AS (
    SELECT cancer_study_identifier, profile_type, panels, COUNT(*) AS n
    FROM sample_buckets
    WHERE NOT has(panels, 'WES')
    GROUP BY cancer_study_identifier, profile_type, panels
),
panel_genes AS (
    -- Same panel -> gene chain as mutation_panel_gene_coverage.
    SELECT gp.stable_id AS gene_panel_id, g.hugo_gene_symbol AS hugo_gene_symbol
    FROM gene_panel gp
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    JOIN gene g ON gpl.gene_id = g.entrez_gene_id
),
panel_profiled AS (
    SELECT cancer_study_identifier, profile_type, hugo_gene_symbol, SUM(n) AS n
    FROM (
        -- DISTINCT: a gene listed by two panels of one signature counts once.
        SELECT DISTINCT s.cancer_study_identifier, s.profile_type, s.panels, s.n, pg.hugo_gene_symbol
        FROM (
            SELECT cancer_study_identifier, profile_type, panels, n,
                   arrayJoin(panels) AS gene_panel_id
            FROM signature_counts
        ) s
        JOIN panel_genes pg ON s.gene_panel_id = pg.gene_panel_id
    )
    GROUP BY cancer_study_identifier, profile_type, hugo_gene_symbol
)
SELECT a.cancer_study_identifier,
       a.hugo_gene_symbol,
       a.alteration_type,
       a.altered_samples,
       COALESCE(w.n, 0) + COALESCE(p.n, 0) AS profiled_samples,
       a.altered_events,
       now() AS built_at
FROM altered a
JOIN alteration_to_profile m ON a.alteration_type = m.alteration_type
LEFT JOIN wes_profiled w
    ON w.cancer_study_identifier = a.cancer_study_identifier
   AND w.profile_type = m.profile_type
LEFT JOIN panel_profiled p
    ON p.cancer_study_identifier = a.cancer_study_identifier
   AND p.profile_type = m.profile_type
   AND p.hugo_gene_symbol = a.hugo_gene_symbol

WITH
alteration_to_profile AS (
    SELECT t.1 AS alteration_type, t.2 AS profile_type
    FROM (
        SELECT arrayJoin([
            ('mutation',           'MUTATION_EXTENDED'),
            ('amplification',      'COPY_NUMBER_ALTERATION'),
            ('deep_deletion',      'COPY_NUMBER_ALTERATION'),
            ('structural_variant', 'STRUCTURAL_VARIANT'),
            ('any',                'ANY')
        ]) AS t
    )
),
sample_cancer_type AS (
    SELECT q.preference_name AS preference_name,
           cd.cancer_study_identifier AS cancer_study_identifier,
           cd.sample_unique_id AS sample_unique_id,
           cd.attribute_value AS cancer_type
    FROM (SELECT * FROM clinical_data_derived WHERE cancer_study_identifier='msk_chord_2024') cd
    JOIN cancer_study_query_preferences q ON cd.cancer_study_identifier = q.cancer_study_identifier
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
events AS (
    SELECT cancer_study_identifier,
           hugo_gene_symbol,
           sample_unique_id,
           multiIf(
               variant_type = 'mutation' AND mutation_status != 'UNCALLED', 'mutation',
               variant_type = 'cna' AND cna_alteration = 2,                  'amplification',
               variant_type = 'cna' AND cna_alteration = -2,                 'deep_deletion',
               variant_type = 'structural_variant' AND mutation_status != 'UNCALLED',
                                                                             'structural_variant',
               '') AS event_type
    FROM (SELECT * FROM genomic_event_derived WHERE cancer_study_identifier='msk_chord_2024')
    WHERE off_panel = 0
      AND cancer_study_identifier IN (SELECT cancer_study_identifier FROM cancer_study_query_preferences)
),
typed_events AS (
    SELECT sct.preference_name AS preference_name, sct.cancer_type AS cancer_type,
           e.hugo_gene_symbol AS hugo_gene_symbol, e.event_type AS event_type,
           e.sample_unique_id AS sample_unique_id
    FROM events e
    JOIN sample_cancer_type sct
        ON e.cancer_study_identifier = sct.cancer_study_identifier
       AND e.sample_unique_id = sct.sample_unique_id
    WHERE e.event_type != ''
),
altered AS (
    SELECT preference_name, cancer_type, hugo_gene_symbol, event_type AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples
    FROM typed_events
    GROUP BY preference_name, cancer_type, hugo_gene_symbol, event_type
    UNION ALL
    SELECT preference_name, cancer_type, hugo_gene_symbol, 'any' AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples
    FROM typed_events
    GROUP BY preference_name, cancer_type, hugo_gene_symbol
),
profile_rows AS (
    SELECT cancer_study_identifier, alteration_type AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='msk_chord_2024')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'COPY_NUMBER_ALTERATION', 'STRUCTURAL_VARIANT')
    UNION ALL
    SELECT cancer_study_identifier, 'ANY' AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='msk_chord_2024')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'COPY_NUMBER_ALTERATION', 'STRUCTURAL_VARIANT')
),
sample_buckets AS (
    SELECT sct.preference_name AS preference_name, sct.cancer_type AS cancer_type,
           pr.profile_type AS profile_type, pr.sample_unique_id AS sample_unique_id,
           arraySort(groupUniqArray(pr.gene_panel_id)) AS panels
    FROM profile_rows pr
    JOIN sample_cancer_type sct
        ON pr.cancer_study_identifier = sct.cancer_study_identifier
       AND pr.sample_unique_id = sct.sample_unique_id
    GROUP BY preference_name, cancer_type, profile_type, sample_unique_id
),
wes_profiled AS (
    SELECT preference_name, cancer_type, profile_type, COUNT(*) AS n
    FROM sample_buckets
    WHERE has(panels, 'WES')
    GROUP BY preference_name, cancer_type, profile_type
),
signature_counts AS (
    SELECT preference_name, cancer_type, profile_type, panels, COUNT(*) AS n
    FROM sample_buckets
    WHERE NOT has(panels, 'WES')
    GROUP BY preference_name, cancer_type, profile_type, panels
),
panel_genes AS (
    SELECT gp.stable_id AS gene_panel_id, g.hugo_gene_symbol AS hugo_gene_symbol
    FROM gene_panel gp
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    JOIN gene g ON gpl.gene_id = g.entrez_gene_id
),
panel_profiled AS (
    SELECT preference_name, cancer_type, profile_type, hugo_gene_symbol, SUM(n) AS n
    FROM (
        SELECT DISTINCT s.preference_name, s.cancer_type, s.profile_type, s.panels, s.n,
                        pg.hugo_gene_symbol
        FROM (
            SELECT preference_name, cancer_type, profile_type, panels, n,
                   arrayJoin(panels) AS gene_panel_id
            FROM signature_counts
        ) s
        JOIN panel_genes pg ON s.gene_panel_id = pg.gene_panel_id
    )
    GROUP BY preference_name, cancer_type, profile_type, hugo_gene_symbol
)
SELECT a.preference_name,
       a.cancer_type,
       a.hugo_gene_symbol,
       a.alteration_type,
       a.altered_samples,
       COALESCE(w.n, 0) + COALESCE(p.n, 0) AS profiled_samples,
       now() AS built_at
FROM altered a
JOIN alteration_to_profile m ON a.alteration_type = m.alteration_type
LEFT JOIN wes_profiled w
    ON w.preference_name = a.preference_name
   AND w.cancer_type = a.cancer_type
   AND w.profile_type = m.profile_type
LEFT JOIN panel_profiled p
    ON p.preference_name = a.preference_name
   AND p.cancer_type = a.cancer_type
   AND p.profile_type = m.profile_type
   AND p.hugo_gene_symbol = a.hugo_gene_symbol

WITH
alteration_to_profile AS (
    SELECT t.1 AS alteration_type, t.2 AS profile_type
    FROM (
        SELECT arrayJoin([
            ('mutation',           'MUTATION_EXTENDED'),
            ('amplification',      'COPY_NUMBER_ALTERATION'),
            ('deep_deletion',      'COPY_NUMBER_ALTERATION'),
            ('structural_variant', 'STRUCTURAL_VARIANT'),
            ('any',                'ANY')
        ]) AS t
    )
),
sample_cancer_type AS (
    SELECT q.preference_name AS preference_name,
           cd.cancer_study_identifier AS cancer_study_identifier,
           cd.sample_unique_id AS sample_unique_id,
           cd.attribute_value AS cancer_type
    FROM (SELECT * FROM clinical_data_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018') cd
    JOIN cancer_study_query_preferences q ON cd.cancer_study_identifier = q.cancer_study_identifier
    WHERE cd.attribute_name = 'CANCER_TYPE'
),
events AS (
    SELECT cancer_study_identifier,
           hugo_gene_symbol,
           sample_unique_id,
           multiIf(
               variant_type = 'mutation' AND mutation_status != 'UNCALLED', 'mutation',
               variant_type = 'cna' AND cna_alteration = 2,                  'amplification',
               variant_type = 'cna' AND cna_alteration = -2,                 'deep_deletion',
               variant_type = 'structural_variant' AND mutation_status != 'UNCALLED',
                                                                             'structural_variant',
               '') AS event_type
    FROM (SELECT * FROM genomic_event_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    WHERE off_panel = 0
      AND cancer_study_identifier IN (SELECT cancer_study_identifier FROM cancer_study_query_preferences)
),
typed_events AS (
    SELECT sct.preference_name AS preference_name, sct.cancer_type AS cancer_type,
           e.hugo_gene_symbol AS hugo_gene_symbol, e.event_type AS event_type,
           e.sample_unique_id AS sample_unique_id
    FROM events e
    JOIN sample_cancer_type sct
        ON e.cancer_study_identifier = sct.cancer_study_identifier
       AND e.sample_unique_id = sct.sample_unique_id
    WHERE e.event_type != ''
),
altered AS (
    SELECT preference_name, cancer_type, hugo_gene_symbol, event_type AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples
    FROM typed_events
    GROUP BY preference_name, cancer_type, hugo_gene_symbol, event_type
    UNION ALL
    SELECT preference_name, cancer_type, hugo_gene_symbol, 'any' AS alteration_type,
           COUNT(DISTINCT sample_unique_id) AS altered_samples
    FROM typed_events
    GROUP BY preference_name, cancer_type, hugo_gene_symbol
),
profile_rows AS (
    SELECT cancer_study_identifier, alteration_type AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'COPY_NUMBER_ALTERATION', 'STRUCTURAL_VARIANT')
    UNION ALL
    SELECT cancer_study_identifier, 'ANY' AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'COPY_NUMBER_ALTERATION', 'STRUCTURAL_VARIANT')
),
sample_buckets AS (
    SELECT sct.preference_name AS preference_name, sct.cancer_type AS cancer_type,
           pr.profile_type AS profile_type, pr.sample_unique_id AS sample_unique_id,
           arraySort(groupUniqArray(pr.gene_panel_id)) AS panels
    FROM profile_rows pr
    JOIN sample_cancer_type sct
        ON pr.cancer_study_identifier = sct.cancer_study_identifier
       AND pr.sample_unique_id = sct.sample_unique_id
    GROUP BY preference_name, cancer_type, profile_type, sample_unique_id
),
wes_profiled AS (
    SELECT preference_name, cancer_type, profile_type, COUNT(*) AS n
    FROM sample_buckets
    WHERE has(panels, 'WES')
    GROUP BY preference_name, cancer_type, profile_type
),
signature_counts AS (
    SELECT preference_name, cancer_type, profile_type, panels, COUNT(*) AS n
    FROM sample_buckets
    WHERE NOT has(panels, 'WES')
    GROUP BY preference_name, cancer_type, profile_type, panels
),
panel_genes AS (
    SELECT gp.stable_id AS gene_panel_id, g.hugo_gene_symbol AS hugo_gene_symbol
    FROM gene_panel gp
    JOIN gene_panel_list gpl ON gp.internal_id = gpl.internal_id
    JOIN gene g ON gpl.gene_id = g.entrez_gene_id
),
panel_profiled AS (
    SELECT preference_name, cancer_type, profile_type, hugo_gene_symbol, SUM(n) AS n
    FROM (
        SELECT DISTINCT s.preference_name, s.cancer_type, s.profile_type, s.panels, s.n,
                        pg.hugo_gene_symbol
        FROM (
            SELECT preference_name, cancer_type, profile_type, panels, n,
                   arrayJoin(panels) AS gene_panel_id
            FROM signature_counts
        ) s
        JOIN panel_genes pg ON s.gene_panel_id = pg.gene_panel_id
    )
    GROUP BY preference_name, cancer_type, profile_type, hugo_gene_symbol
)
SELECT a.preference_name,
       a.cancer_type,
       a.hugo_gene_symbol,
       a.alteration_type,
       a.altered_samples,
       COALESCE(w.n, 0) + COALESCE(p.n, 0) AS profiled_samples,
       now() AS built_at
FROM altered a
JOIN alteration_to_profile m ON a.alteration_type = m.alteration_type
LEFT JOIN wes_profiled w
    ON w.preference_name = a.preference_name
   AND w.cancer_type = a.cancer_type
   AND w.profile_type = m.profile_type
LEFT JOIN panel_profiled p
    ON p.preference_name = a.preference_name
   AND p.cancer_type = a.cancer_type
   AND p.profile_type = m.profile_type
   AND p.hugo_gene_symbol = a.hugo_gene_symbol

WITH
sample_patient AS (
    SELECT sample_unique_id, patient_unique_id
    FROM (SELECT * FROM sample_derived WHERE cancer_study_identifier='msk_chord_2024')
),
discrete_cna_profiles AS (
    SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE'
),
profiled AS (
    SELECT cancer_study_identifier, alteration_type AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='msk_chord_2024')
    UNION ALL
    SELECT cancer_study_identifier, 'COPY_NUMBER_ALTERATION_DISCRETE' AS profile_type,
           sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='msk_chord_2024')
    WHERE alteration_type = 'COPY_NUMBER_ALTERATION'
      AND genetic_profile_id IN (SELECT stable_id FROM discrete_cna_profiles)
    UNION ALL
    SELECT cancer_study_identifier, 'ANY_MUT_CNA_SV' AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='msk_chord_2024')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT')
       OR (alteration_type = 'COPY_NUMBER_ALTERATION'
           AND genetic_profile_id IN (SELECT stable_id FROM discrete_cna_profiles))
)
SELECT cancer_study_identifier,
       'ALL_SAMPLES' AS profile_type,
       COUNT(DISTINCT sample_unique_id) AS samples,
       COUNT(DISTINCT patient_unique_id) AS patients,
       toUInt64(0) AS wes_samples,
       now() AS built_at
FROM (SELECT * FROM sample_derived WHERE cancer_study_identifier='msk_chord_2024')
GROUP BY cancer_study_identifier
UNION ALL
SELECT p.cancer_study_identifier,
       p.profile_type,
       COUNT(DISTINCT p.sample_unique_id) AS samples,
       COUNT(DISTINCT nullIf(sp.patient_unique_id, '')) AS patients,
       COUNT(DISTINCT if(p.gene_panel_id = 'WES', p.sample_unique_id, NULL)) AS wes_samples,
       now() AS built_at
FROM profiled p
LEFT JOIN sample_patient sp ON p.sample_unique_id = sp.sample_unique_id
GROUP BY p.cancer_study_identifier, p.profile_type

WITH
sample_patient AS (
    SELECT sample_unique_id, patient_unique_id
    FROM (SELECT * FROM sample_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
),
discrete_cna_profiles AS (
    SELECT stable_id FROM genetic_profile WHERE datatype = 'DISCRETE'
),
profiled AS (
    SELECT cancer_study_identifier, alteration_type AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    UNION ALL
    SELECT cancer_study_identifier, 'COPY_NUMBER_ALTERATION_DISCRETE' AS profile_type,
           sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    WHERE alteration_type = 'COPY_NUMBER_ALTERATION'
      AND genetic_profile_id IN (SELECT stable_id FROM discrete_cna_profiles)
    UNION ALL
    SELECT cancer_study_identifier, 'ANY_MUT_CNA_SV' AS profile_type, sample_unique_id, gene_panel_id
    FROM (SELECT * FROM sample_to_gene_panel_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
    WHERE alteration_type IN ('MUTATION_EXTENDED', 'STRUCTURAL_VARIANT')
       OR (alteration_type = 'COPY_NUMBER_ALTERATION'
           AND genetic_profile_id IN (SELECT stable_id FROM discrete_cna_profiles))
)
SELECT cancer_study_identifier,
       'ALL_SAMPLES' AS profile_type,
       COUNT(DISTINCT sample_unique_id) AS samples,
       COUNT(DISTINCT patient_unique_id) AS patients,
       toUInt64(0) AS wes_samples,
       now() AS built_at
FROM (SELECT * FROM sample_derived WHERE cancer_study_identifier='brca_tcga_pan_can_atlas_2018')
GROUP BY cancer_study_identifier
UNION ALL
SELECT p.cancer_study_identifier,
       p.profile_type,
       COUNT(DISTINCT p.sample_unique_id) AS samples,
       COUNT(DISTINCT nullIf(sp.patient_unique_id, '')) AS patients,
       COUNT(DISTINCT if(p.gene_panel_id = 'WES', p.sample_unique_id, NULL)) AS wes_samples,
       now() AS built_at
FROM profiled p
LEFT JOIN sample_patient sp ON p.sample_unique_id = sp.sample_unique_id
GROUP BY p.cancer_study_identifier, p.profile_type