Advanced Postgres¶

-- BEGIN a transaction
BEGIN;

-- Deduct from sender
UPDATE app.accounts
SET balance = balance - 100.00
WHERE id = 'account-ali';

-- Credit to receiver
UPDATE app.accounts
SET balance = balance + 100.00
WHERE id = 'account-omar';

-- If both succeeded , commit
COMMIT;

-- If anything went wrong , rollback (undoes both updates)
ROLLBACK;

BEGIN;

INSERT INTO app.orders (user_id, total_amount) VALUES ('user-1', 50.00);

SAVEPOINT before_items;

INSERT INTO app.order_items (order_id, product_id, quantity, price)
VALUES ('order-1', 'product-xyz', 1, 50.00);

-- Something went wrong with the items , rollback to savepoint
ROLLBACK TO SAVEPOINT before_items;

-- Try again with corrected data
INSERT INTO app.order_items (order_id, product_id, quantity, price)
VALUES ('order-1', 'product-abc', 2, 25.00);

COMMIT;

-- Read Committed (default) - prevents dirty reads
-- Each query in the transaction sees only committed data at the moment the query runs
-- Different queries in the same transaction can see different states of the data

-- Repeatable Read - prevents dirty reads AND non-repeatable reads
-- The transaction sees a snapshot of data taken at the first query
-- Prevents "same query , different results" within one transaction

-- Serializable - the strictest level
-- Transactions execute as if they ran one after another
-- May fail with "serialization failure" if conflicts are detected
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check seat availability atomically
SELECT COUNT(*) FROM app.flight_seats
WHERE flight_id = 'flight-123' AND booked = FALSE;

-- If count > 0, book the seat
UPDATE app.flight_seats SET booked = TRUE
WHERE flight_id = 'flight-123' AND seat_number = '12A';

COMMIT;

-- Basic CTE - define a named subquery
WITH active_users AS (
    SELECT id, email, username
    FROM app.users
    WHERE is_active = TRUE
      AND last_login > NOW() - INTERVAL '30 days'
)
SELECT au.username, COUNT(o.id) AS order_count
FROM active_users au
LEFT JOIN app.orders o ON o.user_id = au.id
GROUP BY au.id, au.username
ORDER BY order_count DESC;

-- Multiple CTEs in one query
WITH
user_orders AS (
    SELECT user_id, COUNT(*) AS orders, SUM(total_amount) AS total_spent
    FROM app.orders
    GROUP BY user_id
),
user_sessions AS (
    SELECT user_id, COUNT(*) AS sessions
    FROM app.sessions
    GROUP BY user_id
)
SELECT u.email, uo.orders, uo.total_spent, us.sessions
FROM app.users u
LEFT JOIN user_orders uo ON uo.user_id = u.id
LEFT JOIN user_sessions us ON us.user_id = u.id
WHERE u.is_active = TRUE;

-- Recursive CTE - queries that reference themselves (hierarchical data)
WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 AS level
    FROM app.employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: direct reports
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM app.employees e
    JOIN org_tree ot ON ot.id = e.manager_id
)
SELECT * FROM org_tree ORDER BY level, name;

-- ROW_NUMBER - assign a unique number to each row within a partition
SELECT
    email,
    role,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY role ORDER BY created_at) AS join_order
FROM app.users;
-- Each role group gets its own numbered sequence from 1

-- RANK and DENSE_RANK - ranking with ties
SELECT
    username,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rank
FROM user_spending;
-- RANK: 1, 2, 2, 4 (skips 3 when two are tied)
-- DENSE_RANK: 1, 2, 2, 3 (no gaps)

-- Running totals with SUM
SELECT
    created_at,
    total_amount,
    SUM(total_amount) OVER (ORDER BY created_at) AS running_revenue
FROM app.orders
WHERE status != 'cancelled';

-- Moving average (last 7 days)
SELECT
    created_at::date AS day,
    SUM(total_amount) AS daily_revenue,
    AVG(SUM(total_amount)) OVER (
        ORDER BY created_at::date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM app.orders
GROUP BY created_at::date;

-- LAG and LEAD - access previous/next row values
SELECT
    created_at::date AS day,
    SUM(total_amount) AS revenue,
    LAG(SUM(total_amount), 1) OVER (ORDER BY created_at::date) AS prev_day,
    SUM(total_amount) - LAG(SUM(total_amount), 1) OVER (ORDER BY created_at::date) AS day_over_day_change
FROM app.orders
GROUP BY created_at::date;

-- Create a view that shows only active users without password hashes
CREATE VIEW app.view_active_users AS
SELECT id, email, username, role, created_at
FROM app.users
WHERE is_active = TRUE;

-- Query the view like a table
SELECT * FROM app.view_active_users ORDER BY created_at DESC;

-- Create a view joining multiple tables for reporting
CREATE VIEW app.order_summary AS
SELECT
    o.id AS order_id,
    u.email AS user_email,
    o.total_amount,
    o.status,
    o.created_at,
    COUNT(oi.id) AS item_count
FROM app.orders o
JOIN app.users u ON u.id = o.user_id
LEFT JOIN app.order_items oi ON oi.order_id = o.id
GROUP BY o.id, u.email;

-- Create a materialized view for an expensive reporting query
CREATE MATERIALIZED VIEW app.daily_revenue_mv AS
SELECT
    created_at::date AS day,
    COUNT(*) AS orders,
    SUM(total_amount) AS revenue,
    COUNT(DISTINCT user_id) AS unique_customers
FROM app.orders
WHERE status != 'cancelled'
GROUP BY created_at::date
WITH DATA;

-- Refresh it (takes a lock - use CONCURRENTLY for production)
REFRESH MATERIALIZED VIEW CONCURRENTLY app.daily_revenue_mv;
-- CONCURRENTLY requires a unique index on the MV but doesn't block reads

-- Create a trigger function
CREATE OR REPLACE FUNCTION app.log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO app.audit_log (table_name, record_id, old_data, new_data, action, changed_by)
    VALUES (
        'users',
        NEW.id,
        CASE WHEN TG_OP = 'UPDATE' THEN row_to_json(OLD) ELSE NULL END,
        CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW) END,
        TG_OP,
        current_user
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Attach trigger to table
CREATE TRIGGER audit_users_changes
    AFTER INSERT OR UPDATE OR DELETE ON app.users
    FOR EACH ROW
    EXECUTE FUNCTION app.log_user_changes();