What other window functions are you aware of?

Besides RANK() and DENSE_RANK(), SQL offers several other window functions that are useful for analytical queries:

• ROW_NUMBER(): Assigns a unique sequential number to each row within a partition, starting at 1. Useful for pagination or selecting the first row per group.
• NTILE(n): Divides the rows in a partition into n buckets and assigns a bucket number to each row. Handy for percentile calculations.
• LAG() / LEAD(): Return the value of a column from a preceding or following row within the same partition, enabling comparisons between adjacent rows.
• FIRST_VALUE() / LAST_VALUE(): Return the first or last value in the ordered partition, often used with a window frame to limit the scope.
• NTH_VALUE(n): Returns the nth value in the ordered partition.
• CUME_DIST(): Computes the cumulative distribution of a value within a partition.
• PERCENT_RANK(): Computes the relative rank of a row within a partition as a fraction.

These functions are typically used with the OVER clause, specifying PARTITION BY and ORDER BY, and sometimes a frame clause to control the rows considered. They enable complex analytics without subqueries or self-joins.