📊 SQL Avançado — o que tens MESMO de dominar

1) Ordem lógica de execução (isto separa juniors de mids)

A ordem não é a ordem em que escreves:

1. FROM / JOIN — constrói o dataset
2. WHERE — filtra linhas
3. GROUP BY
4. HAVING — filtra grupos
5. SELECT — calcula colunas
6. DISTINCT
7. ORDER BY
8. LIMIT

Implicação clássica: não podes usar alias do SELECT no WHERE (normalmente), mas podes no ORDER BY.

---

2) Joins (e os bugs mais comuns)

• Inner: só matches
• Left: mantém esquerda, NULL no resto
• Full: mantém ambos (nem sempre disponível)

Anti-pattern comum

Filtrar coluna da tabela da direita no WHERE depois de um LEFT JOIN:

-- ISTO transforma o left join em inner join sem querer
SELECT ...
FROM A
LEFT JOIN B ON ...
WHERE B.col = 'x';

✅ Correto (se queres manter o left join):

SELECT ...
FROM A
LEFT JOIN B ON ... AND B.col = 'x';

---

3) Window Functions (o "core" do bloco)

Windows = calcular valores sem colapsar linhas (ao contrário do GROUP BY).

Sintaxe base:

<função>() OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS/RANGE ...
)

Top funções:

• Ranking: ROW_NUMBER, RANK, DENSE_RANK
• Navegação: LAG, LEAD
• Agregações: SUM() OVER, AVG() OVER
• Percentis: NTILE

ROW_NUMBER vs RANK vs DENSE_RANK

Com empates (ex: 10, 10, 9):

• ROW_NUMBER: dá sempre números únicos (quebra empates à força)
• RANK: empates partilham rank e salta números
• DENSE_RANK: empates partilham rank e não salta números

---

4) Top N por grupo (pergunta clássica)

Ex: "Top 3 compras por cliente":

WITH ranked AS (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT *
FROM ranked
WHERE rn <= 3;

---

5) Deduplicação (muito frequente)

Ex: "manter o registo mais recente por user_id":

WITH x AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM users_events
)
SELECT *
FROM x
WHERE rn = 1;

---

6) Rolling metrics (7 dias, 30 dias…)

Ex: rolling 7 dias por user:

SELECT
  user_id,
  day,
  SUM(value) OVER (
    PARTITION BY user_id
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d
FROM daily_metrics;

ROWS = baseado em linhas (bom quando tens 1 linha por dia).

RANGE = baseado em valores (datas/valores) e pode ser mais tricky.

---

7) CTEs (WITH) — quando usar e quando evitar

CTE é ótimo para legibilidade, mas em alguns engines pode "materializar" e piorar performance.

Em entrevistas: usa CTE para clareza e diz que em produção validas o plano de execução.

---

8) EXISTS vs IN (quando importa)

EXISTS: bom para "há pelo menos 1 match", geralmente eficiente.

IN: cuidado com NULL e listas grandes.

SELECT *
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

---

9) NULLs — 3 armadilhas que aparecem sempre

• NULL não é igual a nada, nem a NULL → usa IS NULL / IS NOT NULL
• COUNT(col) ignora NULL, COUNT(*) conta tudo

---

10) Performance mental-model

• Filtra cedo (WHERE)
• Evita SELECT * em tabelas enormes
• Particionamento/cluster ajuda
• Cuidado com joins que aumentam cardinalidade (explosões)

---

🎯 10 perguntas (estilo entrevista)

1. Explica a diferença entre ROW_NUMBER, RANK e DENSE_RANK. (Mini: valores 10,10,9 — qual o resultado de cada um?)
2. Como obter "o último registo por utilizador" numa tabela com eventos?
3. Top 2 produtos mais vendidos por categoria.
4. Qual a ordem de execução do SQL e porque não podes usar alias no WHERE?
5. Qual o bug típico de LEFT JOIN + WHERE na tabela da direita?
6. Diferença entre WHERE e HAVING.
7. Como calcular "rolling 7 dias" por utilizador?
8. Quando usar EXISTS em vez de IN?
9. Diferença entre COUNT(*) e COUNT(col).
10. Como detetar duplicados e removê-los logicamente numa query?

---

🧪 Exercícios práticos

1. Tabela orders(customer_id, order_id, amount, created_at). Escreve SQL para devolver a maior order por customer (por amount).

2. Tabela events(user_id, event_time, event_type). Queremos o evento anterior de cada user.

3. Explica o que acontece aqui e como corrigir:

SELECT a.id, b.status
FROM a
LEFT JOIN b ON a.id = b.a_id
WHERE b.status = 'active';