SQL

SELECT id, name, email
FROM customers
WHERE country = 'MX'
  AND created_at >= '2026-01-01T00:00:00Z'
ORDER BY created_at DESC, name ASC
LIMIT 20 OFFSET 40;           -- page 3 with 20 rows per page

SELECT DISTINCT country FROM customers;
SELECT name AS customer_name FROM customers c;   -- column and table aliases

SELECT customer_id,
       COUNT(*)          AS orders,
       SUM(total)        AS revenue,
       AVG(total)        AS avg_ticket,
       MAX(created_at)   AS last_order
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total) > 1000
ORDER BY revenue DESC;

-- Customers with their order count, including those with no orders
SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Customers that never ordered
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Subquery in WHERE
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);

-- EXISTS is usually clearer and faster for "has at least one"
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- CTE: name an intermediate result
WITH top_customers AS (
  SELECT customer_id, SUM(total) AS revenue
  FROM orders GROUP BY customer_id
  ORDER BY revenue DESC LIMIT 10
)
SELECT c.name, t.revenue
FROM top_customers t JOIN customers c ON c.id = t.customer_id;

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

SELECT customer_id, id, total, created_at,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS n,
  RANK()       OVER (ORDER BY total DESC)                          AS rank_by_total,
  LAG(total)   OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_total,
  SUM(total)   OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;

CREATE TABLE customers (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  country     CHAR(2) NOT NULL DEFAULT 'MX',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  total        NUMERIC(12, 2) NOT NULL CHECK (total >= 0),
  status       TEXT NOT NULL DEFAULT 'pending',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO customers (email, name)
VALUES ('ana@example.com', 'Ana'), ('luis@example.com', 'Luis')
RETURNING id;                                  -- RETURNING is PostgreSQL

UPDATE orders SET status = 'paid'
WHERE id = 42;

DELETE FROM orders
WHERE status = 'pending' AND created_at < now() - INTERVAL '30 days';

-- Upsert (PostgreSQL): insert or update on unique conflict
INSERT INTO customers (email, name)
VALUES ('ana@example.com', 'Ana María')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

SAVEPOINT before_fee;
UPDATE accounts SET balance = balance - 1 WHERE id = 1;
ROLLBACK TO SAVEPOINT before_fee;    -- undo only the fee

COMMIT;                              -- or ROLLBACK; to undo everything