SQL Window Functions Practice Questions

They differ only in how they treat ties.

• ROW_NUMBER: always unique (1, 2, 3, 4); tied rows get an arbitrary order unless ORDER BY is fully deterministic.
• RANK: tied rows share a rank and the next rank is skipped (1, 2, 2, 4).
• DENSE_RANK: tied rows share a rank with no gap (1, 2, 2, 3).
• Top-N per group: use RANK or DENSE_RANK to include ties, ROW_NUMBER for exactly N rows.

WHERE runs before window functions are calculated, so it cannot see their results.

• Compute the window function in a CTE or subquery, then filter in the outer query (shown here).
• The same applies to HAVING.
• Snowflake, BigQuery and DuckDB have QUALIFY for this; PostgreSQL does not.

GROUP BY collapses each group to one row. PARTITION BY keeps every row and adds the group's calculation beside it.

• Use GROUP BY when you want one summary row per group.
• Use PARTITION BY when you need row detail next to a group total, like each order's share of its customer's spend.
• Adding ORDER BY inside OVER turns SUM into a running total.