Write an SQL query to retrieve, join, and aggregate data from multiple tables related to a software application.