viewof trends2 = await client.table(
`
WITH totals AS (SELECT INSTNM, UNITID, SUM(degrees) totals FROM degrees WHERE AWLEVEL=5 AND year > 2015 AND CARNEGIE IN (21, 22) AND CONTROL = 2 GROUP BY INSTNM, UNITID),
mmount AS (SELECT totals AS mmountotal FROM totals WHERE UNITID = 232706),
compgroup AS (SELECT * FROM totals CROSS JOIN mmount WHERE totals > 3000 AND totals < 3500), --ABS(totals - mmountotal) < 250),
tb AS (SELECT UNITID, CIPTitle, sum(degrees.degrees) AS "majors" FROM degrees WHERE AWLEVEL=5 AND CIPTitle IN (${JSON.stringify(
cutlist
)
.slice(1, -1)
.replaceAll(
'"',
"'"
)}) AND year > 2016 GROUP BY UNITID, CIPTitle ORDER BY majors DESC ),
totals2 AS (SELECT * FROM compgroup CROSS JOIN (SELECT DISTINCT(CIPTitle) FROM tb) t1),
tb1 AS (SELECT *, COALESCE(majors, 0) AS totals, COALESCE(majors/totals, 0) as share FROM tb RIGHT JOIN totals2 USING (UNITID, CIPTitle) ORDER BY share DESC)
SELECT ROW_NUMBER() OVER () as rank, * FROM tb1
`
)