-- ============================================================================
-- Aula 6: exercícios de SQL, banco de fraude em cartão
-- ============================================================================
-- Como usar: abra este arquivo no worksheet do seu FreeSQL, já com as 6 tabelas
-- carregadas (veja o Colab `Aula 06 - Alunos - Carregar Dados para o FreeSQL`, se ainda não rodou).
--
-- Para cada pergunta, escreva a sua consulta no espaço logo abaixo dela, e rode
-- no worksheet. Não apague a pergunta: ela fica como comentário.
--
-- As perguntas seguem a mesma ordem dos blocos da Aula 5: anatomia da consulta,
-- filtros, agregações, e por último juntando tabelas. As duas últimas são
-- desafio, e pedem para combinar tudo o que você praticou até aqui.
-- ============================================================================


-- ============================================================================
-- As 6 tabelas
-- ============================================================================
-- categorias (12 linhas)
--   id_categoria    número inteiro, chave primária
--   nome            texto, o nome da categoria (Eletrônicos, Farmácia, ...)
--
-- clientes (1.500 linhas)
--   id_cliente        número inteiro, chave primária
--   nome              texto
--   cpf               texto
--   cidade            texto
--   estado            texto, sigla de 2 letras (SP, PR, RJ, ...)
--   data_nascimento   data
--   data_cadastro     data
--   telefone          texto, pode estar vazio
--   score_risco       número decimal, de 0 a 100
--
-- lojistas (300 linhas)
--   id_lojista      número inteiro, chave primária
--   nome            texto
--   id_categoria    número inteiro, aponta para categorias
--   cidade          texto
--   estado          texto, sigla de 2 letras
--   data_cadastro   data
--
-- cartoes (1.937 linhas)
--   id_cartao       número inteiro, chave primária
--   id_cliente      número inteiro, aponta para clientes
--   bandeira        texto (Visa, Mastercard, Elo, American Express)
--   final_cartao    texto, os 4 últimos dígitos do cartão
--   data_emissao    data
--   limite          número decimal
--   status          texto (ativo, bloqueado, cancelado)
--
-- transacoes (50.000 linhas, a tabela grande)
--   id_transacao    número inteiro, chave primária
--   id_cartao       número inteiro, aponta para cartoes
--   id_lojista      número inteiro, aponta para lojistas
--   valor           número decimal
--   data_hora       data e hora
--   canal           texto (presencial, online)
--   status          texto (aprovada, recusada, contestada)
--   pais            texto
--
-- contestacoes (1.013 linhas)
--   id_contestacao      número inteiro, chave primária
--   id_transacao        número inteiro, aponta para transacoes
--   data_contestacao    data e hora
--   motivo              texto
--   resultado           texto (Estornado, Negado, Em análise)
--
-- Os números de linha acima são do volume atual. Rode SELECT COUNT(*) em
-- qualquer tabela para conferir se bate com o banco que você carregou.
-- ============================================================================


-- ============================================================================
-- BLOCO 1: Anatomia da consulta
-- ============================================================================

-- Pergunta 1
-- Liste o nome e a cidade de todos os clientes de Curitiba.

SELECT nome, cidade
FROM clientes
WHERE cidade='Curitiba';

-- Pergunta 2
-- Liste as 10 transações de maior valor (id_transacao e valor), da maior para
-- a menor.

SELECT id_transacao, valor
FROM transacoes
ORDER BY valor DESC
FETCH FIRST 10 ROWS ONLY;


-- Pergunta 3
-- Liste os cartões com status 'bloqueado', mostrando id_cartao, id_cliente e
-- bandeira.

SELECT id_cartao, id_cliente, bandeira
FROM cartoes
WHERE status = 'bloqueado';

-- ============================================================================
-- BLOCO 2: Filtros e condições
-- ============================================================================

-- Pergunta 4
-- Liste as transações com valor entre R$1.000 e R$5.000.

SELECT *
FROM transacoes
WHERE valor BETWEEN 1000 AND 5000;

-- Pergunta 5
-- Liste os lojistas da cidade de São Paulo, com nome e cidade.

SELECT nome, cidade
FROM lojistas
WHERE cidade='São Paulo';


-- Pergunta 6
-- Liste os clientes cujo nome começa com a letra A.

SELECT *
FROM clientes
WHERE nome LIKE 'A%';

-- Pergunta 7
-- Liste os clientes sem telefone cadastrado.

SELECT *
FROM clientes
WHERE telefone IS NULL;

-- ============================================================================
-- BLOCO 3: Agregações
-- ============================================================================

-- Pergunta 8
-- Quantas transações existem no total, qual o valor total movimentado, e qual
-- o valor médio por transação?

SELECT COUNT(*) AS quantidade,
       SUM(valor) AS soma_total,
       AVG(valor) AS valor_medio
FROM transacoes;

-- Pergunta 9
-- Para cada lojista, quantas transações e qual o valor total? Ordene do maior
-- valor para o menor.

SELECT l.nome,
       COUNT(*) AS quantidade_transacoes,
       SUM(t.valor) AS valor_total
FROM transacoes t
LEFT JOIN lojistas l
ON l.id_lojista = t.id_lojista
GROUP BY l.nome
ORDER BY valor_total DESC;

-- Pergunta 10
-- Quais lojistas somaram mais de R$25.000 em transações?


SELECT l.id_lojista,
       l.nome,
       SUM(t.valor) AS total_transacoes
FROM lojistas l 
LEFT JOIN transacoes t
ON l.id_lojista = t.id_lojista
GROUP BY l.id_lojista, l.nome 
HAVING SUM(t.valor) > 25000;

-- Pergunta 11
-- Quantos cartões cada cliente tem? Liste só quem tem mais de um cartão.

SELECT cl.id_cliente,
       cl.nome,
       cl.cpf,
COUNT(cr.id_cartao) AS quantidade_cartoes
FROM clientes cl
LEFT JOIN cartoes cr
ON cl.id_cliente = cr.id_cliente
GROUP BY cl.id_cliente, cl.nome, cl.cpf
HAVING quantidade_cartoes > 1;


-- Pergunta 12
-- Para cada motivo de contestação, quantas contestações existem? Qual motivo
-- é o mais comum?


SELECT motivo,
       COUNT(*) AS quantidade
FROM contestacoes
GROUP BY motivo
ORDER BY quantidade DESC;

-- ============================================================================
-- BLOCO 4: Juntando tabelas
-- ============================================================================
-- Repare numa diferença em relação à Aula 5: ali, transacoes tinha id_cliente
-- direto. Aqui não. A tabela transacoes aponta para id_cartao, e quem aponta
-- para id_cliente é a tabela cartoes. Para chegar do cliente até a transação
-- dele, são dois JOIN em sequência, não um só.

-- Pergunta 13
-- Liste as 10 transações de maior valor, com o número final do cartão usado
-- (final_cartao).

SELECT t.id_transacao,
       t.valor,
       cr.final_cartao
FROM transacoes t
LEFT JOIN cartoes cr
ON cr.id_cartao = t.id_cartao
ORDER BY t.valor DESC
FETCH FIRST 10 ROWS ONLY;

-- Pergunta 14
-- Liste as 10 transações de maior valor, agora com o nome do cliente que fez
-- a compra. Precisa passar por transacoes, cartoes e clientes.

SELECT t.id_transacao,
       t.valor,
       cr.final_cartao,
       cl.id_cliente,
       cl.nome
FROM transacoes t
LEFT JOIN cartoes cr
ON cr.id_cartao = t.id_cartao
LEFT JOIN clientes cl
ON cr.id_cliente = cl.id_cliente
ORDER BY t.valor DESC
FETCH FIRST 10 ROWS ONLY;

-- Pergunta 15
-- Liste os lojistas que nunca receberam nenhuma transação.

SELECT l.id_lojista,
       l.nome,
       t.valor
FROM lojistas l
LEFT JOIN transacoes t
ON l.id_lojista = t.id_lojista
GROUP BY l.id_lojista, l.nome
HAVING SUM(t.valor) IS NULL;

-- Pergunta 16
-- Liste os clientes que nunca fizeram nenhuma transação em nenhum cartão.


SELECT cl.id_cliente,
       cl.nome
FROM clientes cl
LEFT JOIN cartoes cr
ON cr.id_cliente = cl.id_cliente
LEFT JOIN transacoes t
ON t.id_cartao = cr.id_cartao
GROUP BY cl.id_cliente, cl.nome
HAVING SUM(t.valor) IS NULL;

-- ============================================================================
-- DESAFIO: combinando tudo
-- ============================================================================

-- Pergunta 17
-- Para cada categoria de lojista, qual o valor total de transações com status
-- 'contestada'? Ordene da maior categoria para a menor.

SELECT ctg.id_categoria, 
       ctg.nome,
       SUM(t.valor) AS valor_total
FROM categorias ctg
LEFT JOIN lojistas l
ON ctg.id_categoria = l.id_categoria
LEFT JOIN transacoes t
ON t.id_lojista = l.id_lojista
GROUP BY ctg.id_categoria, ctg.nome
HAVING t.status = 'contestada'
ORDER BY valor_total DESC;


-- Pergunta 18
-- Liste os clientes que têm pelo menos uma transação contestada, junto com o
-- motivo e o resultado de cada contestação.

SELECT cl.id_cliente, 
       cl.nome,
       COUNT(con.id_contestacao) AS qntd_contestacao,
       con.motivo,
       con.resultado
FROM contestacoes con
LEFT JOIN transacoes t
ON t.id_transacao = con.id_transacao
LEFT JOIN cartoes cr
ON cr.id_cartao = t.id_cartao
LEFT JOIN clientes cl
ON cl.id_cliente = cr.id_cliente
WHERE t.status = 'contestada'
GROUP BY cl.id_cliente, cl.nome, con.motivo, con.resultado;

-- Respondido por Antonio Mádson Rocha (Grupo 7)