Skip to Content

FILTER Clause for Conditional Aggregation

When to use

Compute multiple conditional aggregates over the same rows in a single pass (counts of ‘click’ vs ‘purchase’, sum-of-spend for high-value users, etc.).

Analogy

FILTER is a sieve you put on the aggregate: only rows matching the sieve get weighed.

Data-flow diagram

  +-------------------------+
  | COUNT(*) FILTER (WHERE  |  click count
  |   kind='click')         |
  +-------------------------+
  +-------------------------+
  | SUM(amount) FILTER (WHERE|  high-value spend
  |   user_segment='gold')  |
  +-------------------------+

  single scan, single sort, multiple conditional aggregates.

Deep explanation

FILTER (WHERE predicate) is SQL-standard and applies a predicate at the aggregation stage. It is faster and clearer than wrapping each measure in CASE WHEN … THEN 1 ELSE NULL END — you keep one scan, one sort, one aggregation, then layer all measures. Combined with GROUP BY, you can build complete per-entity per-window feature vectors in one query.

Examples

Example 1

-- 8a: classic per-user feature vector in one scan
SELECT user_id,
       COUNT(*)                                                AS events,
       COUNT(*) FILTER (WHERE kind = 'click')                  AS clicks,
       COUNT(*) FILTER (WHERE kind = 'purchase')               AS purchases,
       SUM(amount) FILTER (WHERE kind = 'purchase')            AS spend,
       AVG(amount) FILTER (WHERE kind = 'refund')              AS avg_refund
  FROM events
 WHERE ts >= NOW() - INTERVAL '30 days'
 GROUP BY user_id;

Five related measures in one pass — zero extra scans, zero CASE-WHEN code paths.

Example 2

-- 8b: ratio as two FILTERs (without losing rows via case-shenanigans)
SELECT user_id,
       COUNT(*) FILTER (WHERE converted) AS converted,
       COUNT(*) FILTER (WHERE NOT converted) AS not_converted
  FROM experiments
 GROUP BY user_id;

Two FILTERs with COUNT let you preserve the denominator (otherwise dividing loses zeros on missing rows).

Example 3

-- 8c: combined with window functions for per-cohort deltas
SELECT user_id, cohort_week,
       COUNT(*)                                     AS events,
       COUNT(*) FILTER (WHERE is_first_session)     AS new_user_events
  FROM events
 GROUP BY user_id, cohort_week;

Combined grouping on cohort gives you segments for free without subqueries.

Common mistake

Wrapping each measure in SUM(CASE WHEN ... THEN 1 ELSE 0 END). It works but doubles the work: each case adds a virtual row, then the aggregator processes duplicates. FILTER is canonical and faster in Postgres. Watch out for FILTER (WHERE col IS NOT NULL) on numeric columns when the natural choice is FILTER (WHERE col IS NOT NULL) vs FILTER (WHERE col > 0) — semantics differ subtly.

Key takeaway

FILTER (WHERE …) is the canonical conditional aggregate; faster than CASE WHEN, clearer to read; combine multiple measures in one GROUP BY to keep feature pipelines cheap.

Production Failure Playbook

Failure scenario 1: case-when-aggregator-blowup

Failure scenario 2: filter-without-group-by