TESTE PRÁTICO - Análise de Dados (3a Ediçao - Maratona Digital)

PHOTO EMBED

Fri Sep 13 2024 13:33:33 GMT+0000 (Coordinated Universal Time)

Saved by @edsonjorgef1 #sql 

A interoperabilidade no sistema bancário em Moçambique é uma realidade, tendo a SIMO como ponto central de gestão do sistema integrado de transações. Os bancos passaram a receber um conjunto de dados (em ficheiros de texto) transacionados nos seus terminais como POSs (compras) e ATMs (levantamentos/pagamentos) por meios de cartões. Por forma a garantir o melhor controle e rentabilidade (pelo menos uma transação) dos cartões. Considere a estrutura do ficheiro de transações de clientes (Clientes Ativos - 1 transação dentro de 90 dias, Clientes inativos - Transações acima de 90 dias) recebido abaixo:
Estrutura da tabela a considerar:
| Data | Transacao_ID | Terminal_ID | Tipo_Terminal | Valor | Cliente | Idade | Tipo_Cartao | Provincia | Distrito |
|---|---|---|---|---|---|---|---|---|---|
1. Crie um modelo normalizado e os devidos relacionamentos de acordo com a tabela.
2. Com base nas tabelas normalizadas escreva uma query que indique todos os clientes inactivos da província de Gaza, com mais levantamentos.
3. Tendo em consideração que para o banco uma “boa” rentabilidade é ter transações acima de MZN 1000. Indique a província do cliente menos rentável e com mais transações.
4. Ainda sobre a “boa rentabilidade”, indique a província com maior número de clientes activos e menos rentáveis.
5. Continuando sobre a “boa rentabilidade”, indique o cliente ativo mais velho, que realizou mais pagamentos no dia da independência.
  
-- 1. Criação do modelo normalizado

CREATE TABLE Cliente (
    ClienteID INT PRIMARY KEY,
    Nome VARCHAR(100),
    Idade INT
);

CREATE TABLE Cartao (
    CartaoID INT PRIMARY KEY,
    ClienteID INT,
    Tipo_Cartao VARCHAR(50),
    FOREIGN KEY (ClienteID) REFERENCES Cliente(ClienteID)
);

CREATE TABLE Localizacao (
    LocalizacaoID INT PRIMARY KEY,
    Provincia VARCHAR(50),
    Distrito VARCHAR(50)
);

CREATE TABLE Terminal (
    TerminalID VARCHAR(50) PRIMARY KEY,
    Tipo_Terminal VARCHAR(20),
    LocalizacaoID INT,
    FOREIGN KEY (LocalizacaoID) REFERENCES Localizacao(LocalizacaoID)
);

CREATE TABLE Transacao (
    TransacaoID VARCHAR(50) PRIMARY KEY,
    Data DATE,
    Valor DECIMAL(10,2),
    CartaoID INT,
    TerminalID VARCHAR(50),
    FOREIGN KEY (CartaoID) REFERENCES Cartao(CartaoID),
    FOREIGN KEY (TerminalID) REFERENCES Terminal(TerminalID)
);

-- Inserção de dados de exemplo (opcional, para teste)
-- Inserção de dados de exemplo

-- Clientes
INSERT INTO Cliente (ClienteID, Nome, Idade) VALUES
(1, 'João Silva', 35),
(2, 'Maria Santos', 28),
(3, 'Pedro Nunes', 45),
(4, 'Ana Oliveira', 50),
(5, 'Edson Famanda', 29),
(6, 'Luísa Costa', 42),
(7, 'António Mendes', 55),
(8, 'Sofia Rodrigues', 30),
(9, 'Miguel Almeida', 38),
(10, 'Beatriz Sousa', 47);

-- Cartões
INSERT INTO Cartao (CartaoID, ClienteID, Tipo_Cartao) VALUES
(101, 1, 'Débito'),
(102, 2, 'Crédito'),
(103, 3, 'Débito'),
(104, 4, 'Crédito'),
(105, 5, 'Débito'),
(106, 6, 'Crédito'),
(107, 7, 'Débito'),
(108, 8, 'Crédito'),
(109, 9, 'Débito'),
(110, 10, 'Crédito');

-- Localizações
INSERT INTO Localizacao (LocalizacaoID, Provincia, Distrito) VALUES
(201, 'Gaza', 'Xai-Xai'),
(202, 'Maputo', 'Matola'),
(203, 'Sofala', 'Beira'),
(204, 'Nampula', 'Nampula'),
(205, 'Gaza', 'Chibuto'),
(206, 'Inhambane', 'Inhambane'),
(207, 'Tete', 'Tete'),
(208, 'Zambézia', 'Quelimane'),
(209, 'Cabo Delgado', 'Pemba'),
(210, 'Niassa', 'Lichinga');

-- Terminais
INSERT INTO Terminal (TerminalID, Tipo_Terminal, LocalizacaoID) VALUES
('T001', 'ATM', 201),
('T002', 'POS', 202),
('T003', 'ATM', 203),
('T004', 'POS', 204),
('T005', 'ATM', 205),
('T006', 'POS', 206),
('T007', 'ATM', 207),
('T008', 'POS', 208),
('T009', 'ATM', 209),
('T010', 'POS', 210);

-- Transações (50 transações)
INSERT INTO Transacao (TransacaoID, Data, Valor, CartaoID, TerminalID) VALUES
('TR001', '2024-06-25', 500.00, 101, 'T001'),
('TR002', '2024-06-25', 1200.00, 102, 'T002'),
('TR003', '2024-05-15', 800.00, 103, 'T003'),
('TR004', '2024-06-25', 1500.00, 104, 'T004'),
('TR005', '2024-03-01', 300.00, 105, 'T005'),
('TR006', '2024-06-25', 2000.00, 106, 'T006'),
('TR007', '2024-06-01', 100.00, 107, 'T007'),
('TR008', '2024-06-10', 950.00, 108, 'T008'),
('TR009', '2024-06-15', 1100.00, 109, 'T009'),
('TR010', '2024-06-20', 750.00, 110, 'T010'),
('TR011', '2024-06-25', 600.00, 101, 'T001'),
('TR012', '2024-05-30', 1800.00, 102, 'T002'),
('TR013', '2024-04-22', 400.00, 103, 'T003'),
('TR014', '2024-06-25', 2500.00, 104, 'T004'),
('TR015', '2024-02-15', 200.00, 105, 'T005'),
('TR016', '2024-06-25', 3000.00, 106, 'T006'),
('TR017', '2024-05-18', 150.00, 107, 'T007'),
('TR018', '2024-06-05', 1050.00, 108, 'T008'),
('TR019', '2024-06-12', 900.00, 109, 'T009'),
('TR020', '2024-06-19', 1250.00, 110, 'T010'),
('TR021', '2024-06-25', 700.00, 101, 'T001'),
('TR022', '2024-06-02', 1600.00, 102, 'T002'),
('TR023', '2024-05-10', 550.00, 103, 'T003'),
('TR024', '2024-06-25', 2200.00, 104, 'T004'),
('TR025', '2024-01-20', 350.00, 105, 'T005'),
('TR026', '2024-06-25', 2800.00, 106, 'T006'),
('TR027', '2024-04-30', 180.00, 107, 'T007'),
('TR028', '2024-06-08', 1150.00, 108, 'T008'),
('TR029', '2024-06-14', 980.00, 109, 'T009'),
('TR030', '2024-06-22', 1450.00, 110, 'T010'),
('TR031', '2024-06-25', 850.00, 101, 'T001'),
('TR032', '2024-05-28', 2100.00, 102, 'T002'),
('TR033', '2024-04-18', 480.00, 103, 'T003'),
('TR034', '2024-06-25', 3200.00, 104, 'T004'),
('TR035', '2024-02-10', 280.00, 105, 'T005'),
('TR036', '2024-06-25', 3500.00, 106, 'T006'),
('TR037', '2024-05-22', 220.00, 107, 'T007'),
('TR038', '2024-06-03', 1350.00, 108, 'T008'),
('TR039', '2024-06-11', 1020.00, 109, 'T009'),
('TR040', '2024-06-18', 1650.00, 110, 'T010'),
('TR041', '2024-06-25', 920.00, 101, 'T001'),
('TR042', '2024-06-01', 2400.00, 102, 'T002'),
('TR043', '2024-05-08', 630.00, 103, 'T003'),
('TR044', '2024-06-25', 2900.00, 104, 'T004'),
('TR045', '2024-01-15', 380.00, 105, 'T005'),
('TR046', '2024-06-25', 3800.00, 106, 'T006'),
('TR047', '2024-04-25', 250.00, 107, 'T007'),
('TR048', '2024-06-07', 1550.00, 108, 'T008'),
('TR049', '2024-06-13', 1080.00, 109, 'T009'),
('TR050', '2024-06-21', 1850.00, 110, 'T010');

-- 2. Query para clientes inativos da província de Gaza, com mais levantamentos
SELECT c.ClienteID, c.Nome, COUNT(*) as NumLevantamentos
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
JOIN Localizacao l ON te.LocalizacaoID = l.LocalizacaoID
WHERE l.Provincia = 'Gaza'
  AND te.Tipo_Terminal = 'ATM'
  AND t.Data < DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY c.ClienteID, c.Nome
ORDER BY NumLevantamentos DESC;

-- 3. Província do cliente menos rentável e com mais transações
SELECT l.Provincia
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
JOIN Localizacao l ON te.LocalizacaoID = l.LocalizacaoID
GROUP BY l.Provincia
ORDER BY SUM(CASE WHEN t.Valor > 1000 THEN 1 ELSE 0 END) ASC, COUNT(*) DESC
LIMIT 1;

-- 4. Província com maior número de clientes ativos e menos rentáveis
SELECT l.Provincia
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
JOIN Localizacao l ON te.LocalizacaoID = l.LocalizacaoID
WHERE t.Data >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY l.Provincia
ORDER BY COUNT(DISTINCT c.ClienteID) DESC, SUM(CASE WHEN t.Valor <= 1000 THEN 1 ELSE 0 END) DESC
LIMIT 1;

-- 5. Cliente ativo mais velho, com mais pagamentos no dia da independência
SELECT c.ClienteID, c.Nome, c.Idade, COUNT(*) as NumPagamentos
FROM Cliente c
JOIN Cartao ca ON c.ClienteID = ca.ClienteID
JOIN Transacao t ON ca.CartaoID = t.CartaoID
JOIN Terminal te ON t.TerminalID = te.TerminalID
WHERE te.Tipo_Terminal = 'POS'
  AND DAY(t.Data) = 25 AND MONTH(t.Data) = 6
  AND t.Data >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY c.ClienteID, c.Nome, c.Idade
ORDER BY c.Idade DESC, NumPagamentos DESC
LIMIT 1;
content_copyCOPY

https://sqlfiddle.com/mysql/online-compiler?id=d6bf30f4-ccd9-4b1d-bff1-80b81e3f85e8