Rows can be eliminated by applying conditions on the new column value. The QUALIFY clause is analogous to the HAVING clause of aggregate functions. The QUALIFY clause eliminates rows based on the function value, returning a new value for each of the participating rows. For example:
SELECT StoreID, SUM(profit) OVER (PARTITION BY StoreID) FROM facts QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;
An SQL query that contains both ordered analytical functions and aggregate functions can have both a QUALIFY clause and a HAVING clause, as in the following example:
SELECT StoreID, SUM(sale), SUM(profit) OVER (PARTITION BY StoreID) FROM facts GROUP BY StoreID, sale, profit HAVING SUM(sale) > 15 QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;