Craig Kerstiens: Using Postgres FILTER

For developers who have been around SQL long enough, they know that there is often more than one way to get the same result. Today I wanted to look at a few different ways to aggregate and show a total with paid and unpaid status. First, we will use a common table express (CTE), which is a nice method for organizing subqueries. Second, we use CASE statements within aggregation context to filter out the values we want. Lastly, we use FILTER to clean up the syntax, but effectively do the same as the CASE statement. Hands On Tutorial We've loaded a sample data set and a hands on tutorial in our browser based Postgres playground. Target Output The report we're going to work to generate is a monthly report of revenue from an invoices table. We'll want our end report to look something like: mnth | billed | uncollected | collected ------------+---------+-------------+---------- 2023-02-01 | 1498.06 | 1498.06 | 0 2023-01-01 | 2993.95 | 1483.04 | 1510.91 2022-12-01 | 1413.17 | 382.84 | 1030.33 2022-11-01 | 1378.18 | 197.52 | 1180.66 2022-10-01 | 1342.91 | 185.03 | 1157.88 2022-09-01 | 1299.90 | 88.01 | 1211.89 2022-08-01 | 1261.97 | 85.29 | 1176.68 Invoices Table First, let’s look at the underlying data. Show the details of the invoices table: Table "public.invoices" Column | Type | Collation | Nullable | Default --------------------+--------------------------------+-----------+----------+-------------------------------------- id | bigint | | not null | nextval('invoices_id_seq'::regclass) account_id | integer | | | net_total_in_cents | integer | | | invoice_period | daterange | | | status | text | | [...]