comparing_cohorts.sql

gistfile1.txt

WITH
  mutatedSamples AS (
  SELECT
    project_short_name,
    sample_barcode_tumor
  FROM
    `isb-cgc.TCGA_hg19_data_v0.Somatic_Mutation_MC3`
  WHERE
    Hugo_Symbol = 'EGFR'
    AND Variant_Type = 'SNP'
    AND Consequence = 'missense_variant' ),
    --
    --
    --
  mutExpr AS (
  SELECT
    HGNC_gene_symbol,
    LOG10(normalized_count+1) AS averageExpr,
    "mutated_expression" AS variantStatus,
    sample_barcode
  FROM
    `isb-cgc.TCGA_hg19_data_v0.RNAseq_Gene_Expression_UNC_RSEM`
  WHERE
    HGNC_gene_symbol = 'EGFR'
    AND sample_barcode IN (
    SELECT
      sample_barcode_tumor
    FROM
      mutatedSamples) ),
      --
      --
      --
  nonMutExpr AS (
  SELECT
    HGNC_gene_symbol,
    LOG10(normalized_count+1) AS averageExpr,
    'non_mutated_expression' as variantStatus,
    sample_barcode
  FROM
    `isb-cgc.TCGA_hg19_data_v0.RNAseq_Gene_Expression_UNC_RSEM`
  WHERE
    HGNC_gene_symbol = 'EGFR'
    AND REGEXP_CONTAINS(sample_barcode, "-01A")
    AND sample_barcode NOT IN (
    SELECT
      sample_barcode_tumor
    FROM
      mutatedSamples) )
(
  SELECT
    HGNC_gene_symbol,
    COUNT(sample_barcode) AS N,
    AVG(averageExpr) AS AvgExpr,
    STDDEV_SAMP(averageExpr) AS StdDev,
    variantStatus
  FROM
    mutExpr
  GROUP BY
    HGNC_gene_symbol,
    variantStatus
)
    --
UNION ALL
    --
(
  SELECT
    HGNC_gene_symbol,
    COUNT(sample_barcode) AS N,
    AVG(averageExpr) AS AvgExpr,
    STDDEV_SAMP(averageExpr) AS StdDev,
    variantStatus
  FROM
    nonMutExpr
  GROUP BY
    HGNC_gene_symbol,
    variantStatus
)