SQL for Data Engineers

# Installation de DuckDB
!pip install duckdb --quiet

print("✅ DuckDB installé !")

import duckdb

# Créer une connexion en mémoire
con = duckdb.connect(database=':memory:')

print("✅ Connexion DuckDB créée !")
print(f"Version : {duckdb.__version__}")

# Création des tables de démonstration

# Table clients
con.execute("""
CREATE TABLE clients (
    id_client INTEGER PRIMARY KEY,
    nom VARCHAR(50),
    email VARCHAR(100),
    pays VARCHAR(50),
    date_inscription DATE
);
""")

con.execute("""
INSERT INTO clients VALUES
    (1, 'Alice', 'alice@mail.com', 'France', '2023-01-15'),
    (2, 'Bob', 'bob@mail.com', 'France', '2023-02-20'),
    (3, 'Charlie', 'charlie@mail.com', 'Allemagne', '2023-03-10'),
    (4, 'Diana', 'diana@mail.com', 'Belgique', '2023-04-05'),
    (5, 'Eve', 'eve@mail.com', 'France', '2023-05-12');
""")

# Table commandes
con.execute("""
CREATE TABLE commandes (
    id_commande INTEGER PRIMARY KEY,
    id_client INTEGER,
    produit VARCHAR(50),
    montant DECIMAL(10,2),
    date_commande DATE,
    FOREIGN KEY (id_client) REFERENCES clients(id_client)
);
""")

con.execute("""
INSERT INTO commandes VALUES
    (1, 1, 'Clavier', 50.00, '2023-07-12'),
    (2, 1, 'Souris', 25.00, '2023-07-15'),
    (3, 2, 'Écran', 120.00, '2023-08-01'),
    (4, 1, 'Webcam', 45.00, '2023-08-20'),
    (5, 4, 'Casque', 80.00, '2023-09-05'),
    (6, 2, 'Clavier', 55.00, '2023-09-15'),
    (7, 5, 'Souris', 30.00, '2023-10-01'),
    (8, 4, 'Écran', 150.00, '2023-10-20');
""")

print("✅ Tables créées : clients (5 lignes), commandes (8 lignes)")

# Vérifier les données
print(" Table clients :")
print(con.execute("SELECT * FROM clients").fetchdf())

print("\n Table commandes :")
print(con.execute("SELECT * FROM commandes").fetchdf())

# SELECT * — Toutes les colonnes
query = "SELECT * FROM clients"
con.execute(query).fetchdf()

# SELECT colonnes spécifiques
query = "SELECT nom, email, pays FROM clients"
con.execute(query).fetchdf()

# WHERE — Filtrer les lignes
query = """
SELECT nom, email, pays
FROM clients
WHERE pays = 'France'
"""
con.execute(query).fetchdf()

# ORDER BY + LIMIT
query = """
SELECT nom, montant, date_commande
FROM commandes
JOIN clients ON clients.id_client = commandes.id_client
ORDER BY montant DESC
LIMIT 3
"""
print(" Top 3 des commandes les plus chères :")
con.execute(query).fetchdf()

# COUNT — Compter les lignes
query = "SELECT COUNT(*) AS nb_clients FROM clients"
con.execute(query).fetchdf()

# SUM, AVG, MIN, MAX
query = """
SELECT 
    COUNT(*) AS nb_commandes,
    SUM(montant) AS total,
    AVG(montant) AS moyenne,
    MIN(montant) AS min,
    MAX(montant) AS max
FROM commandes
"""
con.execute(query).fetchdf()

# GROUP BY — Nombre de clients par pays
query = """
SELECT pays, COUNT(*) AS nb_clients
FROM clients
GROUP BY pays
ORDER BY nb_clients DESC
"""
con.execute(query).fetchdf()

# Total des achats par client
query = """
SELECT c.nom, SUM(cmd.montant) AS total_achats
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
GROUP BY c.nom
ORDER BY total_achats DESC
"""
con.execute(query).fetchdf()

# HAVING — Filtrer après agrégation
# Clients ayant dépensé plus de 100€
query = """
SELECT c.nom, SUM(cmd.montant) AS total_achats
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
GROUP BY c.nom
HAVING SUM(cmd.montant) > 100
ORDER BY total_achats DESC
"""
print("🏆 Clients ayant dépensé plus de 100€ :")
con.execute(query).fetchdf()

# INNER JOIN — Clients avec leurs commandes
query = """
SELECT c.nom, cmd.produit, cmd.montant
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
ORDER BY c.nom
"""
con.execute(query).fetchdf()

# LEFT JOIN — Tous les clients, même sans commande
query = """
SELECT c.nom, cmd.produit, cmd.montant
FROM clients c
LEFT JOIN commandes cmd ON c.id_client = cmd.id_client
ORDER BY c.nom
"""
print("👀 Remarque : Charlie n'a pas de commande (NULL)")
con.execute(query).fetchdf()

# Trouver les clients sans commande
query = """
SELECT c.nom, c.email
FROM clients c
LEFT JOIN commandes cmd ON c.id_client = cmd.id_client
WHERE cmd.id_commande IS NULL
"""
print("😴 Clients sans aucune commande :")
con.execute(query).fetchdf()

# Ventes par mois
query = """
SELECT 
    DATE_TRUNC('month', date_commande) AS mois,
    SUM(montant) AS total_ventes,
    COUNT(*) AS nb_commandes
FROM commandes
GROUP BY DATE_TRUNC('month', date_commande)
ORDER BY mois
"""
print(" Ventes mensuelles :")
con.execute(query).fetchdf()

# Commandes par jour de la semaine
query = """
SELECT 
    EXTRACT(DOW FROM date_commande) AS jour_num,
    CASE EXTRACT(DOW FROM date_commande)
        WHEN 0 THEN 'Dimanche'
        WHEN 1 THEN 'Lundi'
        WHEN 2 THEN 'Mardi'
        WHEN 3 THEN 'Mercredi'
        WHEN 4 THEN 'Jeudi'
        WHEN 5 THEN 'Vendredi'
        WHEN 6 THEN 'Samedi'
    END AS jour,
    COUNT(*) AS nb_commandes
FROM commandes
GROUP BY EXTRACT(DOW FROM date_commande)
ORDER BY jour_num
"""
con.execute(query).fetchdf()

# CASE — Catégoriser les clients
query = """
SELECT 
    c.nom,
    SUM(cmd.montant) AS total_achats,
    CASE
        WHEN SUM(cmd.montant) >= 150 THEN '🥇 Premium'
        WHEN SUM(cmd.montant) >= 100 THEN '🥈 Standard'
        ELSE '🥉 Basique'
    END AS categorie
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
GROUP BY c.nom
ORDER BY total_achats DESC
"""
con.execute(query).fetchdf()

# CTE — Calcul intermédiaire réutilisable
query = """
WITH total_par_client AS (
    SELECT 
        c.id_client,
        c.nom,
        c.pays,
        SUM(cmd.montant) AS total
    FROM clients c
    JOIN commandes cmd ON c.id_client = cmd.id_client
    GROUP BY c.id_client, c.nom, c.pays
)
SELECT 
    nom,
    pays,
    total,
    CASE WHEN total > 100 THEN '✅ VIP' ELSE '❌' END AS vip
FROM total_par_client
ORDER BY total DESC
"""
con.execute(query).fetchdf()

# RANK — Classement des clients par total d'achats
query = """
SELECT 
    c.nom,
    SUM(cmd.montant) AS total_achats,
    RANK() OVER (ORDER BY SUM(cmd.montant) DESC) AS rang
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
GROUP BY c.nom
"""
con.execute(query).fetchdf()

# SUM OVER — Total cumulatif
query = """
SELECT 
    date_commande,
    montant,
    SUM(montant) OVER (ORDER BY date_commande) AS cumul
FROM commandes
ORDER BY date_commande
"""
print(" Évolution cumulative des ventes :")
con.execute(query).fetchdf()

# LAG — Comparer avec la période précédente
query = """
WITH mensuel AS (
    SELECT 
        DATE_TRUNC('month', date_commande) AS mois,
        SUM(montant) AS total
    FROM commandes
    GROUP BY DATE_TRUNC('month', date_commande)
)
SELECT 
    mois,
    total,
    LAG(total) OVER (ORDER BY mois) AS total_precedent,
    total - LAG(total) OVER (ORDER BY mois) AS evolution
FROM mensuel
ORDER BY mois
"""
print(" Évolution mensuelle :")
con.execute(query).fetchdf()

import pandas as pd

# Récupérer le résultat en DataFrame Pandas
query = """
SELECT c.nom, c.pays, SUM(cmd.montant) AS total
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
GROUP BY c.nom, c.pays
ORDER BY total DESC
"""

df = con.execute(query).fetchdf()
print(type(df))  # C'est un DataFrame Pandas !
df

# Requêter directement un DataFrame Pandas avec SQL !
df_exemple = pd.DataFrame({
    'produit': ['A', 'B', 'C', 'A', 'B'],
    'ventes': [100, 200, 150, 120, 180]
})

# DuckDB peut requêter le DataFrame directement
result = duckdb.query("""
    SELECT produit, SUM(ventes) AS total
    FROM df_exemple
    GROUP BY produit
    ORDER BY total DESC
""").fetchdf()

print("SQL directement sur un DataFrame Pandas :")
result

# DuckDB peut aussi lire directement des fichiers CSV/Parquet
# Exemple (si tu as un fichier) :
# duckdb.query("SELECT * FROM 'mon_fichier.csv' LIMIT 10")
# duckdb.query("SELECT * FROM 'data.parquet' WHERE date > '2024-01-01'")

print("💡 DuckDB peut lire directement :")
print("   - CSV  : SELECT * FROM 'fichier.csv'")
print("   - Parquet : SELECT * FROM 'fichier.parquet'")
print("   - JSON : SELECT * FROM 'fichier.json'")

# ✏️ Espace pour tes exercices
query = """
-- Écris ta requête ici
SELECT * FROM clients LIMIT 5
"""
con.execute(query).fetchdf()