Best practices and tips for tuning analytical functions?

rjs_docs

Can you please provide any tips for tuning analytical functions? For example, how does ordering projections by the PARTITION BY or ORDER BY columns help?
@bat @rbankula

From Optimizing Query Performance and Resource Pool Tuning.

baron42bba

First starting point is always EXPLAIN query.
If joins and nested queries are used CREATE LOCAL TEMPORARY TABLE can be a way out of the slow experience. Using those keep an eye on using the same segmentation for the temporary table as is used for the source table. If the output get's later inserted into another table compare the segmentation clause as well.

If possible develop the query with a smaller dataset on a slower system.
It might not work due to memory limits and gives ideas where to improve further.