SÉANCE 2 – SQL Analytique avec DuckDB

-- INNER JOIN : Seulement les correspondances
SELECT c.nom, co.montant, co.date
FROM clients c
INNER JOIN commandes co ON c.id = co.client_id;

-- LEFT JOIN : Tous les clients, même sans commande
SELECT c.nom, COUNT(co.id) as nb_commandes
FROM clients c
LEFT JOIN commandes co ON c.id = co.client_id
GROUP BY c.nom;

-- Jointure à 3 tables
SELECT 
    c.nom as client,
    p.nom as produit,
    v.quantite,
    v.montant
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;

-- Agrégation simple
SELECT 
    categorie,
    COUNT(*) as nb_produits,
    AVG(prix) as prix_moyen,
    SUM(stock) as stock_total
FROM produits
GROUP BY categorie;

-- HAVING : Filtrer les groupes
SELECT 
    client_id,
    COUNT(*) as nb_achats,
    SUM(montant) as total_depense
FROM ventes
GROUP BY client_id
HAVING SUM(montant) > 1000
ORDER BY total_depense DESC;

fonction() OVER (
    PARTITION BY colonnes     -- Grouper (optionnel)
    ORDER BY colonnes         -- Trier (optionnel)
    ROWS/RANGE définition     -- Fenêtre (optionnel)
)

-- Numéroter toutes les lignes
SELECT 
    nom,
    prix,
    ROW_NUMBER() OVER (ORDER BY prix DESC) as rang
FROM produits;

SELECT 
    nom,
    score,
    RANK() OVER (ORDER BY score DESC) as rang,
    DENSE_RANK() OVER (ORDER BY score DESC) as rang_dense
FROM etudiants;

-- Top 3 produits par catégorie
SELECT *
FROM (
    SELECT 
        categorie,
        nom,
        prix,
        ROW_NUMBER() OVER (
            PARTITION BY categorie 
            ORDER BY prix DESC
        ) as rang_categorie
    FROM produits
) WHERE rang_categorie <= 3;

-- Comparer avec le mois précédent
SELECT 
    mois,
    ca,
    LAG(ca, 1) OVER (ORDER BY mois) as ca_mois_precedent,
    ca - LAG(ca, 1) OVER (ORDER BY mois) as evolution
FROM ventes_mensuelles;

-- Moyenne mobile sur 3 mois
SELECT 
    mois,
    ca,
    AVG(ca) OVER (
        ORDER BY mois
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moyenne_mobile_3mois
FROM ventes_mensuelles;

-- Avec CTE (lisible)
WITH ventes_client AS (
    SELECT client_id, SUM(montant) as total
    FROM ventes
    GROUP BY client_id
),
clients_premium AS (
    SELECT * 
    FROM ventes_client 
    WHERE total > 1000
)
SELECT * 
FROM clients_premium 
WHERE client_id > 100;

WITH client_stats AS (
    SELECT 
        client_id,
        COUNT(*) as nb_achats,
        SUM(montant) as ca_total,
        AVG(montant) as panier_moyen
    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 * FROM client_segments
ORDER BY ca_total DESC;