get-top-rules.sql

WITH matched_rule AS (
	SELECT U0."id"
	FROM "mtx_rule" U0
	INNER JOIN "mtx_biopsyrule" U1 ON (U0."id" = U1."rule_id")
	WHERE U1."biopsy_id" = 3
),
top_biopsies AS (
	SELECT "mtx_biopsyrule"."biopsy_id" AS id
		,COUNT("mtx_biopsyrule"."rule_id") AS "num_common_rules"
	FROM "mtx_biopsyrule"
	WHERE (
			"mtx_biopsyrule"."rule_id" IN (
				SELECT id
				FROM matched_rule
				)
			AND NOT ("mtx_biopsyrule"."biopsy_id" = 3)
			)
	GROUP BY "mtx_biopsyrule"."biopsy_id"
	ORDER BY num_common_rules DESC limit 20
	)
	,top_rules
AS (
	SELECT array_agg("mtx_biopsyrule"."biopsy_id") AS biopsy_id
		,"mtx_biopsyrule"."rule_id" AS rule_id
		,count("mtx_biopsyrule"."biopsy_id") AS match_count
	FROM "mtx_biopsyrule"
	WHERE (
			"mtx_biopsyrule"."biopsy_id" IN (
				SELECT id
				FROM top_biopsies
				)
			)
	GROUP BY "mtx_biopsyrule"."rule_id"
	ORDER BY match_count DESC limit 20
	)
SELECT 
    gr.id,
    tr.biopsy_id AS matches
	,tr.rule_id
	,g.name
	,gr.mutated
FROM top_rules tr
INNER JOIN mtx_generule gr ON gr.rule_id = tr.rule_id
INNER JOIN mtx_gene g ON g.id = gr.gene_id
GROUP BY 
    gr.id
    ,tr.biopsy_id
	,tr.rule_id
	,g.name
	,gr.mutated
ORDER BY tr.rule_id