smrgit

WITH
  -- first we get the 77 samples that passed the QC tests
  qcSet AS (
  SELECT
    TCGA_case_ID AS case_barcode
  FROM
    `isb-cgc.hg19_data_previews.TCGA_Breast_SuppTable01`
  WHERE
    QC_Status="pass" ),
  --

smrgit

WITH
  -- first we get the 77 samples that passed the QC tests
  qcSet AS (
  SELECT
    TCGA_case_ID AS case_barcode
  FROM
    `isb-cgc.hg19_data_previews.TCGA_Breast_SuppTable01`
  WHERE
    QC_Status="pass" ),
  --

smrgit

WITH
  cases AS (
  SELECT
    case_barcode
  FROM
    `isb-cgc.TCGA_bioclin_v0.Clinical`
  WHERE
    project_short_name="TCGA-LUSC"
    OR project_short_name="TCGA-LUAD" ),
  radImg AS (

smrgit

WITH
  t1 AS (
  SELECT
    dbName,
    file_id AS file_gdc_id,
    access,
    cases__project__program__name AS program_name,
    cases__project__project_id AS project_short_name,
    experimental_strategy,
    data_category,

smrgit

WITH
  t1 AS (
  SELECT
    dbName,
    file_id AS file_gdc_id,
    access,
    cases__project__program__name AS program_name,
    cases__project__project_id AS project_short_name,
    experimental_strategy,
    data_category,

smrgit

WITH
  -- first we just extract all of the rows corresponding to mature miR's
  d1 AS (
  SELECT
    sample_barcode,
    aliquot_barcode,
    chromosome,
    start_pos,
    end_pos,
    strand,

smrgit

WITH
  aList AS (
  SELECT
    aliquot_barcode AS abarcode
  FROM
    `isb-cgc-04-0010.draft_new_data.bcgsc_hg38_isoforms`
  GROUP BY
    abarcode ),
  gdcData AS (
  SELECT

smrgit

WITH
  --
  -- we start by translating the correlations that we got to ranks,
  -- based on sorting the genes on corrByGene "DESC"
  -- this will result in the highest positive correlation getting
  -- rank #1, etc
  -- we also lightly filter the genes by excluding any with near-zero
  -- or negative correlation coefficients, and the result is a list
  -- of approx 9000 genes with symbol, correlation, and rank
  geneScoresT AS (

smrgit

WITH
  hg38_d1 AS (
    -- we start with a table at the aliquot level, in case there are multiple aliquots
    -- for a single sample;
  SELECT
    sample_barcode,
    aliquot_barcode,
    mirna_id,
    reads_per_million_miRNA_mapped AS RPM
  FROM

smrgit

WITH
  -- first we get the 77 samples that passed the QC tests
  qcSet AS (
  SELECT
    TCGA_case_ID AS case_barcode
  FROM
    `isb-cgc.hg19_data_previews.TCGA_Breast_SuppTable01`
  WHERE
    QC_Status="pass" ),
  --