14-postgresql-functions.sql

-- ============================================================
-- SQL Masterclass — Chapter 14: PostgreSQL Functions & Features
-- ============================================================
-- 🟣 POSTGRESQL SPECIFIC
--
-- In this chapter you will learn:
--   • DATE_TRUNC — truncating dates to specific precision
--   • EXTRACT — pulling parts from timestamps
--   • GENERATE_SERIES — creating sequences
--   • STRING_AGG — concatenating strings from groups
--   • ARRAY_AGG — aggregating into arrays
--   • COALESCE, NULLIF, GREATEST, LEAST
--   • Regular expressions (~ and ~*)
--   • Lateral joins
--   • FILTER clause for conditional aggregation
-- ============================================================
-- ⚠️ This chapter requires PostgreSQL. It will NOT work in SQLite.
-- ============================================================


-- ============================================================
-- 14.1  DATE_TRUNC — Truncate to precision
-- ============================================================
-- Much cleaner than SUBSTR() for date grouping!

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

-- Quarterly aggregation
SELECT
    DATE_TRUNC('quarter', order_purchase_timestamp::TIMESTAMP) AS quarter,
    COUNT(*) AS num_orders
FROM orders
GROUP BY DATE_TRUNC('quarter', order_purchase_timestamp::TIMESTAMP)
ORDER BY quarter;

-- Weekly order counts
SELECT
    DATE_TRUNC('week', order_purchase_timestamp::TIMESTAMP) AS week_start,
    COUNT(*) AS num_orders
FROM orders
GROUP BY DATE_TRUNC('week', order_purchase_timestamp::TIMESTAMP)
ORDER BY week_start;


-- ============================================================
-- 14.2  EXTRACT — Pull date parts
-- ============================================================

SELECT
    order_id,
    order_purchase_timestamp,
    EXTRACT(YEAR FROM order_purchase_timestamp::TIMESTAMP)    AS year,
    EXTRACT(MONTH FROM order_purchase_timestamp::TIMESTAMP)   AS month,
    EXTRACT(DAY FROM order_purchase_timestamp::TIMESTAMP)     AS day,
    EXTRACT(DOW FROM order_purchase_timestamp::TIMESTAMP)     AS day_of_week,
    EXTRACT(HOUR FROM order_purchase_timestamp::TIMESTAMP)    AS hour,
    EXTRACT(EPOCH FROM order_purchase_timestamp::TIMESTAMP)   AS unix_timestamp
FROM orders
LIMIT 10;

-- Hour-of-day analysis using EXTRACT
SELECT
    EXTRACT(HOUR FROM order_purchase_timestamp::TIMESTAMP) AS hour,
    COUNT(*) AS order_count,
    SUM(p.payment_value) AS revenue
FROM orders o
JOIN order_payments p ON o.order_id = p.order_id
GROUP BY EXTRACT(HOUR FROM order_purchase_timestamp::TIMESTAMP)
ORDER BY hour;


-- ============================================================
-- 14.3  GENERATE_SERIES — Create sequences
-- ============================================================
-- Generate a series of dates, numbers, or timestamps.

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


-- Fill in missing months (no gaps in time series!)
WITH all_months AS (
    SELECT generate_series(
        '2017-01-01'::DATE,
        '2018-12-01'::DATE,
        '1 month'::INTERVAL
    )::DATE AS month
),
monthly_orders AS (
    SELECT
        DATE_TRUNC('month', order_purchase_timestamp::TIMESTAMP)::DATE AS month,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY DATE_TRUNC('month', order_purchase_timestamp::TIMESTAMP)::DATE
)
SELECT
    am.month,
    COALESCE(mo.order_count, 0) AS order_count
FROM all_months am
LEFT JOIN monthly_orders mo ON am.month = mo.month
ORDER BY am.month;


-- Generate a number sequence
SELECT generate_series(1, 10) AS n;

-- Price histogram bins
WITH bins AS (
    SELECT generate_series(0, 5000, 100) AS bin_start
)
SELECT
    b.bin_start,
    b.bin_start + 99 AS bin_end,
    COUNT(oi.price) AS items_count
FROM bins b
LEFT JOIN order_items oi
    ON oi.price >= b.bin_start
    AND oi.price < b.bin_start + 100
GROUP BY b.bin_start
ORDER BY b.bin_start;


-- ============================================================
-- 14.4  STRING_AGG — Concatenate strings
-- ============================================================

-- List all payment types per order as comma-separated string
SELECT
    order_id,
    STRING_AGG(DISTINCT payment_type, ', ' ORDER BY payment_type) AS payment_types,
    SUM(payment_value) AS total_value
FROM order_payments
GROUP BY order_id
HAVING COUNT(DISTINCT payment_type) > 1
LIMIT 10;

-- List cities per state
SELECT
    customer_state,
    COUNT(DISTINCT customer_city) AS num_cities,
    STRING_AGG(DISTINCT customer_city, ', ' ORDER BY customer_city) AS cities
FROM customers
GROUP BY customer_state
ORDER BY num_cities DESC
LIMIT 5;


-- ============================================================
-- 14.5  FILTER CLAUSE — Clean conditional aggregation
-- ============================================================
-- PostgreSQL-specific alternative to SUM(CASE WHEN ...)

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


-- Revenue by payment type using FILTER
SELECT
    SUM(payment_value) FILTER (WHERE payment_type = 'credit_card') AS credit_card,
    SUM(payment_value) FILTER (WHERE payment_type = 'boleto')      AS boleto,
    SUM(payment_value) FILTER (WHERE payment_type = 'debit_card')  AS debit_card,
    SUM(payment_value) FILTER (WHERE payment_type = 'voucher')     AS voucher
FROM order_payments;


-- Monthly breakdown with FILTER
SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp::TIMESTAMP)::DATE AS month,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE o.order_status = 'delivered') AS delivered,
    AVG(r.review_score) FILTER (WHERE r.review_score IS NOT NULL) AS avg_review
FROM orders o
LEFT JOIN order_reviews r ON o.order_id = r.order_id
GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp::TIMESTAMP)::DATE
ORDER BY month;


-- ============================================================
-- 14.6  COALESCE, NULLIF, GREATEST, LEAST
-- ============================================================

-- COALESCE: first non-NULL value
SELECT
    order_id,
    order_delivered_customer_date,
    order_estimated_delivery_date,
    COALESCE(
        CAST(order_delivered_customer_date AS VARCHAR),
        CAST(order_estimated_delivery_date AS VARCHAR),
        'Not available'
    ) AS best_delivery_date
FROM orders
LIMIT 10;

-- NULLIF: returns NULL if two values are equal (great for safe division)
SELECT
    1.0 / NULLIF(0, 0) AS safe_division;  -- returns NULL instead of error

-- GREATEST and LEAST from a set of values
SELECT
    order_id,
    price,
    freight_value,
    GREATEST(price, freight_value)  AS higher_cost,
    LEAST(price, freight_value)     AS lower_cost
FROM order_items
LIMIT 10;


-- ============================================================
-- 14.7  REGULAR EXPRESSIONS
-- ============================================================
-- ~ : case-sensitive regex match
-- ~* : case-insensitive regex match
-- !~ : does NOT match

-- Find cities starting with 'São' or 'Sao' (accent-insensitive)
SELECT DISTINCT customer_city
FROM customers
WHERE customer_city ~* '^s[aã]o'
ORDER BY customer_city
LIMIT 15;

-- Find cities with numbers in their name
SELECT DISTINCT customer_city
FROM customers
WHERE customer_city ~ '[0-9]'
LIMIT 10;

-- Extract patterns with regexp_matches
SELECT DISTINCT
    customer_city,
    (regexp_matches(customer_city, '^([a-z]+)', 'i'))[1] AS first_word
FROM customers
LIMIT 10;


-- ============================================================
-- 14.8  LATERAL JOINS
-- ============================================================
-- LATERAL allows subqueries to reference columns from
-- preceding tables. Like a correlated subquery in FROM.

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


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Use DATE_TRUNC to find the quarter with the
--             highest average review score.

-- Exercise 2: Use GENERATE_SERIES to create a gap-free weekly
--             order count for all of 2018.

-- Exercise 3: Use STRING_AGG to show each order with a
--             comma-separated list of product categories.

-- Exercise 4: Rewrite this query using FILTER:
--             SELECT customer_state,
--               SUM(CASE WHEN payment_type='credit_card' THEN payment_value ELSE 0 END)
--             FROM ...


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
SELECT
    DATE_TRUNC('quarter', r.review_creation_date::TIMESTAMP) AS quarter,
    ROUND(AVG(r.review_score)::NUMERIC, 2) AS avg_score,
    COUNT(*) AS num_reviews
FROM order_reviews r
WHERE r.review_creation_date IS NOT NULL
GROUP BY DATE_TRUNC('quarter', r.review_creation_date::TIMESTAMP)
ORDER BY avg_score DESC
LIMIT 1;

-- Exercise 2
WITH all_weeks AS (
    SELECT generate_series(
        '2018-01-01'::DATE,
        '2018-12-31'::DATE,
        '1 week'::INTERVAL
    )::DATE AS week_start
),
weekly_orders AS (
    SELECT
        DATE_TRUNC('week', order_purchase_timestamp::TIMESTAMP)::DATE AS week_start,
        COUNT(*) AS order_count
    FROM orders
    WHERE order_purchase_timestamp >= '2018-01-01'
      AND order_purchase_timestamp < '2019-01-01'
    GROUP BY DATE_TRUNC('week', order_purchase_timestamp::TIMESTAMP)::DATE
)
SELECT
    aw.week_start,
    COALESCE(wo.order_count, 0) AS order_count
FROM all_weeks aw
LEFT JOIN weekly_orders wo ON aw.week_start = wo.week_start
ORDER BY aw.week_start;

-- Exercise 3
SELECT
    oi.order_id,
    STRING_AGG(DISTINCT COALESCE(t.product_category_name_english, p.product_category_name), ', '
        ORDER BY COALESCE(t.product_category_name_english, p.product_category_name)
    ) AS categories
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
WHERE p.product_category_name IS NOT NULL
GROUP BY oi.order_id
HAVING COUNT(DISTINCT p.product_category_name) > 1
LIMIT 10;

-- Exercise 4
SELECT
    c.customer_state,
    SUM(p.payment_value) FILTER (WHERE p.payment_type = 'credit_card') AS credit_card_value,
    SUM(p.payment_value) FILTER (WHERE p.payment_type = 'boleto') AS boleto_value,
    SUM(p.payment_value) FILTER (WHERE p.payment_type = 'debit_card') AS debit_card_value
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 c.customer_state
ORDER BY credit_card_value DESC
LIMIT 10;