Filter, generate_series, and string_agg

PostgreSQL has functions that other databases wish they had. You already met FILTER during funnel analysis; now we make it official. FILTER replaces awkward CASE-inside-COUNT patterns, GENERATE_SERIES creates rows from thin air, and STRING_AGG concatenates values into a single string.

WHERE filters the entire query. FILTER applies to a single aggregate, so you can compute multiple conditional aggregates in one query. For example, counting delivered and canceled orders side by side without splitting into separate queries.

filter_clause.sql
sql
-- FILTER clause: cleaner than CASE
SELECT
  DATE_TRUNC('month', order_purchase_timestamp) AS month,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE order_status = 'delivered') AS delivered,
  COUNT(*) FILTER (WHERE order_status = 'canceled') AS canceled,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE order_status = 'delivered') / COUNT(*), 1
  ) AS delivery_rate
FROM orders
GROUP BY DATE_TRUNC('month', order_purchase_timestamp)
ORDER BY month;

FILTER (WHERE condition) is PostgreSQL-specific and much cleaner than SUM(CASE WHEN ... THEN 1 ELSE 0 END). Same result, better readability.

GENERATE_SERIES creates a sequence of dates or numbers. This is invaluable for zero-filling time series so months with no orders still show up as zero rather than being missing.

generate_series.sql
sql
-- GENERATE_SERIES: create a calendar + LEFT JOIN for zero-fill
WITH calendar AS (
  SELECT generate_series(
    '2017-01-01'::DATE,
    '2018-12-01'::DATE,
    INTERVAL '1 month'
  )::DATE AS month
),
monthly_orders AS (
  SELECT
    DATE_TRUNC('month', order_purchase_timestamp)::DATE AS month,
    COUNT(*) AS order_count
  FROM orders
  GROUP BY DATE_TRUNC('month', order_purchase_timestamp)
)
SELECT
  c.month,
  COALESCE(mo.order_count, 0) AS order_count
FROM calendar c
LEFT JOIN monthly_orders mo ON c.month = mo.month
ORDER BY c.month;

GENERATE_SERIES creates rows. LEFT JOIN with COALESCE ensures months with zero orders still appear (zero-fill pattern).

STRING_AGG is the text version of ARRAY_AGG. It concatenates values into a single string with a delimiter, which is great for readable summaries.

string_agg.sql
sql
-- STRING_AGG: concatenate categories per customer
SELECT
  o.customer_id,
  STRING_AGG(DISTINCT p.product_category_name, ', ' ORDER BY p.product_category_name) AS categories_purchased
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.product_category_name) > 2
LIMIT 10;

-- COALESCE / NULLIF / GREATEST / LEAST
SELECT
  COALESCE(review_comment_message, 'No comment') AS comment,
  NULLIF(review_score, 0) AS score_or_null,
  GREATEST(price, freight_value) AS larger_cost
FROM order_reviews r
JOIN order_items oi ON r.order_id = oi.order_id
LIMIT 10;

STRING_AGG joins values with a delimiter. COALESCE returns first non-NULL. NULLIF converts a specific value to NULL. GREATEST/LEAST pick the max/min from a list.

Quiz: Quiz

Loading practice…