Write a SQL query using complex joins, subqueries, and window functions to extract specific institutional metrics.
