July 23, 20269 min read

Advanced SQL: 40 Senior/DBA-Level Concepts

Window functions, CTEs, performance, concurrency, security and administration: 40 topics for serious data work.

sql
banco-de-dados
avancado
dba
performance

Advanced SQL: 40 Senior / DBA-Level Concepts

This is where the difference lives between "it works" and "it works fast and safely in production". Forty topics on window functions, CTEs, performance, concurrency, security and administration — the toolkit of anyone doing serious data work.

1. Window Functions — OVER / PARTITION BY

Aggregates without collapsing the rows. You keep the detail AND see the group total on the same row.

SELECT nome, cidade, total,
  SUM(total) OVER (PARTITION BY cidade) AS total_cidade
FROM pedidos;

2. ROW_NUMBER — numbering rows

Gives a sequential number per group. The basis for 'the most recent of each'.

SELECT * FROM (
  SELECT p.*,
    ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data DESC) AS rn
  FROM pedidos p
) t
WHERE rn = 1; -- last order of each customer

3. RANK / DENSE_RANK

They rank with ties. RANK skips positions; DENSE_RANK does not.

SELECT nome, total,
  RANK()       OVER (ORDER BY total DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY total DESC) AS dense
FROM pedidos;

4. LAG / LEAD — previous/next row

Compare a row with its neighbour. Perfect for month-over-month variation.

SELECT mes, receita,
  LAG(receita) OVER (ORDER BY mes) AS mes_anterior,
  receita - LAG(receita) OVER (ORDER BY mes) AS variacao
FROM receita_mensal;

5. Running total

A total that runs row by row using ORDER BY inside the window.

SELECT data, valor,
  SUM(valor) OVER (ORDER BY data
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
FROM movimentacoes;

6. NTILE — splitting into bands

Divides the rows into N groups of similar size (quartiles, deciles).

SELECT nome, total,
  NTILE(4) OVER (ORDER BY total DESC) AS quartil
FROM clientes_gasto;

7. FIRST_VALUE / LAST_VALUE

Takes the first/last value of the window. Mind the frame on LAST_VALUE.

SELECT cidade, nome, total,
  FIRST_VALUE(nome) OVER (
    PARTITION BY cidade ORDER BY total DESC) AS maior_comprador
FROM pedidos;

8. CTE — WITH (named query)

Names subqueries and makes complex queries readable, top to bottom.

WITH faturamento AS (
  SELECT cidade, SUM(total) AS total FROM pedidos GROUP BY cidade
)
SELECT * FROM faturamento WHERE total > 10000;

9. Recursive CTE — hierarchies

A CTE that calls itself: it walks trees (org charts, categories, graphs).

WITH RECURSIVE subordinados AS (
  SELECT id, nome, gerente_id FROM funcionarios WHERE id = 1
  UNION ALL
  SELECT f.id, f.nome, f.gerente_id
  FROM funcionarios f
  JOIN subordinados s ON f.gerente_id = s.id
)
SELECT * FROM subordinados;

10. Pivot — rows into columns

Turns values into columns with conditional aggregation (works on any database).

SELECT produto,
  SUM(CASE WHEN mes = 1 THEN qtd END) AS jan,
  SUM(CASE WHEN mes = 2 THEN qtd END) AS fev,
  SUM(CASE WHEN mes = 3 THEN qtd END) AS mar
FROM vendas
GROUP BY produto;

11. Unpivot — columns into rows

The inverse of the pivot: it normalises columns into (key, value) pairs.

SELECT produto, 'jan' AS mes, jan AS qtd FROM vendas_wide
UNION ALL
SELECT produto, 'fev', fev FROM vendas_wide
UNION ALL
SELECT produto, 'mar', mar FROM vendas_wide;

12. MERGE / UPSERT

Inserts if it does not exist, updates if it does — in a single statement.

-- PostgreSQL / SQLite
INSERT INTO estoque (produto_id, qtd) VALUES (10, 5)
ON CONFLICT (produto_id)
DO UPDATE SET qtd = estoque.qtd + EXCLUDED.qtd;

13. ON DUPLICATE KEY (MySQL)

MySQL's take on the UPSERT.

INSERT INTO estoque (produto_id, qtd) VALUES (10, 5)
ON DUPLICATE KEY UPDATE qtd = qtd + VALUES(qtd);

14. Composite index and covering index

An index on several columns can answer the query without touching the table.

-- Order matters: it filters by cliente_id and sorts by data
CREATE INDEX idx_ped_cli_data ON pedidos(cliente_id, data);

-- Covering: it includes columns from the SELECT (PostgreSQL)
CREATE INDEX idx_cover ON pedidos(cliente_id) INCLUDE (total);

15. EXPLAIN — reading the execution plan

It shows HOW the database will execute. It is the number-one tuning tool.

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 42;
-- Look for 'Seq Scan' (bad on a large table) vs 'Index Scan' (good)

16. Sargable predicates

Write the WHERE in a way that lets the index be used. Do not wrap the column in a function.

-- BAD: a function on the column blocks the index
WHERE YEAR(data) = 2024

-- GOOD: uses the index on 'data'
WHERE data >= '2024-01-01' AND data < '2025-01-01'

17. Materialized View

Like a VIEW, but it stores the result. Fast to read; it has to be refreshed.

CREATE MATERIALIZED VIEW mv_dashboard AS
SELECT cidade, SUM(total) AS total FROM pedidos GROUP BY cidade;

REFRESH MATERIALIZED VIEW mv_dashboard;

18. Stored Procedure

A named, reusable block of SQL, with parameters and logic living in the database.

CREATE PROCEDURE dar_desconto(IN pid INT, IN pct DECIMAL)
BEGIN
  UPDATE produtos SET preco = preco * (1 - pct/100) WHERE id = pid;
END;

CALL dar_desconto(10, 15);

19. Function (UDF)

Returns a value and can be used inside queries.

CREATE FUNCTION preco_com_iva(preco DECIMAL)
RETURNS DECIMAL
RETURN preco * 1.23;

SELECT nome, preco_com_iva(preco) FROM produtos;

20. Triggers — automatic reactions

Runs code automatically on INSERT/UPDATE/DELETE. Great for auditing.

CREATE TRIGGER trg_log_preco
AFTER UPDATE ON produtos
FOR EACH ROW
INSERT INTO log_precos (produto_id, preco_antigo, preco_novo)
VALUES (OLD.id, OLD.preco, NEW.preco);

21. Isolation levels

They control what one transaction sees of the others. A trade-off between consistency and concurrency.

-- READ COMMITTED (the default on many databases), REPEATABLE READ, SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  -- consistent reads, no 'phantom reads'
COMMIT;

22. Deadlocks and locking

Two transactions block each other, each waiting on the other. Prevent it by accessing resources in the same order.

-- Locks the rows until the COMMIT (avoids a race)
BEGIN;
  SELECT * FROM contas WHERE id = 1 FOR UPDATE;
  UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

23. Table partitioning

Splits a giant table into pieces (by month, for instance). Queries scan only the right partition.

CREATE TABLE pedidos (id BIGINT, data DATE, total DECIMAL)
PARTITION BY RANGE (data);

CREATE TABLE pedidos_2024 PARTITION OF pedidos
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

24. Normalisation (1NF, 2NF, 3NF)

Organises data to avoid repetition and anomalies. Each fact in one place only.

-- BAD (repeats the customer on every order):
-- pedidos(id, cliente_nome, cliente_email, total)

-- GOOD (3NF): split into two tables linked by an FK
-- clientes(id, nome, email)
-- pedidos(id, cliente_id, total)

25. Denormalisation

Repeating data on purpose to gain read speed (common in BI/analytics).

-- Stores the total already computed so it is not recalculated on every read
ALTER TABLE clientes ADD COLUMN total_gasto DECIMAL(12,2) DEFAULT 0;
-- (kept up to date by a trigger or a job)

26. Partial / filtered index

Indexes only the rows that matter. Smaller and faster.

-- Only open orders (PostgreSQL)
CREATE INDEX idx_abertos ON pedidos(cliente_id) WHERE status = 'aberto';

27. GROUPING SETS / ROLLUP / CUBE

Several levels of totalling in a single query (subtotals + grand total).

SELECT cidade, categoria, SUM(total)
FROM pedidos
GROUP BY ROLLUP (cidade, categoria);

28. LATERAL JOIN / CROSS APPLY

A JOIN where the right side can see the left one. Great for 'top N per group'.

SELECT c.nome, p.*
FROM clientes c
CROSS JOIN LATERAL (
  SELECT * FROM pedidos p
  WHERE p.cliente_id = c.id ORDER BY data DESC LIMIT 3
) p; -- last 3 orders of each customer

29. JSON in SQL

Modern databases store and query JSON natively.

-- PostgreSQL
SELECT dados->>'email' AS email
FROM eventos
WHERE dados->>'tipo' = 'login';

-- Index on a JSON field
CREATE INDEX idx_tipo ON eventos ((dados->>'tipo'));

30. Full-text search

Real text search (relevance, stemming), far beyond LIKE '%x%'.

-- PostgreSQL
SELECT * FROM artigos
WHERE to_tsvector('portuguese', corpo) @@ to_tsquery('portuguese', 'banco & dados');

31. Avoid the common traps

SELECT * in production, N+1 and functions in the WHERE all kill performance.

-- Avoid: SELECT * (brings too many columns, breaks the covering index)
-- Avoid: querying inside a loop in the app (N+1) -> use a JOIN
-- Avoid: WHERE LOWER(email) = ... -> use a proper column/index

32. EXISTS vs IN vs JOIN

Similar semantics, different performance. EXISTS usually wins with large subsets; mind NOT IN and NULL.

-- Prefer EXISTS for 'has at least one'
SELECT c.* FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

-- NOT IN breaks if the subquery has a NULL -> use NOT EXISTS

33. Temp tables vs CTE vs variables

A CTE is great for readability; a temporary table materialises the result and can be indexed (good for heavy reuse).

CREATE TEMP TABLE tmp_top AS
SELECT cliente_id, SUM(total) AS total
FROM pedidos GROUP BY cliente_id;

CREATE INDEX ON tmp_top(total);
SELECT * FROM tmp_top ORDER BY total DESC LIMIT 10;

34. Efficient pagination (keyset)

A large OFFSET is slow. Paginate by the last id/value you saw (keyset/seek).

-- SLOW on high pages:
SELECT * FROM pedidos ORDER BY id LIMIT 20 OFFSET 100000;

-- FAST (keyset):
SELECT * FROM pedidos WHERE id > 100000 ORDER BY id LIMIT 20;

35. Batching

Update/delete in batches so you neither lock the table nor blow up the log.

-- Deletes 10 thousand at a time, in a loop in the app
DELETE FROM logs
WHERE id IN (
  SELECT id FROM logs WHERE criado_em < '2023-01-01' LIMIT 10000
);

36. SQL Injection — the number-one flaw

Never concatenate user input into the query. Always use parameterised queries.

-- DANGEROUS (injection!):
-- "SELECT * FROM users WHERE email = '" + input + "'"

-- SAFE (placeholder / prepared statement):
SELECT * FROM users WHERE email = ?;   -- the value is passed separately

37. Dynamic SQL, carefully

Building SQL at runtime is powerful, but dangerous. Validate identifiers and parameterise values.

-- If you need a dynamic column/table, use a whitelist:
-- if (col not in ['nome','email']) throw;
-- and ALWAYS parameterise the VALUES, never the raw input.

38. GRANT / REVOKE — permissions

Principle of least privilege: each user reaches only what they need.

GRANT SELECT, INSERT ON pedidos TO app_user;
REVOKE DELETE ON pedidos FROM app_user;

39. Backup and restore

Without a tested backup, there is no data. Automate it and test the restore.

-- PostgreSQL
-- pg_dump -Fc meubanco > backup.dump
-- pg_restore -d meubanco backup.dump

-- Point-in-time recovery with WAL to restore up to a given instant

40. Replication and sharding

Scale reads with replicas; scale writes/volume by splitting the data (sharding) on a key.

-- Read replica: the app sends SELECTs to the replica, writes to the primary.
-- Sharding: cliente_id % 4 decides which shard the row lives on.
-- (infrastructure configuration, not a single command)

Jhonatan Pinheiro

Loading page...