Write a complex SQL query involving multiple joins, aggregations, and subqueries to retrieve specific transactional data.