join BCGSC and GDC hg38 miRNA isoform-level data and compute read_count deltas

bcgsc_gdc_delta_isoforms.sql

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
    *
  FROM
    `isb-cgc-04-0010.draft_new_data.TCGA_gdc_hg38_miRNA_Isoform_Expression`
  JOIN
    aList
  ON
    aliquot_barcode=abarcode ),
  bcData AS (
  SELECT
    *
  FROM
    `isb-cgc-04-0010.draft_new_data.bcgsc_hg38_isoforms` )
SELECT
  b.aliquot_barcode AS b_barcode,
  #g.aliquot_barcode AS g_barcode,
  b.mirna_id AS b_mirna_id,
  #g.mirna_id AS g_mirna_id,
  b.mirna_accession AS b_accession,
  #g.mirna_accession AS g_accession,
  b.mirna_transcript AS b_transcript,
  #g.mirna_transcript AS g_transcript,
  b.chromosome AS b_chrom,
  #g.chromosome AS g_chrom,
  b.start_pos AS b_start,
  #g.start_pos AS g_start,
  b.end_pos AS b_end,
  #g.end_pos AS g_end,
  b.strand AS b_strand,
  #g.strand AS g_strand,
  b.read_count AS b_read_count,
  g.read_count AS g_read_count,
  (b.read_count - g.read_count) AS delta_read_count,
  b.reads_per_million_miRNA_mapped AS b_rpm,
  g.reads_per_million_miRNA_mapped AS g_rpm,
  b.cross_mapped AS b_cross,
  g.cross_mapped AS g_cross
FROM
  bcData b
JOIN
  gdcData g
ON
  b.aliquot_barcode=g.aliquot_barcode
  AND b.mirna_id=g.mirna_id
  AND b.mirna_accession=g.mirna_accession
  AND b.mirna_transcript=g.mirna_transcript
  AND b.chromosome=g.chromosome
  AND b.start_pos=g.start_pos
  AND b.end_pos=g.end_pos
  AND b.strand=g.strand