bq001.sql

WITH
mutCounts AS (
  SELECT
     COUNT(DISTINCT( Tumor_SampleBarcode )) AS CaseCount,
     Hugo_Symbol,
     HGVSc
  FROM
     `isb-cgc-bq.pancancer_atlas.Filtered_MC3_MAF_V5_one_per_tumor_sample`
  GROUP BY
     Hugo_Symbol,
     HGVSc
),
mutRatios AS (
  SELECT
     HGVSc,
     Hugo_Symbol,
     CaseCount,
     (CaseCount/SUM(CaseCount) OVER (PARTITION BY Hugo_Symbol)) AS ratio
  FROM
     mutCounts
)
SELECT  *
FROM
   mutRatios
WHERE
   CaseCount>=10
   AND ratio>=0.2
   AND HGVSc is not null
ORDER BY
   ratio DESC