Use window functions instead of aggregate functions, when possible
Before the introduction of window functions, the only means to aggregate data in SQL was by using a GROUP BY clause along with aggregate functions in the projection. This works well in many cases, and if aggregation data needed to be enriched with regular data, the grouped query can be pushed down into a joined subquery.
But SQL:2003 defined window functions, which are implemented by many popular database vendors. Window functions can aggregate data on result sets that are not grouped. In fact, each window function supports its own, independent PARTITION BY clause, which is an awesome tool for reporting.
Using window functions will:
Lead to more readable SQL (less dedicated GROUP BY clauses in subqueries)
Improve performance, as a RDBMS is likely to optimise window functions more easily
When you write a GROUP BY clause in a subquery, think again if this cannot be done with a window function.