14-postgresql-functions.md

Chapter 14: PostgreSQL Functions & Features

PostgreSQL is often called the "world's most advanced open-source database" because of its extensive feature set. This chapter explores specialized functions that simplify complex data manipulation and reporting.

Warning

These features are specific to PostgreSQL and will not function in SQLite.

14.1 DATE_TRUNC — Precise Date Grouping

DATE_TRUNC is a much cleaner way to group by time periods (week, month, quarter) than string manipulation.

-- Monthly revenue using DATE_TRUNC
SELECT
    DATE_TRUNC('month', order_purchase_timestamp::TIMESTAMP) AS month,
    SUM(p.payment_value) AS revenue
FROM orders o JOIN order_payments p ON o.order_id = p.order_id
GROUP BY 1
ORDER BY 1;

14.2 GENERATE_SERIES — Building Sequences

GENERATE_SERIES can create sequences of numbers or dates, which is perfect for generating "gap-free" time series or building histograms.

-- Generate all months in a range
SELECT generate_series(
    '2017-01-01'::DATE,
    '2018-12-01'::DATE,
    '1 month'::INTERVAL
)::DATE;

14.3 STRING_AGG — Grouped Concatenation

STRING_AGG allows you to concatenate strings from multiple rows into a single delimited string for each group.

-- List all payment types per order
SELECT
    order_id,
    STRING_AGG(DISTINCT payment_type, ', ' ORDER BY payment_type) AS methods
FROM order_payments
GROUP BY order_id;

14.4 The FILTER Clause

PostgreSQL provides an elegant FILTER clause for conditional aggregation, which is often more readable than the standard SUM(CASE WHEN ...) pattern.

-- Order counts using FILTER
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE order_status = 'delivered') AS delivered,
    COUNT(*) FILTER (WHERE order_status = 'canceled') AS canceled
FROM orders;

14.5 LATERAL JOINs

A LATERAL join allows a subquery in the FROM clause to reference columns from preceding tables/subqueries. This is like a "for each" loop in SQL.

-- Top 3 most expensive items per seller
SELECT
    s.seller_id,
    top_items.product_id,
    top_items.price
FROM sellers s
CROSS JOIN LATERAL (
    SELECT product_id, price FROM order_items oi
    WHERE oi.seller_id = s.seller_id
    ORDER BY price DESC LIMIT 3
) AS top_items;

---

Exercises

1. Use DATE_TRUNC to find the quarter with the highest average review score.
2. Use GENERATE_SERIES to create a gap-free weekly order count for all of 2018.
3. Use STRING_AGG to show each order with a comma-separated list of product categories.
4. Rewrite a conditional payment type query using the FILTER clause.

Solutions

-- Exercise 1
SELECT DATE_TRUNC('quarter', review_creation_date::TIMESTAMP) AS q, AVG(review_score) FROM order_reviews GROUP BY 1 ORDER BY 2 DESC LIMIT 1;

-- Exercise 2
WITH weeks AS (SELECT generate_series('2018-01-01'::DATE, '2018-12-31'::DATE, '1 week'::INTERVAL)::DATE AS w), counts AS (SELECT DATE_TRUNC('week', order_purchase_timestamp::TIMESTAMP)::DATE AS w, COUNT(*) AS c FROM orders WHERE order_purchase_timestamp LIKE '2018%' GROUP BY 1) SELECT weeks.w, COALESCE(counts.c, 0) FROM weeks LEFT JOIN counts ON weeks.w = counts.w;

-- Exercise 3
SELECT order_id, STRING_AGG(DISTINCT product_category_name, ', ') FROM order_items oi JOIN products p ON oi.product_id = p.product_id GROUP BY 1;

-- Exercise 4
SELECT customer_state, SUM(payment_value) FILTER (WHERE payment_type = 'credit_card') FROM order_payments p JOIN orders o ON p.order_id = o.order_id JOIN customers c ON o.customer_id = c.customer_id GROUP BY 1;