Postgres: Filter an aggregation without the WHERE clause

Turns out you can filter aggregations in PostgreSQL outside of the WHERE clause.

During a recent project, we happened upon an interesting use case. We have data that shows a person’s (lid) status (NULL, Active, or Committed) for each fiscal year. We are trying to figure out the prior fiscal year where a person had an “Active” or “Committed” status. Each year a person’s status gets reset and they have to regain their status.

For example:

• For lid 1 fiscal year 2014, there is no prior “Active” or “Committed” year.
• For lid 1 fiscal year 2015, the prior “Active” or “Committed” year is 2014.
• For lid 1 fiscal year 2016, the prior “Active” or “Committed” year is 2014.
• For lid 1 fiscal year 2017, the prior “Active” or “Committed” year is 2016.

In order to solve this we had to use a window function, but we also needed to filter that window function.

Resulting Table

SQL Code:

-- drop the sample table
drop table if exists partition_test;

-- create a sample table
create table partition_test (
  lid int,
  fiscal_year int,
  end_designation varchar
);

-- insert sample data into the sample table
insert into partition_test (lid, fiscal_year, end_designation) VALUES
  (1, 2014, 'Active'),
  (1, 2015, NULL),
  (1, 2016, 'Committed'),
  (1, 2017, 'Active'),
  (2, 2015, NULL),
  (2, 2016, 'Active'),
  (3, 2016, 'Active'),
  (3, 2017, 'Committed');

-- show data from the sample table
select * from partition_test;

-- show the data from the sample table plus a new column that 
select 
  *,
  max(fiscal_year) filter(where partition_test.end_designation IS NOT NULL) over (PARTITION BY lid ORDER BY fiscal_year ASC ROWS BETWEEN UNBOUNDED PRECEDING and 1 PRECEDING) AS fiscal_year_prev_desig
from partition_test;```

See how Zuar can help you with your next data project!