Analytical SQL Tips Series — Filter Clause

Often in a single query we need different versions of an aggregate and, while the SQL CASE statement might first come to mind, there’s an alternative called the FILTER clause.

FILTER enhances SQL readability by allowing a WHERE condition to be directly applied to aggregate functions, simplifying the process of creating diverse aggregations.

Some also may find the Filter Clause more intuitive to think in terms of the mental model.

This is a great example of how it’s possible to achieve the same result but with different syntax in SQL.

Note: It’s important to understand that the WHERE condition used inside the FILTER clause is distinct from the WHERE clause that follows the FROM statement in a query. They can coexist, but the WHERE condition within the FILTER clause specifically targets and affects only the designated aggregate function, not the entire query.

CASE WHEN Statement

FILTER Clause

Try it on DuckDB WASM . It takes about 10 seconds to initialize DuckDB and create the table

CREATE TABLE Weather AS 
FROM 'https://recce-dbt-tips.s3.amazonaws.com/central-park-weather.csv';

SELECT 

  count(CASE WHEN prcp BETWEEN 0.1 AND 0.2 THEN 1 END) low_days,
  count(CASE WHEN prcp > 0.4 THEN 1 END) high_days,

  avg(CASE WHEN prcp BETWEEN 0.1 AND 0.2 THEN tmax END) low_prcp_temp,
  avg(CASE WHEN prcp > 0.4 THEN tmax END) high_prcp_temp

FROM Weather;

SELECT 

     count(*)  FILTER (WHERE prcp BETWEEN 0.1 AND 0.2) low_days ,
     count(*)  FILTER (WHERE 0.4 < prcp)  high_days ,

     avg(tmax) FILTER (WHERE prcp BETWEEN 0.1 AND 0.2) low_prcp_temp,
     avg(tmax) FILTER (WHERE 0.4 < prcp) high_prcp_temp

FROM  Weather;

You can apply these SQL tip in your dbt code review. If you want to know more about analytical SQL tips, follow Recce on the Linkedin.