quantify aneuploidy

[fpbarthel]

@Kcjohnson let me know if this conflicts at all with what you had in mind:

/*
For each `pair_barcode` with segmentation data in the `titan_seg` table:
- Quantify the number of segments
- Quantify the sum of all segment sizes (this should roughly add up to the total genome size)
- Quantify the sum of all *heterozygous* segment sizes
- Quantify the proportion of heterozygous segments sizes vs non-heterozygous segments
*/
WITH
segquant AS
(
    SELECT
        pair_barcode,
        COUNT(*) AS num_seg,
        sum(upper(pos) - lower(pos)) AS seg_size,
        sum(CASE WHEN corrected_call IN ('HET','NEUT') THEN upper(pos) - lower(pos) ELSE 0 END) AS het_size
    FROM analysis.titan_seg
    GROUP BY pair_barcode
)
SELECT *, round(het_size::decimal/seg_size,4) AS prop_het FROM segquant ORDER BY 4

Results are now available via SELECT * FROM analysis.titan_seg_prop_het

[fpbarthel]

Compare these results between tumor pairs

/*
For each `tumor_pair_barcode` in the `tumor_pairs` table:
- Compare number of segments between (a) and (b)
- Compare proportion of genome that is heterozyous between (a) and (b)
*/
SELECT
    tumor_pair_barcode,
    tumor_barcode_a,
    tumor_barcode_b,
    s1.num_seg AS num_seg_a,
    s2.num_seg AS num_seg_b,
    s1.prop_het AS prop_het_a,
    s2.prop_het AS prop_het_b,
    s2.num_seg - s1.num_seg AS delta_num_seg,
    s2.prop_het - s1.prop_het AS delta_prop_het
FROM analysis.tumor_pairs pa
LEFT JOIN analysis.pairs p1 ON p1.tumor_barcode = pa.tumor_barcode_a
LEFT JOIN analysis.pairs p2 ON p2.tumor_barcode = pa.tumor_barcode_b
LEFT JOIN analysis.titan_seg_prop_het s1 ON s1.pair_barcode = p1.pair_barcode
LEFT JOIN analysis.titan_seg_prop_het s2 ON s2.pair_barcode = p2.pair_barcode
ORDER BY 9

[Kcjohnson]

We largely solved this issue with the analysis.aneuploidy and analysis.taylor_aneuploidy tables.