Guide pratique · E-commerce · SaaS · RH · Finance · Lecture : ~20 min Teste ton niveau sur modai-lab.com/test-sql/
La réalité des données en entreprise
En cours, les datasets sont parfaits. En entreprise, c'est le chaos.
Des NULL partout. Des espaces cachés dans les chaînes. Des dates mal formatées. Des doublons qui faussent tout. Et personne ne te prévient.
Un analyst qui sait nettoyer la donnée vaut 10 fois celui qui sait juste l'agréger.
Ce guide couvre :
Les 14 fonctions expliquées avec des exemples réels
Les combinaisons les plus puissantes entre elles
Des cas business : e-commerce, SaaS, RH, finance
Un prompt Claude pour auditer la qualité de tes données en 2 minutes
🧪 Calibre ton niveau avant de commencer
Sais-tu déjà expliquer la différence entre COALESCE() et NULLIF() ? Fais le test en 5 minutes et reçois ton score instantanément.
👉 Faire le test sur modai-lab.com/test-sql/
_Résultat en direct · Niveau calibré · Parcours personnalisé_
BLOC 1 — Gérer les NULL
Fonction 1 — COALESCE()
Ce qu'elle fait : retourne la première valeur non-NULL dans une liste d'arguments.
-- Syntaxe
COALESCE(valeur1, valeur2, ..., valeur_par_defaut)
-- Exemples de base
SELECT COALESCE(telephone, 'Non renseigné') FROM users;
-- Si telephone = NULL → 'Non renseigné'
-- Si telephone = '06...' → '06...'
-- Avec plusieurs fallbacks
SELECT COALESCE(mobile, telephone_fixe, telephone_pro, 'Aucun contact') AS contact
FROM contacts;
-- Essaie mobile, si NULL → fixe, si NULL → pro, si NULL → 'Aucun contact'
-- Dans une agrégation pour éviter qu'un NULL fausse un SUM
SELECT SUM(COALESCE(remise, )) AS total_remises FROM orders;
-- Dans un calcul
SELECT
prix - COALESCE(remise, ) AS prix_final,
quantite * COALESCE(prix_unitaire, prix_catalogue) AS total_ligne
FROM order_lines;
Cas business — E-commerce : prix de fallback
-- Un produit peut avoir un prix promo, un prix membre, ou le prix public
SELECT
produit_id,
nom,
COALESCE(prix_promo, prix_membre, prix_public) AS prix_affiché,
CASE
WHEN prix_promo IS NOT NULL THEN 'Promotion'
WHEN prix_membre IS NOT NULL THEN 'Tarif membre'
ELSE 'Prix public'
END AS type_tarif
FROM produits;
Fonction 2 — NULLIF()
Ce qu'elle fait : retourne NULL si les deux arguments sont égaux, sinon retourne le premier argument.
-- Syntaxe
NULLIF(valeur, valeur_a_remplacer_par_null)
-- Usage principal : éviter la division par zéro
SELECT
montant_ventes / NULLIF(nombre_transactions, ) AS panier_moyen
FROM stats;
-- Si nombre_transactions = 0 → NULL au lieu d'une erreur de division
-- Nettoyer des valeurs "vides" qui ne sont pas NULL
SELECT NULLIF(commentaire, '') AS commentaire_propre FROM reviews;
-- '' et NULL traitées pareillement ensuite
SELECT NULLIF(TRIM(code_postal), '') AS code_postal FROM adresses;
-- ' ' (espaces) → TRIM → '' → NULLIF → NULL
-- Combiner avec COALESCE
SELECT COALESCE(NULLIF(TRIM(region), ''), 'Non renseignée') AS region
FROM users;
-- Gère NULL, chaîne vide, et chaîne avec espaces en une fois
Cas business — Finance : éviter les divisions par zéro dans les KPIs
SELECT
departement,
budget_alloue,
budget_consomme,
ROUND(. * budget_consomme / NULLIF(budget_alloue, ), ) AS taux_consommation_pct,
budget_alloue - budget_consomme AS budget_restant
FROM budgets
ORDER BY taux_consommation_pct DESC NULLS LAST;
Fonction 3 — IS NULL / IS NOT NULL
Ce qu'elles font : filtrer proprement les valeurs manquantes. La seule façon correcte de tester un NULL.
-- ❌ Ne fonctionne jamais — NULL = NULL est toujours UNKNOWN
SELECT * FROM users WHERE telephone = NULL;
SELECT * FROM users WHERE telephone != NULL;
-- ✅ La seule syntaxe correcte
SELECT * FROM users WHERE telephone IS NULL;
SELECT * FROM users WHERE telephone IS NOT NULL;
-- Compter les valeurs manquantes par colonne
SELECT
COUNT(*) AS total,
COUNT(*) FILTER (WHERE telephone IS NULL) AS tel_manquant,
COUNT(*) FILTER (WHERE email IS NULL) AS email_manquant,
COUNT(*) FILTER (WHERE adresse IS NULL) AS adresse_manquante,
ROUND(. * COUNT(*) FILTER (WHERE telephone IS NULL) / COUNT(*), ) AS pct_tel_null
FROM users;
-- Trier : NULL en dernier
SELECT nom, date_livraison FROM orders
ORDER BY date_livraison ASC NULLS LAST;
-- Sans NULLS LAST : les NULL apparaissent en premier dans ORDER BY ASC
IS DISTINCT FROM — tester l'égalité en gérant les NULL
-- IS DISTINCT FROM : NULL-safe equality (NULL = NULL → TRUE)
-- Utile pour détecter les changements dans les données historiques
SELECT *
FROM users_historique h
JOIN users u ON h.id = u.id
WHERE h.email IS DISTINCT FROM u.email -- détecte aussi les changements NULL → valeur
OR h.telephone IS DISTINCT FROM u.telephone;
🧪 Teste la gestion des NULL sur des données réelles
COALESCE, NULLIF, IS NULL : les 3 premiers sujets testés quand les données sont sales. Modai Lab te soumet des datasets avec 30% de NULL — score immédiat.
_Exercices interactifs · Données réelles · Feedback instantané_
BLOC 2 — Nettoyer les chaînes de caractères
Fonction 4 — TRIM() / LTRIM() / RTRIM()
Ce qu'elles font : supprimer les espaces (ou d'autres caractères) en début et fin de chaîne.
-- Les espaces invisibles qui cassent tout
-- ' Alice ' ≠ 'Alice' dans une comparaison
-- TRIM : supprime de gauche et droite
SELECT TRIM(' Alice '); -- → 'Alice'
-- LTRIM / RTRIM : supprime d'un côté seulement
SELECT LTRIM(' Alice '); -- → 'Alice '
SELECT RTRIM(' Alice '); -- → ' Alice'
-- Supprimer un caractère spécifique (PostgreSQL)
SELECT TRIM(BOTH ',' FROM ',Alice,'); -- → 'Alice'
SELECT TRIM(LEADING '0' FROM '007'); -- → '7'
-- Usage typique : nettoyer les imports CSV
UPDATE users
SET
email = TRIM(LOWER(email)),
nom = TRIM(nom),
code_postal = TRIM(code_postal)
WHERE email LIKE '% %' OR nom LIKE ' %' OR nom LIKE '% ';
Piège fréquent :
-- ❌ Cette comparaison rate les valeurs avec espaces
SELECT * FROM users WHERE email = 'alice@example.com';
-- 'alice@example.com ' (avec espace final) ne matche pas
-- ✅ Toujours TRIM avant de comparer sur des imports
SELECT * FROM users WHERE TRIM(email) = 'alice@example.com';
-- ✅ Encore mieux : nettoyer à l'import, pas à chaque requête
CREATE INDEX idx_email_trim ON users (TRIM(email));
Fonction 5 — UPPER() / LOWER()
Ce qu'elles font : harmoniser la casse pour éviter les doublons invisibles.
-- Problème : 'France', 'FRANCE', 'france' sont traités comme 3 valeurs différentes
SELECT pays, COUNT(*) FROM users GROUP BY pays;
-- France | 450
-- FRANCE | 12 ← import mal normalisé
-- france | 8 ← saisie manuelle
-- ✅ Normaliser avant d'agréger
SELECT LOWER(pays) AS pays_normalise, COUNT(*) AS nb
FROM users
GROUP BY LOWER(pays)
ORDER BY nb DESC;
-- Normaliser les emails (toujours en minuscules)
UPDATE users SET email = LOWER(TRIM(email));
-- Normalisation complète d'un nom : première lettre en majuscule
SELECT INITCAP(LOWER(nom)) AS nom_normalise FROM users;
-- 'ALICE DUPONT' → 'Alice Dupont'
-- 'alice dupont' → 'Alice Dupont'
Fonction 6 — REPLACE()
Ce qu'elle fait : remplacer toutes les occurrences d'une sous-chaîne par une autre.
-- Syntaxe
REPLACE(texte, chercher, remplacer)
-- Nettoyer des caractères parasites
SELECT REPLACE(telephone, ' ', '') AS tel_sans_espaces FROM contacts;
-- '06 12 34 56 78' → '0612345678'
SELECT REPLACE(REPLACE(telephone, '-', ''), '.', '') AS tel_propre FROM contacts;
-- '06-12.34.56.78' → '0612345678'
-- Corriger des valeurs mal saisies
UPDATE produits SET categorie = REPLACE(categorie, 'Electronique', 'Électronique');
-- Nettoyer des imports avec virgules dans les montants
SELECT CAST(REPLACE(montant_csv, ',', '.') AS DECIMAL(,)) AS montant
FROM imports_csv;
-- '1,234.56' → '1.234.56' → attention à l'ordre selon la locale
Combiner REPLACE et REGEXP_REPLACE pour les cas complexes
-- Supprimer tout ce qui n'est pas un chiffre dans un numéro de téléphone
SELECT REGEXP_REPLACE(telephone, '[^0-9]', '', 'g') AS tel_chiffres
FROM contacts;
-- '+33 (0)6-12.34.56.78' → '330612345678'
-- Normaliser les codes postaux français (5 chiffres)
SELECT
LPAD(REGEXP_REPLACE(code_postal, '[^0-9]', '', 'g'), , '0') AS code_postal_propre
FROM adresses;
-- '750' → '00750' (si le 0 initial a été supprimé à l'import)
Fonction 7 — SUBSTRING() / SUBSTR()
Ce qu'elle fait : extraire une partie d'une chaîne à partir d'une position.
-- Syntaxe
SUBSTRING(texte FROM position FOR longueur) -- PostgreSQL
SUBSTR(texte, position, longueur) -- MySQL, Oracle, standard
-- Extraire les 3 premières lettres d'un code produit
SELECT SUBSTRING(code_produit FROM FOR ) AS famille_produit FROM produits;
-- 'ELC-001' → 'ELC'
-- Extraire le domaine d'un email
SELECT
email,
SUBSTRING(email FROM POSITION('@' IN email) + ) AS domaine
FROM users;
-- 'alice@example.com' → 'example.com'
-- Extraire l'année d'une date stockée en VARCHAR (à éviter mais parfois nécessaire)
SELECT SUBSTRING(date_str FROM FOR ) AS annee FROM imports;
-- '2024-11-03' → '2024'
Avec des expressions régulières (PostgreSQL)
-- Extraire un numéro de commande d'un texte libre
SELECT REGEXP_MATCH(description, 'CMD-[0-9]{6}') AS numero_commande
FROM tickets_support;
-- Valider le format d'un email avant insertion
SELECT email
FROM users
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
-- Retourne les emails qui ne respectent pas le format standard
Fonction 8 — CONCAT() / ||
Ce qu'elle fait : assembler plusieurs chaînes ou champs en une seule valeur.
-- Syntaxe
CONCAT(valeur1, valeur2, ...) -- gère les NULL (les ignore ou les traite comme '')
valeur1 || valeur2 -- opérateur de concaténation (NULL + quoi que ce soit = NULL)
-- Assembler prénom et nom
SELECT CONCAT(prenom, ' ', nom) AS nom_complet FROM users;
-- Si prenom = NULL → 'NULL Alice' avec ||, mais ' Alice' avec CONCAT
-- CONCAT_WS : concaténer avec séparateur (ignore les NULL proprement)
SELECT CONCAT_WS(', ', rue, ville, code_postal, pays) AS adresse_complete
FROM adresses;
-- Si ville = NULL → 'Rue de la Paix, 75001, France' (NULL ignoré avec son séparateur)
-- Construire des clés composites
SELECT CONCAT(annee, '-', LPAD(mois::TEXT, , '0')) AS cle_mois FROM calendrier;
-- 2024, 3 → '2024-03'
-- Générer des labels dynamiques
SELECT
produit_id,
CONCAT(categorie, ' · ', sous_categorie, ' · ', UPPER(LEFT(nom, ))) AS breadcrumb
FROM produits;
🧪 Teste le nettoyage de chaînes sur des imports réels
TRIM, LOWER, REPLACE, CONCAT : les 4 fonctions du chaos des imports CSV. Modai Lab te soumet des données avec des espaces cachés, des casses mixtes, des caractères parasites.
👉 Tester sur modai-lab.com/test-sql/
_Score immédiat · Explication des erreurs · Niveau calibré_
BLOC 3 — Corriger les dates
Fonction 9 — CAST() / TRY_CAST()
Ce qu'elle fait : convertir un type de données en un autre — notamment VARCHAR → DATE.
-- Syntaxe
CAST(valeur AS type)
valeur::type -- raccourci PostgreSQL
-- Convertir un VARCHAR en DATE
SELECT CAST('2024-11-03' AS DATE);
SELECT '2024-11-03'::DATE; -- PostgreSQL
-- Convertir avec gestion des erreurs (Snowflake, SQL Server)
SELECT TRY_CAST('2024-13-45' AS DATE); -- retourne NULL au lieu d'une erreur
-- Détecter les valeurs inconvertibles avant migration
SELECT date_str, COUNT(*) AS n
FROM imports
WHERE date_str !~ '^\d{4}-\d{2}-\d{2}$' -- format non standard
GROUP BY date_str
ORDER BY n DESC;
-- Migration propre : VARCHAR → DATE
ALTER TABLE imports ADD COLUMN date_propre DATE;
UPDATE imports
SET date_propre = CASE
WHEN date_str ~ '^\d{4}-\d{2}-\d{2}$' THEN CAST(date_str AS DATE)
WHEN date_str ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(date_str, 'DD/MM/YYYY')
ELSE NULL -- valeurs inconvertibles → NULL explicite
END;
-- Vérifier le résultat
SELECT COUNT(*) FILTER (WHERE date_propre IS NULL) AS echecs_conversion FROM imports;
Fonction 10 — DATE_TRUNC()
Ce qu'elle fait : tronquer une date ou un timestamp à un niveau de granularité (jour, semaine, mois, trimestre, année).
-- Syntaxe
DATE_TRUNC('granularite', timestamp)
-- Les niveaux disponibles
SELECT DATE_TRUNC('year', NOW()); -- 2024-01-01 00:00:00
SELECT DATE_TRUNC('quarter', NOW()); -- 2024-10-01 00:00:00 (Q4)
SELECT DATE_TRUNC('month', NOW()); -- 2024-11-01 00:00:00
SELECT DATE_TRUNC('week', NOW()); -- 2024-10-28 00:00:00 (lundi de la semaine)
SELECT DATE_TRUNC('day', NOW()); -- 2024-11-03 00:00:00
SELECT DATE_TRUNC('hour', NOW()); -- 2024-11-03 14:00:00
-- Rapport mensuel
SELECT
DATE_TRUNC('month', created_at) AS mois,
COUNT(*) AS nb_commandes,
SUM(montant) AS ca
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY mois;
-- ❌ Piège : EXTRACT mélange les années, DATE_TRUNC non
SELECT EXTRACT('month' FROM created_at) AS mois, SUM(montant) AS ca
FROM orders GROUP BY ;
-- Janvier 2023 + Janvier 2024 = un seul "mois 1" → résultat faux
-- ✅ DATE_TRUNC pour grouper par période sans mélanger les années
SELECT DATE_TRUNC('month', created_at) AS mois, SUM(montant) AS ca
FROM orders GROUP BY ORDER BY ;
Équivalents par SGBD :
Opération | PostgreSQL | BigQuery | Snowflake |
|---|---|---|---|
Tronquer au mois |
|
|
|
Tronquer au trimestre |
|
|
|
Fonction 11 — DATEDIFF() / AGE()
Ce qu'elles font : calculer des écarts temporels fiables entre deux dates.
-- PostgreSQL : soustraction native + EXTRACT
SELECT
EXTRACT(EPOCH FROM (date_fin - date_debut)) / AS nb_jours,
AGE(date_fin, date_debut) AS duree_lisible -- retourne '2 years 3 months 15 days'
FROM projets;
-- BigQuery
SELECT DATE_DIFF(date_fin, date_debut, DAY) AS nb_jours FROM projets;
SELECT TIMESTAMP_DIFF(ts_fin, ts_debut, HOUR) AS nb_heures FROM logs;
-- Snowflake / SQL Server
SELECT DATEDIFF('day', date_debut, date_fin) AS nb_jours FROM projets;
-- Cas business : délai entre inscription et premier achat
SELECT
u.id,
u.created_at AS date_inscription,
MIN(o.created_at) AS premier_achat,
EXTRACT(EPOCH FROM (MIN(o.created_at) - u.created_at)) / AS jours_avant_achat
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.created_at
ORDER BY jours_avant_achat DESC NULLS LAST; -- utilisateurs qui n'ont pas encore acheté en dernier
Calculer des âges et des anciennetés
-- Ancienneté des employés
SELECT
nom,
date_embauche,
DATE_PART('year', AGE(CURRENT_DATE, date_embauche)) AS annees_anciennete,
CASE
WHEN DATE_PART('year', AGE(CURRENT_DATE, date_embauche)) >= THEN 'Senior'
WHEN DATE_PART('year', AGE(CURRENT_DATE, date_embauche)) >= THEN 'Confirmé'
ELSE 'Junior'
END AS categorie_anciennete
FROM employes
WHERE date_depart IS NULL -- employés encore en poste
ORDER BY date_embauche;
BLOC 4 — Gérer les doublons
Fonction 12 — DISTINCT
Ce qu'il fait : dédupliquer les résultats à la volée.
-- Lister les valeurs uniques
SELECT DISTINCT pays FROM users;
SELECT DISTINCT statut FROM orders;
-- ✅ Usage légitime : test de présence dans une liste
SELECT DISTINCT user_id FROM orders; -- liste des clients ayant commandé
-- ❌ Usage à éviter : masquer un fan-out de JOIN
SELECT DISTINCT u.id, u.nom
FROM users u
JOIN orders o ON u.id = o.user_id;
-- DISTINCT cache le problème. La vraie correction : comprendre pourquoi il y a des doublons
-- ✅ Préférer EXISTS pour tester la présence
SELECT id, nom FROM users u
WHERE EXISTS (SELECT FROM orders o WHERE o.user_id = u.id);
Fonction 13 — ROW_NUMBER()
Ce qu'elle fait : numéroter les lignes dans un groupe, ce qui permet d'identifier et d'isoler les doublons.
-- Syntaxe
ROW_NUMBER() OVER (PARTITION BY col_groupe ORDER BY col_tri)
-- Identifier les doublons dans une table
WITH doublons AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM users
)
-- Voir les doublons
SELECT * FROM doublons WHERE rn > ;
-- Garder seulement la version la plus récente
DELETE FROM users WHERE id IN (
SELECT id FROM doublons WHERE rn >
);
-- Ou avec une CTE de déduplication (version sans modifier la table)
WITH users_propres AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM users
)
SELECT id, email, nom, created_at
FROM users_propres
WHERE rn = ;
Cas business — RH : dédupliquer les candidatures
-- Un candidat peut avoir postulé plusieurs fois au même poste
WITH candidatures_dedup AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY candidat_id, poste_id
ORDER BY date_candidature DESC -- garder la plus récente
) AS rn
FROM candidatures
)
SELECT
c.candidat_id,
p.titre AS poste,
c.date_candidature,
c.statut
FROM candidatures_dedup c
JOIN postes p ON c.poste_id = p.id
WHERE c.rn = -- une seule candidature par candidat/poste
ORDER BY c.date_candidature DESC;
Fonction 14 — GROUP BY + HAVING pour détecter les anomalies
Ce qu'ils font ensemble : identifier les groupes qui violent une règle d'intégrité.
-- Détecter les emails en double
SELECT email, COUNT(*) AS nb_comptes
FROM users
GROUP BY email
HAVING COUNT(*) >
ORDER BY nb_comptes DESC;
-- Détecter les transactions suspectes (même montant, même compte, même jour)
SELECT
compte_id,
DATE(date_transaction) AS jour,
montant,
COUNT(*) AS nb_occurrences
FROM transactions
GROUP BY compte_id, DATE(date_transaction), montant
HAVING COUNT(*) > -- plus de 3 fois la même transaction le même jour
ORDER BY nb_occurrences DESC;
-- Détecter les produits avec des prix incohérents
SELECT sku, COUNT(DISTINCT prix) AS nb_prix_differents
FROM historique_prix
WHERE date_prix >= NOW() - INTERVAL '30 days'
GROUP BY sku
HAVING COUNT(DISTINCT prix) > -- plus de 2 prix différents sur 30 jours
ORDER BY nb_prix_differents DESC;
Cas business — SaaS : détecter les comptes dupliqués
-- Comptes suspects : même domaine email, créés le même jour
WITH comptes_suspects AS (
SELECT
SUBSTRING(email FROM POSITION('@' IN email) + ) AS domaine,
DATE(created_at) AS jour_creation,
COUNT(*) AS nb_comptes,
ARRAY_AGG(id ORDER BY created_at) AS ids,
ARRAY_AGG(email ORDER BY created_at) AS emails
FROM users
GROUP BY ,
HAVING COUNT(*) > -- plus de 5 comptes du même domaine le même jour
)
SELECT *
FROM comptes_suspects
ORDER BY nb_comptes DESC;
🧪 Teste la déduplication sur des données réelles
ROW_NUMBER, DISTINCT, GROUP BY + HAVING : les 3 outils du data analyst qui reçoit un fichier RH à 50 000 lignes avec des doublons. Modai Lab simule exactement ces cas.
_Scénarios data réels · Feedback senior · Progression mesurée_
PARTIE 5 — Les combinaisons les plus puissantes
Combinaison 1 — Normalisation complète d'un import
-- Nettoyer un import CSV complet en une seule requête
INSERT INTO users_propres (email, nom, telephone, pays, created_at)
SELECT
LOWER(TRIM(email)) AS email,
INITCAP(TRIM(nom)) AS nom,
NULLIF(REGEXP_REPLACE(TRIM(telephone), '[^0-9+]', '', 'g'), '') AS telephone,
UPPER(TRIM(pays)) AS pays,
CAST(NULLIF(TRIM(date_creation), '') AS DATE) AS created_at
FROM imports_csv
WHERE TRIM(email) != ''
AND TRIM(email) LIKE '%@%.%' -- validation basique du format email
ON CONFLICT (email) DO UPDATE -- en cas de doublon sur email, mettre à jour
SET nom = EXCLUDED.nom,
updated_at = NOW();
Combinaison 2 — Audit de qualité des données en une requête
-- Générer un rapport de qualité complet sur une table
SELECT
'users' AS table_name,
COUNT(*) AS total_lignes,
-- Taux de remplissage par colonne
ROUND(. * COUNT(email) / COUNT(*), ) AS pct_email,
ROUND(. * COUNT(telephone) / COUNT(*), ) AS pct_telephone,
ROUND(. * COUNT(pays) / COUNT(*), ) AS pct_pays,
-- Problèmes de format
COUNT(*) FILTER (WHERE email NOT LIKE '%@%') AS emails_invalides,
COUNT(*) FILTER (WHERE TRIM(nom) != nom) AS noms_avec_espaces,
COUNT(*) FILTER (WHERE LOWER(email) != email) AS emails_non_normalises,
-- Doublons
COUNT(*) - COUNT(DISTINCT email) AS doublons_email
FROM users;
Combinaison 3 — Déduplication complète avec priorité
-- Fusionner les doublons en gardant les meilleures données de chaque version
WITH doublons_groupes AS (
SELECT
LOWER(TRIM(email)) AS email_normalise,
MAX(telephone) AS meilleur_telephone, -- NULLIF pour ignorer les vides
MAX(adresse) AS meilleure_adresse,
MAX(date_achat) AS derniere_activite,
COUNT(*) AS nb_doublons,
MIN(created_at) AS premiere_inscription -- garder la date la plus ancienne
FROM users
GROUP BY LOWER(TRIM(email))
)
SELECT
email_normalise,
COALESCE(meilleur_telephone, 'Non renseigné') AS telephone,
COALESCE(meilleure_adresse, 'Non renseignée') AS adresse,
derniere_activite,
premiere_inscription,
nb_doublons
FROM doublons_groupes
ORDER BY nb_doublons DESC;
Combinaison 4 — Pipeline de nettoyage étape par étape avec CTE
-- Architecture recommandée pour nettoyer des données complexes
WITH
-- Étape 1 : normaliser les chaînes
normalise AS (
SELECT
id,
LOWER(TRIM(email)) AS email,
INITCAP(TRIM(nom)) AS nom,
NULLIF(TRIM(telephone), '') AS telephone,
UPPER(TRIM(pays)) AS pays,
created_at
FROM imports_bruts
),
-- Étape 2 : valider et convertir les types
valide AS (
SELECT *,
CASE WHEN email LIKE '%@%.%' THEN email ELSE NULL END AS email_valide,
CASE WHEN LENGTH(telephone) >= THEN telephone ELSE NULL END AS tel_valide
FROM normalise
),
-- Étape 3 : dédupliquer
deduplique AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM valide
WHERE email_valide IS NOT NULL -- exclure les emails invalides
)
-- Résultat final propre
SELECT id, email, nom, COALESCE(tel_valide, 'Non renseigné') AS telephone, pays, created_at
FROM deduplique
WHERE rn = ;
PARTIE 6 — Prompt Claude pour auditer la qualité de tes données
Prompt d'audit complet
Tu es un expert SQL data quality. Audite la qualité des données de cette table
et génère un rapport complet avec les requêtes de correction.
TABLE À AUDITER :
[coller le CREATE TABLE ou la liste des colonnes avec types]
CONTEXTE :
- Source des données : [import CSV / API / saisie manuelle / ETL]
- SGBD : [PostgreSQL / BigQuery / Snowflake]
- Volume approximatif : [nombre de lignes]
Génère :
1. REQUÊTE D'AUDIT COMPLET
- Taux de NULL par colonne
- Taux de doublons par clé naturelle
- Valeurs hors format (emails, téléphones, codes postaux)
- Valeurs suspectes (chaînes vides, espaces parasites, casse incohérente)
2. TOP 5 PROBLÈMES PAR PRIORITÉ
- Problèmes bloquants (clés dupliquées, NULL sur colonnes obligatoires)
- Problèmes de qualité (formats incohérents, valeurs aberrantes)
- Problèmes de normalisation (casse, espaces, caractères spéciaux)
3. REQUÊTES DE CORRECTION
- Une requête UPDATE par type de problème
- Avec validation avant application (SELECT qui montre les lignes impactées)
- En partant des corrections les plus sûres aux plus risquées
Format : rapport structuré avec les requêtes SQL prêtes à copier-coller.
Prompt rapide
Génère un script SQL d'audit de qualité pour cette table :
[coller le schéma]
Pour chaque colonne : taux de NULL, doublons, valeurs hors format.
SGBD : [PostgreSQL / BigQuery / Snowflake]
Inclure les requêtes de correction pour les problèmes les plus courants.
Récapitulatif — Les 14 fonctions
Bloc | Fonction | Rôle principal |
|---|---|---|
NULL |
| Remplacer un NULL par une valeur de fallback |
NULL |
| Transformer une valeur en NULL si condition |
NULL |
| Filtrer proprement les valeurs manquantes |
Chaînes |
| Supprimer les espaces cachés |
Chaînes |
| Harmoniser la casse |
Chaînes |
| Corriger les valeurs mal saisies |
Chaînes |
| Extraire une partie d'une chaîne |
Chaînes |
| Assembler des champs éparpillés |
Dates |
| Convertir VARCHAR → DATE proprement |
Dates |
| Tronquer à la granularité souhaitée |
Dates |
| Calculer des écarts temporels fiables |
Doublons |
| Dédupliquer à la volée |
Doublons |
| Identifier et isoler les doublons |
Doublons |
| Détecter les anomalies par agrégat |
✅ Checklist avant de livrer un rapport sur des données importées
☐ J'ai vérifié le taux de NULL sur les colonnes clés avec
COUNT(*) vs COUNT(col)☐ J'ai normalisé les emails avec
LOWER(TRIM(email))☐ J'ai détecté les doublons avec
GROUP BY + HAVING COUNT(*) > 1☐ J'ai vérifié que les dates ne sont pas stockées en VARCHAR
☐ J'ai utilisé
COALESCE()pour les valeurs par défaut dans mes calculs☐ J'ai utilisé
NULLIF()pour éviter les divisions par zéro dans les KPIs☐ J'ai utilisé
DATE_TRUNC()et nonEXTRACT()pour grouper par période
🚀 Pratiquer sur Modai Lab
Les datasets propres n'existent pas en entreprise. La seule façon de développer les bons réflexes est de pratiquer sur des données réelles — avec des NULL inattendus, des formats incohérents, des doublons cachés.
👉 modai-lab.com/test-sql/ — Exercices calibrés · Données imparfaites · Feedback senior
_Guide offert suite au post LinkedIn · Entraînez-vous sur modai-lab.com/test-sql/ · Partagez librement ♻️_