SQL: SELECT, Joins, Window Functions, CTEs and DDL Reference Guide

-- Window functions: rank, partition, running totals.
SELECT
    order_id,
    customer_id,
    created_at,
    total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY     created_at DESC
    )                                                            AS recency_rank,
    SUM(total) OVER (
        PARTITION BY customer_id
        ORDER BY     created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                            AS running_total,
    LAG(total)   OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_total,
    NTILE(4)     OVER (ORDER BY total DESC)                          AS spend_quartile
FROM   orders
WHERE  status = 'shipped';

-- Common helpers:
--   ROW_NUMBER / RANK / DENSE_RANK / PERCENT_RANK
--   FIRST_VALUE / LAST_VALUE / NTH_VALUE
--   LEAD / LAG / NTILE
--   SUM / AVG / COUNT / MIN / MAX -- all work as window aggregates.

-- Named subqueries (CTEs)
WITH recent_orders AS (
    SELECT *
    FROM   orders
    WHERE  created_at >= NOW() - INTERVAL '30 days'
),
totals AS (
    SELECT customer_id, SUM(total) AS spent
    FROM   recent_orders
    GROUP  BY customer_id
)
SELECT c.id, c.name, t.spent
FROM   customers c
JOIN   totals    t ON t.customer_id = c.id
WHERE  t.spent > 1000;

-- Recursive CTE: walk a tree / graph.
WITH RECURSIVE descendants AS (
    SELECT id, parent_id, name, 0 AS depth
    FROM   categories
    WHERE  id = 1
    UNION ALL
    SELECT c.id, c.parent_id, c.name, d.depth + 1
    FROM   categories c
    JOIN   descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants
ORDER  BY depth, name;

-- Tables, constraints, indexes (Postgres-leaning syntax).

CREATE TABLE customers (
    id          BIGSERIAL  PRIMARY KEY,
    email       CITEXT     NOT NULL UNIQUE,
    name        TEXT       NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id            BIGSERIAL  PRIMARY KEY,
    customer_id   BIGINT     NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    status        TEXT       NOT NULL CHECK (status IN ('pending','shipped','cancelled')),
    total         NUMERIC(12, 2) NOT NULL CHECK (total >= 0),
    metadata      JSONB,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (customer_id, id)
);

-- Indexes
CREATE INDEX orders_customer_status_idx
    ON orders (customer_id, status, created_at DESC);

-- Partial index — only the rows you actually query for.
CREATE INDEX orders_pending_idx
    ON orders (created_at)
    WHERE status = 'pending';

-- Add a column without downtime.
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;
ALTER TABLE orders RENAME COLUMN total TO amount_total;

-- Postgres: INSERT ... ON CONFLICT (the "upsert").
INSERT INTO inventory (sku, qty)
VALUES ('ABC-1', 10)
ON CONFLICT (sku) DO UPDATE
    SET qty = inventory.qty + EXCLUDED.qty,
        updated_at = NOW();

-- INSERT IGNORE-like behaviour: skip duplicates.
INSERT INTO inventory (sku, qty)
VALUES ('ABC-1', 10)
ON CONFLICT (sku) DO NOTHING;

-- RETURNING — read what you wrote in one round trip.
INSERT INTO orders (customer_id, total)
VALUES (42, 120.00)
RETURNING id, created_at;

-- UPDATE ... FROM (Postgres) for joined updates.
UPDATE orders o
SET    status = 'cancelled'
FROM   refunds r
WHERE  r.order_id = o.id
  AND  r.requested_at >= NOW() - INTERVAL '7 days';

-- MySQL upsert: INSERT ... ON DUPLICATE KEY UPDATE.
-- SQLite upsert: INSERT ... ON CONFLICT (same syntax as Postgres).

-- "Top customer by month" — joins + window + CTE in one.
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at)::DATE AS month,
        customer_id,
        SUM(total)                            AS spent
    FROM   orders
    WHERE  status = 'shipped'
      AND  created_at >= NOW() - INTERVAL '12 months'
    GROUP  BY 1, 2
),
ranked AS (
    SELECT
        month,
        customer_id,
        spent,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY spent DESC) AS rank
    FROM   monthly
)
SELECT
    r.month,
    c.name      AS top_customer,
    r.spent
FROM   ranked   r
JOIN   customers c ON c.id = r.customer_id
WHERE  r.rank = 1
ORDER  BY r.month;