Caching computed results: when to cache at the query level vs application level

we have a number of expensive aggregation queries for dashboards and leaderboards that can take 5-10 seconds to run directly against the database. we're trying to figure out the best place to cache these computed results. one approach is to cache the final, fully aggregated result at the query level, which is simple but less flexible. the other approach is to cache intermediate per-user metrics or pre-aggregated data, and then perform the final aggregation on read from these cached pieces. this offers more granularity and flexibility if the aggregation logic changes, but it's more complex to manage and keep consistent. what are people's thoughts on the trade-offs here? when do you cache the final output versus caching the building blocks for those outputs? especially interested in experiences with systems that need to handle different aggregations on the same underlying data.