The Declarative Power of SQL

-- You describe the *shape* of what you want.
-- The database decides how to retrieve it.

SELECT
    c.name,
    o.order_date,
    o.total
FROM   customers c
JOIN   orders o  ON o.customer_id = c.id
WHERE  o.total > 1000
  AND  o.order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND  o.order_date <  DATE_TRUNC('month', CURRENT_DATE)
ORDER BY o.total DESC;

-- No loop. No index management. No iteration logic.
-- The planner chooses the index, the join strategy, the sort method.
-- You only said: "give me this month's big orders, newest first."

-- Three tables. One question:
-- "Which UK authors wrote which books in which genres?"

SELECT
    a.name           AS author,
    b.title          AS book,
    g.name           AS genre,
    b.published_year
FROM       authors a
INNER JOIN books   b  ON b.author_id = a.id
INNER JOIN genres  g  ON g.id        = b.genre_id
WHERE a.country = 'UK'
ORDER BY a.name, b.published_year;

-- LEFT JOIN: include authors with no books yet
SELECT
    a.name,
    COUNT(b.id) AS book_count
FROM      authors a
LEFT JOIN books   b ON b.author_id = a.id
GROUP BY a.name
ORDER BY book_count DESC;

-- Running total and rank — per category, not globally
SELECT
    product_name,
    category,
    revenue,

    -- OVER() defines the window: "within each category, ordered by revenue"
    SUM(revenue) OVER (
        PARTITION BY category
        ORDER BY     revenue DESC
    ) AS running_total,

    RANK() OVER (
        PARTITION BY category
        ORDER BY     revenue DESC
    ) AS rank_in_category,

    -- Look one row back — no self-join needed
    LAG(revenue) OVER (
        PARTITION BY category
        ORDER BY     revenue DESC
    ) AS prev_revenue

FROM  product_sales
ORDER BY category, rank_in_category;

-- Month-over-month revenue growth — built in readable steps

WITH monthly AS (
    -- Step 1: aggregate orders by month
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total)                      AS revenue
    FROM  orders
    GROUP BY 1
),

with_prev AS (
    -- Step 2: pull in the previous month's revenue
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly
)

-- Step 3: compute the percentage change
SELECT
    TO_CHAR(month, 'YYYY-MM')              AS month,
    revenue,
    ROUND(
        100.0 * (revenue - prev_revenue)
               / NULLIF(prev_revenue, 0),
    1)                                     AS growth_pct
FROM  with_prev
WHERE prev_revenue IS NOT NULL
ORDER BY month;

-- A bank transfer: both updates happen, or neither does.

BEGIN;

UPDATE accounts
SET    balance = balance - 500
WHERE  id = 'alice';

-- If this raises an error, the whole transaction is rolled back.
-- Alice's balance stays unchanged. No money disappears.

UPDATE accounts
SET    balance = balance + 500
WHERE  id = 'bob';

COMMIT;

-- Durability: once COMMIT returns, the data is on disk.
-- A power failure after this point loses nothing.

-- SAVEPOINT lets you nest rollback points within a transaction
BEGIN;
    INSERT INTO audit_log (event) VALUES ('transfer_started');
    SAVEPOINT before_debit;
    UPDATE accounts SET balance = balance - 500 WHERE id = 'alice';
    -- can ROLLBACK TO before_debit without losing the audit log entry
COMMIT;