Réalisations

-- Comptes qui ont reçu plus de 10 000 euros sur les 7 derniers jours
SELECT o.id_compte,
       COUNT(*) AS nb_entrees,
       SUM(o.montant) AS total_recu
FROM demo.operations o
WHERE o.sens = 'entrant'
  AND o.date_operation >= CURRENT_DATE - 7
GROUP BY o.id_compte
HAVING SUM(o.montant) > 10000
ORDER BY total_recu DESC;

-- Comptes connectés depuis au moins 3 pays différents en 24 h
SELECT id_compte,
       COUNT(DISTINCT pays_ip) AS nb_pays,
       COUNT(*) AS nb_connexions
FROM demo.connexions
WHERE date_connexion >= CURRENT_DATE - 1
GROUP BY id_compte
HAVING COUNT(DISTINCT pays_ip) >= 3
ORDER BY nb_pays DESC;

-- IBAN qui ont reçu des virements d'au moins 4 comptes différents
SELECT o.iban_masque AS beneficiaire,
       COUNT(DISTINCT o.id_compte) AS nb_emetteurs,
       SUM(o.montant) AS total_recu
FROM demo.operations o
WHERE o.type_operation = 'virement_sortant'
  AND o.date_operation >= CURRENT_DATE - 30
GROUP BY o.iban_masque
HAVING COUNT(DISTINCT o.id_compte) >= 4
ORDER BY nb_emetteurs DESC;

-- Alertes par semaine, avec les fraudes et les fausses alertes
SELECT a.semaine,
       COUNT(*) AS alertes,
       SUM(CASE WHEN a.statut = 'fraude' THEN 1 ELSE 0 END) AS fraudes,
       SUM(CASE WHEN a.statut = 'faux_positif' THEN 1 ELSE 0 END) AS fausses_alertes
FROM demo.alertes a
GROUP BY a.semaine
ORDER BY a.semaine;

-- Comptes fermés pour fraude en 2026, par pays de domiciliation
SELECT c.pays_domiciliation AS pays,
       COUNT(*) AS comptes_fermes,
       SUM(c.montant_fraude) AS montant_fraude
FROM demo.comptes c
WHERE c.motif_fermeture = 'fraude'
  AND c.date_fermeture >= '2026-01-01'
GROUP BY c.pays_domiciliation
ORDER BY comptes_fermes DESC
LIMIT 6;