Diagnosing and optimizing a slow, multi-join SQL query in the reporting database

[Evelyn.L]

I manage the reporting database for an e-commerce platform, and one of our core SQL queries for generating daily sales aggregates has suddenly slowed from taking a few seconds to over ten minutes as our transaction volume has grown. The query involves several joins across large tables and uses multiple window functions, and while I've added basic indexes, the execution plan is still doing massive full table scans. I need to dive into serious SQL query optimization but I'm out of my depth with more advanced techniques. For database administrators, what are your go-to strategies for diagnosing and fixing performance on complex analytical queries like this, and how do you decide between refactoring the query logic, creating more sophisticated composite indexes, or pushing for architectural changes like materialized views? I can't just throw more hardware at this.