SQL Window Functions Practice Questions

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.