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!

PostgreSQL vs MySQL: When to Use and How to Choose | Zuar

PostgreSQL and MySQL are both well-known powerhouse database management systems (DBMS). While both are fantastic, you still have to determine which is best for your specific needs. This article compares the differences between PostgreSQL and MySQL.

Zuar | BlogMark Segal

Install PostgreSQL drivers for Tableau Server Linux (Ubuntu) | Zuar

Tableau Error: Unable to connect to the data sourceAfter a fresh Tableau Server [https://www.tableau.com/products/server] install,if you haven’t installed PostgreSQL database drivers, you will run into thiserror when trying to view a Tableau Dashboard that has a live connection toPostgreSQL. Do…

Zuar | BlogJustin Freels

What’s the Difference Between SQL & NoSQL | Zuar

How do you know if a SQL or NoSQL database is the right choice for yourbusiness? First, let’s touch on what these two databases actually are. * SQL databases are relational. The data typically represents real-world objects, like a customer and their order details. * NoSQL databases are non-re…

Zuar | BlogTeam Zuar