Problem Statement
Some warehouses support QUALIFY to filter on window results. Without QUALIFY, how do you structure the query cleanly?
Explanation
Compute the window metric in a subquery or CTE, then filter in the outer query. This keeps the window logic readable and the filter straightforward, and it works across engines that lack QUALIFY.
Name the computed column clearly, such as rn or score, so the outer WHERE clause is self-explanatory. The optimizer can often inline the step without extra cost.
Code Solution
SolutionRead Only
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) AS rn FROM events ) SELECT * FROM ranked WHERE rn = 1;
