Use Snowflake QUALIFY to Filter Window Functions DirectlyUse Snowflake QUALIFY to Filter Window Functions Directly
The network for creativity
Join 1.25M professional creatives like you
Connect with clients, get discovered, and run your business 100% commission-free
Creatives on Contra have earned over $150M and we are just getting started
Still wrapping a query in a subquery just to filter on ROW_NUMBER()? In Snowflake you don't have to.
QUALIFY works like WHERE, but for window functions. It's evaluated after the window is calculated, so you can filter on the result directly:
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1
That one line keeps the latest version of every order. You don't need a nested SELECT, a helper column like rn, or a SELECT * that drags rn into your output.
It also works with RANK, DENSE_RANK, LAG and any other window expression, which makes it ideal for deduplication, top-N per group and keeping the latest record per key.
Cleaner SQL is easier to review, and easier to review means fewer bugs in production.
#Snowflake #SQL #DataEngineering
Post image
Back to feed
The network for creativity
Join 1.25M professional creatives like you
Connect with clients, get discovered, and run your business 100% commission-free
Creatives on Contra have earned over $150M and we are just getting started