The QUALIFY clause filters results of a previously computed window function according to user‑specified search conditions. You can use the clause to apply filtering conditions to the result of a window function without using a subquery. ()
The is supported in Snowflake.
Grammar Syntax
QUALIFY condition
Sample Source Patterns
Input Code:
IN -> Redshift_01.sql
CREATE TABLE store_sales
(
ss_sold_date DATE,
ss_sold_time TIME,
ss_item TEXT,
ss_sales_price FLOAT
);
INSERT INTO store_sales VALUES ('2022-01-01', '09:00:00', 'Product 1', 100.0),
('2022-01-01', '11:00:00', 'Product 2', 500.0),
('2022-01-01', '15:00:00', 'Product 3', 20.0),
('2022-01-01', '17:00:00', 'Product 4', 1000.0),
('2022-01-01', '18:00:00', 'Product 5', 30.0),
('2022-01-02', '10:00:00', 'Product 6', 5000.0),
('2022-01-02', '16:00:00', 'Product 7', 5.0);
SELECT *
FROM store_sales ss
WHERE ss_sold_time > time '12:00:00'
QUALIFY row_number()
OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2;
SS_SOLD_DATE
SS_SOLD_TIME
SS_ITEM
SS_SALES_PRICE
2022-01-01
17:00:00
Product 4
1000
2022-01-01
18:00:00
Product 5
30
2022-01-02
16:00:00
Product 7
5
Output Code:
OUT -> Redshift_01.sql
CREATE TABLE store_sales
(
ss_sold_date DATE,
ss_sold_time TIME,
ss_item TEXT,
ss_sales_price FLOAT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "11/05/2024", "domain": "test" }}';
INSERT INTO store_sales
VALUES ('2022-01-01', '09:00:00', 'Product 1', 100.0),
('2022-01-01', '11:00:00', 'Product 2', 500.0),
('2022-01-01', '15:00:00', 'Product 3', 20.0),
('2022-01-01', '17:00:00', 'Product 4', 1000.0),
('2022-01-01', '18:00:00', 'Product 5', 30.0),
('2022-01-02', '10:00:00', 'Product 6', 5000.0),
('2022-01-02', '16:00:00', 'Product 7', 5.0);
SELECT *
FROM
store_sales ss
WHERE ss_sold_time > time '12:00:00'
QUALIFY row_number()
OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2;
