Correction Phase 1

SELECT ip_address, 
       COUNT(*) as attempts,
       MIN(attempt_date) as first_attempt,
       MAX(attempt_date) as last_attempt
FROM login_attempts 
WHERE attempt_date >= '2024-03-15 00:00:00' 
  AND success = false
GROUP BY ip_address
HAVING COUNT(*) > 4
ORDER BY attempts DESC;

%%sql
SELECT 
    attempt_date::timestamp::date as date,
    EXTRACT(HOUR FROM attempt_date) as heure,
    EXTRACT(MINUTE FROM attempt_date) as minute,
    COUNT(*) as nb_tentatives
FROM login_attempts
WHERE attempt_date BETWEEN '2024-03-14 00:00:00' AND '2024-03-16 23:59:59'
    AND success = false
GROUP BY 
    attempt_date::timestamp::date,
    EXTRACT(HOUR FROM attempt_date),
    EXTRACT(MINUTE FROM attempt_date)
ORDER BY nb_tentatives DESC;

SELECT
   l1.customer_id,
   l1.attempt_date as first_login,
   l1.ip_address as first_ip,
   l2.attempt_date as second_login,
   l2.ip_address as second_ip,
   l2.attempt_date - l1.attempt_date as time_between_logins
FROM login_attempts l1 JOIN login_attempts l2 ON  l1.customer_id = l2.customer_id
WHERE 
   l1.attempt_date >= '2024-03-15 00:00:00' 
   AND l1.ip_address != l2.ip_address 
   AND l2.attempt_date BETWEEN l1.attempt_date AND l1.attempt_date + INTERVAL '30 minutes'
   AND l1.success = true
   AND l2.success = true
ORDER BY 
   l1.customer_id,
   l1.attempt_date;

SELECT 
    t.transaction_id,
    c.name,
    c.email,
    t.amount,
    t.ip_address,
    t.transaction_date
FROM transactions t
JOIN customers c ON t.customer_id = c.customer_id
WHERE c.email LIKE '%.%.%@%'
    AND t.amount > 1000
ORDER BY t.amount DESC;

SELECT DISTINCT c.customer_id, c.name, c.email
FROM customers c
JOIN transactions t ON t.customer_id = c.customer_id
WHERE t.transaction_date BETWEEN '2024-03-15 01:00:00' AND '2024-03-15 05:00:00'
GROUP BY c.customer_id, c.name, c.email
HAVING MAX(t.amount) < 50
ORDER BY c.customer_id;

WITH stats_par_ville AS (
    SELECT 
        l.city as ville,
        l.country as pays,
        COUNT(*) as nb_transactions,
        COUNT(DISTINCT t.customer_id) as nb_comptes,
        ROUND(AVG(t.amount)::numeric, 2) as montant_moyen,
        COUNT(DISTINCT t.ip_address) as nb_ips,
        ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT t.customer_id), 0), 1) as ratio_tx_compte,
        ROUND(COUNT(DISTINCT t.ip_address)::numeric / NULLIF(COUNT(DISTINCT t.customer_id), 0), 1) as ratio_ip_compte
    FROM transactions t
    JOIN locations l ON t.ip_address LIKE l.ip_range || '%'
    WHERE t.transaction_date >= '2024-03-15'
    GROUP BY l.city, l.country
    HAVING COUNT(*) > 0
)
SELECT 
    ville,
    pays,
    nb_transactions as "nombre de transactions",
    nb_comptes as "nombre de comptes",
    montant_moyen as "montant moyen",
    nb_ips as "nombre d'IPs",
    ratio_tx_compte as "transactions par compte",
    ratio_ip_compte as "ip par compte",
    (   -- Score basé sur le montant
        CASE WHEN montant_moyen > 2000 THEN 40
             WHEN montant_moyen > 1000 THEN 20
             ELSE 0
        END +
        -- Score basé sur les transactions par compte
        CASE WHEN ratio_tx_compte > 8 THEN 15
             WHEN ratio_tx_compte > 2 THEN 5
             ELSE 0
        END +
        -- Score basé sur les IPs par compte
        CASE WHEN ratio_ip_compte > 3 THEN 30
             WHEN ratio_ip_compte > 1 THEN 5
             ELSE 0
        END
    ) as score_risque
FROM stats_par_ville
ORDER BY score_risque DESC;