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 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.
-- 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;```