-- ============================================================================
-- SÉANCE 2 - EXEMPLES ET CORRECTIONS SQL
-- SQL Analytique avec DuckDB
-- ============================================================================

-- ============================================================================
-- EXERCICE 1 : Rappels SQL fondamentaux
-- ============================================================================

-- Créer une table de produits
CREATE TABLE produits (
    id INTEGER PRIMARY KEY,
    nom VARCHAR,
    categorie VARCHAR,
    prix DECIMAL(10,2),
    stock INTEGER,
    date_ajout DATE
);

-- Insérer des données de test
INSERT INTO produits VALUES
(1, 'Laptop Dell XPS 15', 'Informatique', 1299.99, 15, '2024-01-10'),
(2, 'MacBook Pro M3', 'Informatique', 2499.99, 8, '2024-01-15'),
(3, 'Souris Logitech', 'Accessoires', 49.99, 50, '2024-01-12'),
(4, 'Clavier mécanique', 'Accessoires', 149.99, 25, '2024-01-18'),
(5, 'Écran 27 pouces', 'Informatique', 349.99, 20, '2024-01-20'),
(6, 'Bureau ergonomique', 'Mobilier', 449.99, 12, '2024-02-01'),
(7, 'Chaise de bureau', 'Mobilier', 299.99, 18, '2024-02-03'),
(8, 'Webcam HD', 'Accessoires', 79.99, 30, '2024-02-05'),
(9, 'Casque audio', 'Accessoires', 199.99, 22, '2024-02-10'),
(10, 'iPad Pro', 'Informatique', 1099.99, 10, '2024-02-12');

-- Requête simple : Produits > 500€
SELECT * FROM produits WHERE prix > 500 ORDER BY prix DESC;

-- Requête avec BETWEEN
SELECT * FROM produits WHERE prix BETWEEN 100 AND 500;

-- Requête avec LIKE
SELECT * FROM produits WHERE nom LIKE '%Pro%';

-- Requête avec IN
SELECT * FROM produits WHERE categorie IN ('Informatique', 'Accessoires');


-- ============================================================================
-- EXERCICE 2 : Agrégations et GROUP BY
-- ============================================================================

-- Statistiques par catégorie
SELECT 
    categorie,
    COUNT(*) as nb_produits,
    ROUND(AVG(prix), 2) as prix_moyen,
    MIN(prix) as prix_min,
    MAX(prix) as prix_max,
    SUM(stock) as stock_total,
    ROUND(SUM(prix * stock), 2) as valeur_stock
FROM produits
GROUP BY categorie
ORDER BY valeur_stock DESC;

-- HAVING : Catégories avec plus de 2 produits
SELECT 
    categorie,
    COUNT(*) as nb_produits,
    ROUND(AVG(prix), 2) as prix_moyen
FROM produits
GROUP BY categorie
HAVING COUNT(*) > 2;


-- ============================================================================
-- EXERCICE 3 : Manipulation de dates
-- ============================================================================

-- Créer une table de ventes
CREATE TABLE ventes (
    id INTEGER PRIMARY KEY,
    produit_id INTEGER,
    client_id INTEGER,
    quantite INTEGER,
    montant DECIMAL(10,2),
    date TIMESTAMP
);

-- Insérer des ventes sur 6 mois
INSERT INTO ventes VALUES
(1, 1, 101, 2, 2599.98, '2024-01-15 10:30:00'),
(2, 3, 102, 5, 249.95, '2024-01-16 14:20:00'),
(3, 2, 103, 1, 2499.99, '2024-01-20 09:15:00'),
(4, 5, 101, 1, 349.99, '2024-02-05 11:45:00'),
(5, 4, 104, 2, 299.98, '2024-02-10 16:30:00'),
(6, 1, 105, 1, 1299.99, '2024-02-15 10:00:00'),
(7, 7, 102, 1, 299.99, '2024-03-01 13:20:00'),
(8, 8, 103, 3, 239.97, '2024-03-05 15:10:00'),
(9, 6, 106, 2, 899.98, '2024-03-10 09:30:00'),
(10, 9, 101, 1, 199.99, '2024-03-15 14:45:00'),
(11, 1, 107, 2, 2599.98, '2024-04-01 10:20:00'),
(12, 3, 102, 10, 499.90, '2024-04-05 11:30:00'),
(13, 5, 108, 2, 699.98, '2024-04-10 16:15:00'),
(14, 10, 103, 1, 1099.99, '2024-05-02 09:45:00'),
(15, 2, 109, 1, 2499.99, '2024-05-15 13:00:00');

-- Ventes par mois
SELECT 
    DATE_TRUNC('month', date) as mois,
    COUNT(*) as nb_ventes,
    SUM(quantite) as produits_vendus,
    ROUND(SUM(montant), 2) as ca
FROM ventes
GROUP BY DATE_TRUNC('month', date)
ORDER BY mois;

-- Ventes par jour de la semaine
SELECT 
    DAYNAME(date) as jour,
    COUNT(*) as nb_ventes,
    ROUND(AVG(montant), 2) as panier_moyen
FROM ventes
GROUP BY DAYNAME(date), DAYOFWEEK(date)
ORDER BY DAYOFWEEK(date);


-- ============================================================================
-- EXERCICE 4 : Jointures
-- ============================================================================

-- Créer une table clients
CREATE TABLE clients (
    id INTEGER PRIMARY KEY,
    nom VARCHAR,
    email VARCHAR,
    ville VARCHAR,
    date_inscription DATE
);

INSERT INTO clients VALUES
(101, 'Alice Martin', 'alice@email.com', 'Paris', '2023-06-15'),
(102, 'Bob Dupont', 'bob@email.com', 'Lyon', '2023-07-20'),
(103, 'Charlie Lefebvre', 'charlie@email.com', 'Marseille', '2023-08-10'),
(104, 'Diana Roy', 'diana@email.com', 'Paris', '2023-09-05'),
(105, 'Ethan Moreau', 'ethan@email.com', 'Lyon', '2023-10-12'),
(106, 'Fanny Petit', 'fanny@email.com', 'Toulouse', '2023-11-01'),
(107, 'Gabriel Simon', 'gabriel@email.com', 'Paris', '2023-12-15'),
(108, 'Hannah Laurent', 'hannah@email.com', 'Nice', '2024-01-20'),
(109, 'Ivan Blanc', 'ivan@email.com', 'Bordeaux', '2024-02-10'),
(110, 'Julie Martin', 'julie@email.com', 'Paris', '2024-03-05');

-- INNER JOIN : Clients ayant fait au moins un achat
SELECT 
    c.nom,
    c.ville,
    COUNT(v.id) as nb_achats,
    ROUND(SUM(v.montant), 2) as ca_total
FROM clients c
INNER JOIN ventes v ON c.id = v.client_id
GROUP BY c.nom, c.ville
ORDER BY ca_total DESC;

-- LEFT JOIN : Tous les clients, même sans achat
SELECT 
    c.nom,
    c.ville,
    COUNT(v.id) as nb_achats,
    COALESCE(SUM(v.montant), 0) as ca_total
FROM clients c
LEFT JOIN ventes v ON c.id = v.client_id
GROUP BY c.nom, c.ville
ORDER BY ca_total DESC;

-- Jointure à 3 tables
SELECT 
    c.nom as client,
    p.nom as produit,
    p.categorie,
    v.quantite,
    v.montant,
    v.date
FROM ventes v
INNER JOIN clients c ON v.client_id = c.id
INNER JOIN produits p ON v.produit_id = p.id
ORDER BY v.date DESC;


-- ============================================================================
-- EXERCICE 5 : CTE (Common Table Expressions)
-- ============================================================================

-- Analyse avec CTE : Clients VIP
WITH client_stats AS (
    SELECT 
        client_id,
        COUNT(*) as nb_achats,
        SUM(montant) as ca_total,
        AVG(montant) as panier_moyen,
        MAX(date) as dernier_achat
    FROM ventes
    GROUP BY client_id
),
client_segments AS (
    SELECT 
        client_id,
        ca_total,
        CASE 
            WHEN ca_total > 2000 THEN 'VIP'
            WHEN ca_total > 1000 THEN 'Premium'
            ELSE 'Standard'
        END as segment
    FROM client_stats
)
SELECT 
    c.nom,
    c.ville,
    cs.segment,
    ROUND(st.ca_total, 2) as ca_total,
    st.nb_achats,
    ROUND(st.panier_moyen, 2) as panier_moyen
FROM client_segments cs
INNER JOIN clients c ON cs.client_id = c.id
INNER JOIN client_stats st ON cs.client_id = st.client_id
ORDER BY st.ca_total DESC;


-- ============================================================================
-- EXERCICE 6 : Window Functions (Fonctions de fenêtrage)
-- ============================================================================

-- ROW_NUMBER : Numéroter les ventes
SELECT 
    ROW_NUMBER() OVER (ORDER BY montant DESC) as rang,
    date,
    montant,
    client_id
FROM ventes
ORDER BY rang;

-- RANK par catégorie de produit
SELECT 
    p.nom,
    p.categorie,
    p.prix,
    RANK() OVER (PARTITION BY p.categorie ORDER BY p.prix DESC) as rang_categorie
FROM produits p
ORDER BY p.categorie, rang_categorie;

-- LAG : Comparer avec la vente précédente
SELECT 
    date,
    montant,
    LAG(montant, 1) OVER (ORDER BY date) as montant_precedent,
    montant - LAG(montant, 1) OVER (ORDER BY date) as difference
FROM ventes
ORDER BY date;

-- Moyenne mobile sur 3 ventes
SELECT 
    date,
    montant,
    ROUND(AVG(montant) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) as moyenne_mobile
FROM ventes
ORDER BY date;

-- Cumul des ventes
SELECT 
    DATE_TRUNC('day', date) as jour,
    SUM(montant) as ca_jour,
    ROUND(SUM(SUM(montant)) OVER (ORDER BY DATE_TRUNC('day', date)), 2) as ca_cumule
FROM ventes
GROUP BY DATE_TRUNC('day', date)
ORDER BY jour;


-- ============================================================================
-- EXERCICE 7 : Analyse avancée - Cohortes
-- ============================================================================

-- Analyse de cohorte : Rétention clients
WITH premiere_commande AS (
    SELECT 
        client_id,
        DATE_TRUNC('month', MIN(date)) as mois_acquisition
    FROM ventes
    GROUP BY client_id
),
activite_mensuelle AS (
    SELECT 
        client_id,
        DATE_TRUNC('month', date) as mois_activite
    FROM ventes
    GROUP BY client_id, DATE_TRUNC('month', date)
)
SELECT 
    pc.mois_acquisition,
    am.mois_activite,
    DATEDIFF('month', pc.mois_acquisition, am.mois_activite) as mois_depuis_acquisition,
    COUNT(DISTINCT am.client_id) as clients_actifs
FROM premiere_commande pc
INNER JOIN activite_mensuelle am ON pc.client_id = am.client_id
GROUP BY pc.mois_acquisition, am.mois_activite
ORDER BY pc.mois_acquisition, am.mois_activite;


-- ============================================================================
-- EXERCICE 8 : Analyse ABC des produits
-- ============================================================================

WITH produit_ca AS (
    SELECT 
        p.id,
        p.nom,
        p.categorie,
        COALESCE(SUM(v.montant), 0) as ca
    FROM produits p
    LEFT JOIN ventes v ON p.id = v.produit_id
    GROUP BY p.id, p.nom, p.categorie
),
produit_cumul AS (
    SELECT 
        *,
        SUM(ca) OVER (ORDER BY ca DESC) as ca_cumule,
        SUM(ca) OVER () as ca_total
    FROM produit_ca
)
SELECT 
    nom,
    categorie,
    ROUND(ca, 2) as ca,
    ROUND(100.0 * ca / ca_total, 2) as pct_ca,
    ROUND(100.0 * ca_cumule / ca_total, 2) as pct_cumule,
    CASE 
        WHEN 100.0 * ca_cumule / ca_total <= 80 THEN 'A'
        WHEN 100.0 * ca_cumule / ca_total <= 95 THEN 'B'
        ELSE 'C'
    END as classe_abc
FROM produit_cumul
WHERE ca > 0
ORDER BY ca DESC;


-- ============================================================================
-- EXERCICE 9 : Requêtes d'optimisation
-- ============================================================================

-- Performance par ville
SELECT 
    c.ville,
    COUNT(DISTINCT c.id) as nb_clients,
    COUNT(v.id) as nb_ventes,
    ROUND(SUM(v.montant), 2) as ca_total,
    ROUND(AVG(v.montant), 2) as panier_moyen
FROM clients c
LEFT JOIN ventes v ON c.id = v.client_id
GROUP BY c.ville
ORDER BY ca_total DESC;

-- Top 5 clients par CA
SELECT 
    c.nom,
    c.ville,
    COUNT(v.id) as nb_achats,
    ROUND(SUM(v.montant), 2) as ca_total,
    ROUND(AVG(v.montant), 2) as panier_moyen
FROM clients c
INNER JOIN ventes v ON c.id = v.client_id
GROUP BY c.nom, c.ville
ORDER BY ca_total DESC
LIMIT 5;


-- ============================================================================
-- EXERCICE 10 : Cas pratique complet
-- ============================================================================

-- Dashboard complet : Vue d'ensemble business
WITH kpis_globaux AS (
    SELECT 
        COUNT(DISTINCT client_id) as nb_clients_actifs,
        COUNT(*) as nb_transactions,
        SUM(quantite) as produits_vendus,
        ROUND(SUM(montant), 2) as ca_total,
        ROUND(AVG(montant), 2) as panier_moyen
    FROM ventes
),
top_produits AS (
    SELECT 
        p.nom,
        COUNT(v.id) as nb_ventes,
        ROUND(SUM(v.montant), 2) as ca
    FROM ventes v
    INNER JOIN produits p ON v.produit_id = p.id
    GROUP BY p.nom
    ORDER BY ca DESC
    LIMIT 5
),
performance_mensuelle AS (
    SELECT 
        DATE_TRUNC('month', date) as mois,
        COUNT(*) as nb_ventes,
        ROUND(SUM(montant), 2) as ca
    FROM ventes
    GROUP BY DATE_TRUNC('month', date)
    ORDER BY mois
)
-- Afficher les KPIs
SELECT * FROM kpis_globaux;

-- Top produits
SELECT * FROM top_produits;

-- Performance mensuelle
SELECT * FROM performance_mensuelle;


-- ============================================================================
-- BONUS : Requêtes utiles pour l'export vers Python
-- ============================================================================

-- Préparer les données pour analyse Python
CREATE VIEW analyse_ventes AS
SELECT 
    v.id,
    v.date,
    DATE_TRUNC('month', v.date) as mois,
    DAYNAME(v.date) as jour_semaine,
    c.id as client_id,
    c.nom as client_nom,
    c.ville as client_ville,
    p.id as produit_id,
    p.nom as produit_nom,
    p.categorie as produit_categorie,
    v.quantite,
    v.montant,
    p.prix as prix_unitaire
FROM ventes v
INNER JOIN clients c ON v.client_id = c.id
INNER JOIN produits p ON v.produit_id = p.id;

-- Utilisation de la vue
SELECT * FROM analyse_ventes ORDER BY date DESC LIMIT 10;

