CODEWITHSUNDEEP
sqlteradatawindow-functions
Grigory P
Grigory P

Reputation: 191

Teradata / Window functions - QUALIFY

I'm analyzing the code of my collegue. Found this query:

SELECT 
     client_id
from lib.applications
QUALIFY Row_Number() Over(PARTITION BY client_id ORDER BY closed) = 1
WHERE closed=0 and application_date > '2016-01-01'

Logically, the query should return a list of clients with active (not closed) applications. I can't uderstand, why he used QUALIFY etc.. here? The request below is simplier and returns the same:

SELECT 
     client_id
from lib.applications
WHERE closed=0 and application_date > '2016-01-01'

Do you have any idea, for what reason QUALIFY could be used here?

Upvotes: 0

Views: 246

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

QUALIFY is returning one row per client_id. The more colloquial way of writing the query would be:

SELECT DISTINCT client_id
FROM lib.applications
WHERE closed = 0 and application_date > '2016-01-01';

Perhaps the author of the query checked performance and found that QUALIFY is faster in this case (although I would doubt that). Perhaps the author was thinking of including other columns, in which case SELECT DISTINCT would not work.

Upvotes: 1

Related Questions