Write a SQL query to join two tables and extract specific aggregated data based on a set of conditions.
