Write a SQL query using joins, aggregations, and subqueries to answer a departmental reporting question at University of Texas at Austin.
