Given a relational database schema, write a complex SQL query utilizing joins, subqueries, and aggregations to retrieve specific transaction data.
