14.1. SQL Tips & Tricks

14.1.1. QUALIFY Statement For Cleaner Queries

One underrated SQL command: QUALIFY.

With QUALIFY, you can filter the results of a window function like RANK() without needing another SELECT statement.

See the example below where we want to get the 3rd highest earner in every department.

# With QUALIFY
SELECT
  employee_id,
  department_id,
  salary
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS ranking
FROM 
  employees
QUALIFY
  ranking = 3;
  
# Without QUALIFY
SELECT *
FROM (
  SELECT
    employee_id,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS ranking
  FROM 
    employees
) AS rank_employees
WHERE
  ranking = 3;