Write a SQL query using JOINs, subqueries, and window functions to extract specific insights from messy research databases.