best-matches-for-neighborhood.sql

WITH selected_biopsy_rules AS (
    SELECT U0."id"
    FROM "mtx_rule" U0
    INNER JOIN "mtx_biopsyrule" U1 ON (U0."id" = U1."rule_id")
    WHERE U1."biopsy_id" = 3
)
SELECT 
    "mtx_biopsy"."id",
    "mtx_biopsy"."days_to_death",
    "mtx_patient"."is_deceased",
    count("mtx_biopsyrule"."rule_id") AS "num_common_rules"
FROM "mtx_biopsy"
INNER JOIN "mtx_biopsyrule" ON ("mtx_biopsy"."id" = "mtx_biopsyrule"."biopsy_id")
INNER JOIN "mtx_patient" ON ("mtx_biopsy"."patient_id" = "mtx_patient"."id")
WHERE ( 
    "mtx_biopsyrule"."rule_id" IN ( SELECT id FROM selected_biopsy_rules )
    AND NOT ( "mtx_biopsy"."id" = 3 )
)
GROUP BY "mtx_biopsy"."id", "mtx_biopsy"."days_to_death", "mtx_patient"."is_deceased"
ORDER BY "num_common_rules" DESC
LIMIT 20