How to Use SQL QUALIFY to Simplify Queries

Stop writing 3 CTEs just to filter a window function. Most data professionals don't know this clause exists. It's called QUALIFY. You want the latest order per customer. Simple enough, right? The typical approach: Write a CTE. Add ROW_NUMBER(). Select from it. Filter WHERE rn = 1. Sometimes wrap it again if you need more logic. That's 3 steps for something that should take 1. QUALIFY lets you filter directly on window functions. No subqueries. No CTEs. No extra nesting. One line replaces all of that. 𝐖𝐡𝐲 𝐝𝐨𝐧'𝐭 𝐦𝐨𝐫𝐞 𝐩𝐞𝐨𝐩𝐥𝐞 𝐤𝐧𝐨𝐰 𝐚𝐛𝐨𝐮𝐭 𝐭𝐡𝐢𝐬? Most of us learned SQL on PostgreSQL or MySQL. Neither supports QUALIFY. So we never saw it. We got used to the CTE workaround and assumed that was the only way. But if you're working in Snowflake, BigQuery, or DuckDB, QUALIFY is native. It works out of the box. The use cases go beyond deduplication: → Top N per group (top 3 products per category) → Running totals that cross a threshold → Percentile filtering with NTILE or PERCENT_RANK I put together an infographic breaking down the syntax, use cases, and which databases support it. Check it out below 👇 If you've been writing CTEs for this, try QUALIFY once. You won't go back. ♻️ Repost to help someone clean up their SQL 📘 Preparing for data analyst interviews? Check out the book I co-authored with Pritesh and Amney with 150+ real questions: https://lnkd.in/dyzXwfVp 📩 I share tips on data analytics & data science in my free newsletter. Join 24,000+ readers → https://lnkd.in/dUfe4Ac6

Fewer CTEs. Cleaner SQL. Are you using QUALIFY yet? Any time you’ve needed to filter on a window function result (like ROW_NUMBER()), you probably reached for a CTE just to do WHERE rn = 1. In Databricks SQL, and also in Snowflake and BigQuery, you can skip that with QUALIFY, which filters after window functions are computed. In Databricks, it’s supported in Databricks SQL and Databricks Runtime 10.4 LTS+. Snowflake and BigQuery expose the same idea. QUALIFY is essentially a WHERE for window functions. Quick rules: - You must reference at least one window function in the SELECT list or in the QUALIFY itself. - Great for deduping, top-N per group, and “latest state per key” patterns, without a helper CTE. ____________ Any query that you will refactor now that you learned this? #databricks #sql #dataengineer

SQL Trick Many Many Data Engineers Don't Know: QUALIFY 🧠 Ever struggled with filtering window function results without resorting to subqueries or CTEs? Use QUALIFY clause - a game-changer for data engineers SELECT    product_name,   category,   total_sales,   ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS sales_rank FROM product_sales QUALIFY sales_rank <= 3; Imagine you're a data engineer at a large retail chain. The marketing team wants to identify the top 3 best-selling products in each category for a promotional campaign. Traditionally, you'd wrap this in a subquery or CTE. With QUALIFY, you can filter directly on the window function result, making your query cleaner and more efficient. This trick simplifies complex queries, improves readability, and potentially boosts performance. It's supported in modern data warehouses like Snowflake, BigQuery, and ofcourse #aws redshift. If you've read so far, do LIKE the post 👍 𝐏.𝐒: Learn no-nonsense AWS Data Engineering with 250+ others: https://aws.sachin.cloud & 𝐖𝐡𝐚𝐭𝐬𝐚𝐩𝐩 𝐠𝐫𝐨𝐮𝐩 : https://w.sachin.cloud

Imagine streamlining your already polished query with just one keyword — QUALIFY. While going through Zach Wilson's SQL Saturdays session, I realized how Snowflake introduced QUALIFY. In essence, QUALIFY lets you filter the results of window functions just like HAVING allows you to filter aggregates produced by GROUP BY. Consider the case where you want to identify the second-highest scorer for each NBA season. Typically, you’d use a window function within a CTE and then filter on the rank equal to 2. With QUALIFY, you can streamline this process and eliminate the extra CTE entirely. The challenge arises because window functions are processed after the WHERE clause in the SQL execution order (FROM / JOIN → WHERE → GROUP BY → HAVING → WINDOW → SELECT → DISTINCT → ORDER BY → LIMIT). That means you can’t directly filter window function results in the WHERE clause. This is where QUALIFY shines. In Snowflake, its execution comes right after the window functions are processed (… → WINDOW → QUALIFY → SELECT → …), allowing you to apply filters based on the outcomes of your window functions seamlessly. Take a look at the query comparison below to see the difference in action. Practice this on DataExpert.io's editor: https://lnkd.in/eXVkrQNe #SQL

✅ QUALIFY in #SQL makes window function filtering way cleaner. I posted about this before, but it's so useful, I want more people to know about it. I'm sure you've worked with the following problems: Find the most recent record for each employee. Or the top 3 sales per region. Or just deduplicate rows based on some ranking. ❌ The old way is to create a CTE or subquery with a window function. Assign row numbers. Then filter on `rn = 1` in the outer/main query. It works, but it makes our SQL ugly. You need a CTE or subquery just to filter the results of a window function. ✅ The better way is to use QUALIFY. Look at the second example in the screenshot. QUALIFY lets you filter window function results directly in the main query. No CTE or subquery needed. We get the same results with way less code. In simpler terms, QUALIFY is like WHERE, but for window functions. WHERE filters before aggregation. QUALIFY filters after window functions run. Here's where it fits in the execution order: FROM -> WHERE -> GROUP BY -> HAVING -> WINDOW Functions -> QUALIFY -> DISTINCT -> ORDER BY -> LIMIT ⚠️ I should note that QUALIFY is not part of standard SQL. It is available in #BigQuery and #Snoflake. But not in PostgreSQL, MySQL, or SQL Server yet. If you're on those databases, you still need the CTE/subquery approach. But if you're on BigQuery or Snowflake, use QUALIFY. I haven't written a CTE for window function filtering since learning about it. It makes our queries so much cleaner and easier to read. #DataEngineering #SQLTips

SQL Tip You Might Not Know (But Should): QUALIFY When you're using window functions like ROW_NUMBER(), RANK(), or DENSE_RANK() and want to filter after the function is applied, most people wrap everything in a subquery. But there’s a cleaner way- QUALIFY, especially in Snowflake, BigQuery, and a few others. Example: Let’s say you want the latest order per customer: SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders QUALIFY rn = 1; No messy subqueries. Just clean and readable SQL. This helps a ton when your logic is complex and you don’t want to keep nesting SELECTs inside SELECTs. ->Not all SQL engines support it (like SQL Server or MySQL), but if you're in Snowflake, BigQuery, or Redshift, it is a must-know. #sql #dataanalytics #businessanalysis

Most SQL users know ROW_NUMBER() for ranking within groups but not as many know QUALIFY. It’s supported in several modern SQL engines like Snowflake, BigQuery, Redshift, and Databricks. So, if you’re using one of those then you should consider adopting in-line deduping. Suppose you want to keep the top 3 rows per user based on a custom sort, prioritizing recent rows that aren't NULL in a given column. Instead of wrapping everything in a CTE or nested query, you can do this inline similar to WHERE or HAVING: select * from my_table qualify row_number() over ( partition by user_id order by case when some_column is not null then 0 else 1 end, event_date desc ) <= 3 Unlike nested queries or CTEs, this keeps your deduping and/or prioritization condition(s) alongside the rest of your query. #sql #datascience #dataengineering #data #analytics #bigdata