Guide pratique · E-commerce · SaaS · Finance · Lecture : ~20 min Teste ton niveau sur modai-lab.com/test-sql/

Ce que les recruteurs testent vraiment

Pas la syntaxe. Tout le monde connaît la syntaxe.

Ils testent ce que tu fais quand les données sont sales, incomplètes, incohérentes. Ils testent si tu sais expliquer pourquoi COUNT(*) et COUNT(colonne) donnent des résultats différents. Pourquoi AVG() peut être faussée sans alerte. Pourquoi l'ordre des colonnes dans GROUP BY change tout.

Ce guide couvre :

  • Les pièges classiques de chaque fonction en conditions réelles

  • Les erreurs qui faussent silencieusement tes résultats

  • Des cas business concrets : e-commerce, SaaS, finance

  • Un prompt Claude pour auditer tes agrégations en 2 minutes

🧪 Calibre ton niveau avant de commencer

Sais-tu déjà expliquer la différence entre COUNT(*), COUNT(col) et COUNT(DISTINCT col) ? 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é_

FONCTION 1 — COUNT()

La question de l'entretien

"Quelle est la différence entre COUNT(*), COUNT(colonne) et COUNT(DISTINCT colonne) ?"

Presque tout le monde dit "COUNT(*) compte les lignes". La bonne réponse va plus loin.

Les 3 formes de COUNT et ce qu'elles font vraiment

-- Données d'exemple :
-- id | user_id | montant | statut
-- 1 | 101 | 50 | livré
-- 2 | 101 | NULL | annulé
-- 3 | 102 | 80 | livré
-- 4 | NULL | 30 | livré
-- 5 | 102 | 90 | livré
 
-- COUNT(*) : compte TOUTES les lignes, NULL compris
SELECT COUNT(*) FROM orders;
-- Résultat : 5
 
-- COUNT(colonne) : compte les lignes où la colonne est NOT NULL
SELECT COUNT(user_id) FROM orders;
-- Résultat : 4 (la ligne 4 a user_id = NULL → exclue)
 
SELECT COUNT(montant) FROM orders;
-- Résultat : 4 (la ligne 2 a montant = NULL → exclue)
 
-- COUNT(DISTINCT colonne) : valeurs distinctes non-NULL
SELECT COUNT(DISTINCT user_id) FROM orders;
-- Résultat : 2 (101 et 102, le NULL est ignoré)
5-fonctions-aggregations-sql-indispensables · 01

Le tableau de vérité COUNT :

Expression

Compte

Inclut NULL ?

Inclut doublons ?

COUNT(*)

Toutes les lignes

✅ Oui

✅ Oui

COUNT(col)

Lignes où col IS NOT NULL

❌ Non

✅ Oui

COUNT(DISTINCT col)

Valeurs uniques non-NULL

❌ Non

❌ Non

Les pièges en conditions réelles

Piège 1 — COUNT(*) vs COUNT(col) pour mesurer une présence

-- ❌ Erreur classique : utiliser COUNT(*) pour mesurer le taux de remplissage
SELECT
COUNT(*) AS total_commandes,
COUNT(*) AS commandes_avec_email -- ← faux : compte tout, même les NULL
FROM orders;
 
-- ✅ Correct
SELECT
COUNT(*) AS total_commandes,
COUNT(email_client) AS commandes_avec_email, -- ignore les NULL
ROUND(. * COUNT(email_client) / COUNT(*), ) AS taux_remplissage_pct
FROM orders;
5-fonctions-aggregations-sql-indispensables · 02 · Piège 1 — COUNT(*) vs COUNT(col) pour mesurer une présence

Piège 2 — COUNT(DISTINCT) qui cache un fan-out

-- Contexte : compter les clients uniques par campagne marketing
-- Problème : un client peut avoir plusieurs touchpoints → doublons
 
-- ❌ COUNT(DISTINCT) utilisé pour masquer un fan-out
SELECT
campaign_id,
COUNT(DISTINCT user_id) AS clients_uniques -- "ça donne le bon COUNT"
FROM user_touchpoints
GROUP BY campaign_id;
-- Le COUNT est correct, mais si tu ajoutes SUM(revenue) ici,
-- il sera multiplié par le nombre de touchpoints par client
 
-- ✅ Éliminer le fan-out avant de joindre
SELECT
campaign_id,
COUNT(user_id) AS clients_uniques -- pas besoin de DISTINCT ici
FROM (
SELECT DISTINCT user_id, campaign_id FROM user_touchpoints
) dedup
GROUP BY campaign_id;
5-fonctions-aggregations-sql-indispensables · 03 · Piège 2 — COUNT(DISTINCT) qui cache un fan-out

Piège 3 — COUNT dans un contexte de window function

-- Compter les commandes par user ET avoir le total global en même temps
SELECT
user_id,
COUNT(*) OVER (PARTITION BY user_id) AS nb_commandes_user,
COUNT(*) OVER () AS total_commandes_global,
ROUND(. * COUNT(*) OVER (PARTITION BY user_id) / COUNT(*) OVER (), ) AS pct_total
FROM orders;
5-fonctions-aggregations-sql-indispensables · 04 · Piège 3 — COUNT dans un contexte de window function

Cas business — SaaS : taux d'activation par cohorte

-- Objectif : % d'users ayant fait leur première action dans les 7 jours
SELECT
DATE_TRUNC('week', u.created_at) AS semaine_inscription,
COUNT(*) AS total_inscrits,
COUNT(a.user_id) AS users_actives, -- COUNT(col) ignore les NULL du LEFT JOIN
ROUND(. * COUNT(a.user_id) / COUNT(*), ) AS taux_activation_pct
FROM users u
LEFT JOIN (
SELECT DISTINCT user_id
FROM actions
WHERE type = 'first_action'
) a ON u.id = a.user_id
AND a.created_at <= u.created_at + INTERVAL '7 days'
GROUP BY
ORDER BY ;
5-fonctions-aggregations-sql-indispensables · 05

FONCTION 2 — SUM()

La question de l'entretien

"Que fait SUM() avec les valeurs NULL ? Et comment calculer un total qui inclut les lignes sans valeur ?"

SUM() et NULL : le comportement silencieux

-- Données :
-- id | montant
-- 1 | 100
-- 2 | NULL ← ligne sans montant
-- 3 | 50
-- 4 | NULL
 
-- SUM() ignore les NULL sans prévenir
SELECT SUM(montant) FROM orders;
-- Résultat : 150 — pas 150 + 0 + 0 = 150, mais 100 + 50 = 150
-- L'information "il y avait 2 lignes sans montant" est perdue
 
-- ✅ Toujours diagnostiquer avant d'interpréter un SUM
SELECT
COUNT(*) AS total_lignes,
COUNT(montant) AS lignes_renseignees,
COUNT(*) - COUNT(montant) AS lignes_null,
SUM(montant) AS somme_sans_null,
SUM(COALESCE(montant, )) AS somme_avec_null_a_zero,
SUM(montant) - SUM(COALESCE(montant, )) AS ecart -- toujours 0 ici, mais utile à voir
FROM orders;
5-fonctions-aggregations-sql-indispensables · 06

Les pièges en conditions réelles

Piège 1 — SUM() sur un mauvais périmètre après JOIN

-- ❌ Fan-out : SUM est multiplié par le nombre de jointures
SELECT u.id, SUM(o.montant) AS ca_total
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN user_tags t ON u.id = t.user_id -- chaque tag multiplie les lignes
GROUP BY u.id;
-- Si un user a 3 commandes et 2 tags → SUM compte chaque commande 2 fois
 
-- ✅ Agréger avant de joindre
SELECT u.id, o.ca_total
FROM users u
JOIN (
SELECT user_id, SUM(montant) AS ca_total
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id
WHERE EXISTS (SELECT FROM user_tags t WHERE t.user_id = u.id);
5-fonctions-aggregations-sql-indispensables · 07 · Piège 1 — SUM() sur un mauvais périmètre après JOIN

Piège 2 — SUM() conditionnel avec CASE WHEN

-- Calculer plusieurs métriques en une seule passe
SELECT
DATE_TRUNC('month', created_at) AS mois,
SUM(montant) AS ca_total,
SUM(CASE WHEN statut = 'livré' THEN montant ELSE END) AS ca_livre,
SUM(CASE WHEN statut = 'retourné' THEN montant ELSE END) AS ca_retourne,
SUM(montant) - SUM(CASE WHEN statut = 'retourné' THEN montant ELSE END) AS ca_net,
ROUND(. *
SUM(CASE WHEN statut = 'retourné' THEN montant ELSE END) /
NULLIF(SUM(montant), ), ) AS taux_retour_pct
FROM orders
GROUP BY
ORDER BY ;
5-fonctions-aggregations-sql-indispensables · 08 · Piège 2 — SUM() conditionnel avec CASE WHEN

Piège 3 — SUM() cumulatif avec window function

-- Cumul des ventes depuis le début de l'année
SELECT
DATE_TRUNC('day', created_at) AS jour,
SUM(montant) AS ca_jour,
SUM(SUM(montant)) OVER (
ORDER BY DATE_TRUNC('day', created_at)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ca_cumulatif
FROM orders
WHERE created_at >= DATE_TRUNC('year', NOW())
GROUP BY
ORDER BY ;
-- SUM(SUM(...)) : le SUM externe est la window function,
-- le SUM interne est l'agrégation du GROUP BY
5-fonctions-aggregations-sql-indispensables · 09 · Piège 3 — SUM() cumulatif avec window function

Cas business — Finance : P&L mensuel

-- Construire un compte de résultat simplifié
WITH transactions_classees AS (
SELECT
DATE_TRUNC('month', date_transaction) AS mois,
type_compte,
SUM(CASE WHEN sens = 'crédit' THEN montant ELSE END) AS credits,
SUM(CASE WHEN sens = 'débit' THEN montant ELSE END) AS debits,
SUM(CASE WHEN sens = 'crédit' THEN montant ELSE -montant END) AS solde
FROM transactions
WHERE annee_fiscale =
GROUP BY ,
)
SELECT
mois,
SUM(CASE WHEN type_compte = 'revenus' THEN solde ELSE END) AS revenus,
SUM(CASE WHEN type_compte = 'charges' THEN -solde ELSE END) AS charges,
SUM(CASE WHEN type_compte = 'revenus' THEN solde ELSE -solde END) AS resultat_net
FROM transactions_classees
GROUP BY mois
ORDER BY mois;
5-fonctions-aggregations-sql-indispensables · 10

🧪 Teste COUNT et SUM sur des données réelles

Ces deux fonctions sont au cœur de tous les rapports analytiques. Modai Lab te soumet des datasets avec des NULL, des doublons et des cas limites — score immédiat.

👉 Pratiquer sur modai-lab.com

_Exercices interactifs · Données réelles · Feedback instantané_

FONCTION 3 — AVG()

La question de l'entretien

"Pourquoi AVG() peut-il être faussé sans erreur ? Donne un exemple concret."

AVG() et le dénominateur fantôme

-- Données : notes de satisfaction (1 à 10), 40% non renseignées
-- id | note
-- 1 | 8
-- 2 | NULL ← non-répondant
-- 3 | 6
-- 4 | NULL
-- 5 | 10
 
-- AVG() ignore les NULL : dénominateur = 3, pas 5
SELECT AVG(note) FROM evaluations;
-- Résultat : (8 + 6 + 10) / 3 = 8.0
 
-- Mais si les non-répondants sont les moins satisfaits (hypothèse courante)
-- la vraie moyenne pourrait être bien inférieure à 8.0
 
-- ✅ Toujours afficher le contexte avec la moyenne
SELECT
AVG(note) AS moy_repondants,
AVG(COALESCE(note, )) AS moy_avec_zero_pour_nr,
AVG(COALESCE(note, )) AS moy_avec_neutre_pour_nr,
COUNT(*) AS total,
COUNT(note) AS repondants,
ROUND(. * COUNT(note) / COUNT(*), ) AS taux_reponse_pct
FROM evaluations;
-- La moyenne sans contexte de taux de réponse est une métrique incomplète
5-fonctions-aggregations-sql-indispensables · 11

Les pièges en conditions réelles

Piège 1 — AVG() sur une période filtrée

-- ❌ Panier moyen calculé sur les commandes livrées seulement
SELECT AVG(montant) AS panier_moyen
FROM orders
WHERE statut = 'livré';
-- Ce n'est pas le panier moyen de toutes les commandes
-- C'est le panier moyen des commandes qui ont abouti
-- Si les grosses commandes sont plus souvent annulées, le résultat est biaisé vers le haut
 
-- ✅ Être explicite sur le périmètre
SELECT
AVG(montant) AS panier_moyen_toutes_commandes,
AVG(CASE WHEN statut = 'livré' THEN montant END) AS panier_moyen_livrees,
AVG(CASE WHEN statut = 'annulé' THEN montant END) AS panier_moyen_annulees
FROM orders;
5-fonctions-aggregations-sql-indispensables · 12 · Piège 1 — AVG() sur une période filtrée

Piège 2 — AVG() vs médiane

-- AVG() est sensible aux valeurs extrêmes (outliers)
-- Données de salaires : [30k, 32k, 31k, 35k, 500k]
-- AVG = (30+32+31+35+500) / 5 = 125.6k → pas représentatif
-- Médiane = 32k → beaucoup plus représentatif
 
-- ✅ Toujours comparer AVG et médiane sur des distributions asymétriques
SELECT
AVG(salaire) AS salaire_moyen,
PERCENTILE_CONT(.) WITHIN GROUP (ORDER BY salaire) AS salaire_median,
PERCENTILE_CONT(.) WITHIN GROUP (ORDER BY salaire) AS q1,
PERCENTILE_CONT(.) WITHIN GROUP (ORDER BY salaire) AS q3,
MAX(salaire) AS max,
MIN(salaire) AS min
FROM employes;
-- Si moyen >> médiane : distribution asymétrique, outliers présents
5-fonctions-aggregations-sql-indispensables · 13 · Piège 2 — AVG() vs médiane

Piège 3 — AVG() mobile pour lisser les tendances

-- Moyenne mobile sur 7 jours pour lisser les fluctuations journalières
SELECT
DATE_TRUNC('day', created_at) AS jour,
AVG(montant) AS panier_moyen_jour,
AVG(AVG(montant)) OVER (
ORDER BY DATE_TRUNC('day', created_at)
ROWS BETWEEN PRECEDING AND CURRENT ROW
) AS panier_moyen_7j
FROM orders
GROUP BY
ORDER BY ;
5-fonctions-aggregations-sql-indispensables · 14 · Piège 3 — AVG() mobile pour lisser les tendances

Cas business — E-commerce : analyse du cycle de vie client

-- AVG() sur des durées pour comprendre les comportements
WITH cycle_client AS (
SELECT
user_id,
MIN(created_at) AS premier_achat,
MAX(created_at) AS dernier_achat,
COUNT(*) AS nb_commandes,
SUM(montant) AS ca_total,
AVG(montant) AS panier_moyen
FROM orders
WHERE statut = 'livré'
GROUP BY user_id
)
SELECT
nb_commandes AS frequence_achat,
COUNT(*) AS nb_clients,
ROUND(AVG(ca_total), ) AS ca_moyen_par_client,
ROUND(AVG(panier_moyen), ) AS panier_moyen,
ROUND(AVG(
EXTRACT(EPOCH FROM (dernier_achat - premier_achat)) /
), ) AS duree_moy_relation_jours
FROM cycle_client
GROUP BY
ORDER BY ;
5-fonctions-aggregations-sql-indispensables · 15

FONCTION 4 — MAX() / MIN()

La question de l'entretien

"Comment utilises-tu MAX() et MIN() sur des dates ? Et sur des chaînes de caractères ?"

MAX() / MIN() sur différents types de données

-- Sur des nombres (comportement attendu)
SELECT MAX(montant) AS plus_grosse_commande, MIN(montant) AS plus_petite_commande
FROM orders;
 
-- Sur des dates (très utile en pratique)
SELECT
MIN(created_at) AS premiere_commande,
MAX(created_at) AS derniere_commande,
MAX(created_at) - MIN(created_at) AS duree_activite
FROM orders
WHERE user_id = ;
 
-- Sur des chaînes de caractères (ordre alphabétique)
SELECT MAX(nom) AS dernier_alpha, MIN(nom) AS premier_alpha
FROM users;
-- MAX('Z...') > MIN('A...') — ordre lexicographique, pas logique métier
-- Attention : la casse peut changer le résultat (majuscules < minuscules en ASCII)
5-fonctions-aggregations-sql-indispensables · 16

Les pièges en conditions réelles

Piège 1 — MAX() pour "la dernière valeur" sans ORDER BY

-- ❌ MAX(created_at) donne la date la plus récente, mais pas la ligne associée
SELECT user_id, MAX(created_at) AS derniere_commande, montant
FROM orders
GROUP BY user_id, montant; -- ← faux : montant n'est pas agrégé correctement
 
-- ✅ Pour récupérer la ligne la plus récente : ROW_NUMBER ou DISTINCT ON
SELECT DISTINCT ON (user_id)
user_id, created_at, montant, statut
FROM orders
ORDER BY user_id, created_at DESC;
-- Ou avec ROW_NUMBER (portable)
SELECT user_id, created_at, montant, statut
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) t
WHERE rn = ;
5-fonctions-aggregations-sql-indispensables · 17 · Piège 1 — MAX() pour "la dernière valeur" sans ORDER BY

Piège 2 — MIN(date) pour la date d'acquisition

-- Pattern classique d'analyse de cohorte
WITH premiere_commande AS (
SELECT
user_id,
MIN(created_at) AS date_premier_achat,
DATE_TRUNC('month', MIN(created_at)) AS cohorte
FROM orders
GROUP BY user_id
)
SELECT
cohorte,
COUNT(*) AS taille_cohorte,
ROUND(AVG(
EXTRACT(EPOCH FROM (MIN(o.created_at) - pc.date_premier_achat)) /
), ) AS delai_moyen_premier_achat_jours
FROM premiere_commande pc
JOIN orders o ON pc.user_id = o.user_id
AND o.created_at > pc.date_premier_achat -- commandes APRÈS la première
GROUP BY
ORDER BY ;
5-fonctions-aggregations-sql-indispensables · 18 · Piège 2 — MIN(date) pour la date d'acquisition

Piège 3 — MAX() / MIN() ignorent les NULL comme SUM()

-- Si toutes les valeurs d'un groupe sont NULL, MAX() et MIN() retournent NULL
SELECT categorie, MAX(prix_promo) AS promo_max
FROM produits
GROUP BY categorie;
-- Les catégories sans promotion ont MAX(prix_promo) = NULL, pas 0
 
-- ✅ Gérer explicitement avec COALESCE si nécessaire
SELECT
categorie,
MAX(prix_promo) AS promo_max,
COALESCE(MAX(prix_promo), ) AS promo_max_ou_zero,
CASE WHEN MAX(prix_promo) IS NULL THEN 'Pas de promo' ELSE 'Promo active' END AS statut_promo
FROM produits
GROUP BY categorie;
5-fonctions-aggregations-sql-indispensables · 19 · Piège 3 — MAX() / MIN() ignorent les NULL comme SUM()

Cas business — SaaS : identifier les pics d'utilisation

-- MAX() pour détecter les pics et les anomalies
WITH activite_horaire AS (
SELECT
DATE_TRUNC('hour', created_at) AS heure,
COUNT(*) AS nb_events,
MAX(duree_session) AS session_max,
MIN(duree_session) AS session_min,
AVG(duree_session) AS session_moyenne
FROM user_sessions
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY
)
SELECT
heure,
nb_events,
session_max,
session_min,
ROUND(session_moyenne, ) AS session_moyenne,
CASE
WHEN nb_events > AVG(nb_events) OVER () * THEN '⚠️ Pic anormal'
WHEN session_max > THEN '⚠️ Session longue'
ELSE '✅ Normal'
END AS alerte
FROM activite_horaire
ORDER BY heure;
5-fonctions-aggregations-sql-indispensables · 20

🧪 Teste AVG, MAX et MIN sur des cas limites

Les recruteurs adorent poser des questions sur le comportement de ces fonctions avec des données réelles. Modai Lab simule exactement ces scénarios — feedback ligne par ligne.

👉 Tester sur modai-lab.com/test-sql/

_Score immédiat · Explication des erreurs · Niveau calibré_

FONCTION 5 — GROUP BY

La question de l'entretien

"Pourquoi l'ordre des colonnes dans GROUP BY change-t-il tout ? Et que se passe-t-il avec les NULL ?"

La règle fondamentale et ce qu'elle implique vraiment

-- Règle : toute colonne dans le SELECT qui n'est pas une agrégation
-- DOIT être dans le GROUP BY
 
-- ❌ Erreur : nom n'est pas dans GROUP BY
SELECT pays, nom, COUNT(*) FROM users GROUP BY pays;
-- Erreur SQL : "nom" must appear in the GROUP BY clause or be used in an aggregate function
 
-- ✅ Correct
SELECT pays, nom, COUNT(*) FROM users GROUP BY pays, nom;
-- Ou avec une agrégation sur nom
SELECT pays, MAX(nom) AS un_nom, COUNT(*) FROM users GROUP BY pays;
5-fonctions-aggregations-sql-indispensables · 21

L'ordre des colonnes dans GROUP BY

-- L'ordre des colonnes dans GROUP BY ne change PAS le résultat fonctionnel
-- mais il change la performance et le plan d'exécution sur certains SGBD
 
SELECT pays, statut, COUNT(*) AS n
FROM orders
GROUP BY pays, statut;
-- Équivalent à GROUP BY statut, pays pour le résultat
 
-- MAIS : l'ordre change le résultat du ORDER BY final
SELECT pays, statut, COUNT(*) AS n
FROM orders
GROUP BY pays, statut
ORDER BY pays, statut; -- trier par pays d'abord, puis statut
 
-- Ce qui change vraiment : l'agrégation séquentielle avec ROLLUP
SELECT pays, statut, COUNT(*) AS n
FROM orders
GROUP BY ROLLUP(pays, statut);
-- ROLLUP(pays, statut) ≠ ROLLUP(statut, pays)
-- Le premier donne : totaux par pays, puis total global
-- Le second donne : totaux par statut, puis total global
5-fonctions-aggregations-sql-indispensables · 22

GROUP BY et les NULL

-- NULL forme un groupe à part dans GROUP BY
SELECT region, COUNT(*) AS nb_users
FROM users
GROUP BY region;
-- Résultat :
-- France | 1200
-- Belgique | 300
-- NULL | 50 ← utilisateurs sans région renseignée
 
-- ✅ Nommer le groupe NULL explicitement
SELECT
COALESCE(region, 'Non renseignée') AS region,
COUNT(*) AS nb_users
FROM users
GROUP BY region -- GROUP BY sur la colonne originale, pas sur l'alias
ORDER BY nb_users DESC;
5-fonctions-aggregations-sql-indispensables · 23

GROUP BY vs DISTINCT : quand utiliser lequel

-- DISTINCT : obtenir les valeurs uniques d'une colonne, sans agrégation
SELECT DISTINCT pays FROM users;
 
-- GROUP BY : obtenir les valeurs uniques ET calculer des agrégats
SELECT pays, COUNT(*) AS nb_users, AVG(age) AS age_moyen
FROM users
GROUP BY pays;
 
-- ❌ DISTINCT ne peut pas remplacer GROUP BY quand tu as des agrégats
-- ✅ GROUP BY est toujours le bon choix dès que tu calcules quelque chose
5-fonctions-aggregations-sql-indispensables · 24

GROUP BY avec des expressions

-- Grouper par une expression calculée
SELECT
DATE_TRUNC('month', created_at) AS mois, -- expression
statut,
COUNT(*) AS nb_commandes,
SUM(montant) AS ca
FROM orders
GROUP BY DATE_TRUNC('month', created_at), statut -- répéter l'expression
ORDER BY mois, statut;
 
-- Alternative : GROUP BY avec le numéro de colonne (à éviter en production)
SELECT DATE_TRUNC('month', created_at) AS mois, statut, COUNT(*)
FROM orders
GROUP BY , ; -- 1 = première colonne, 2 = deuxième — fragile si l'ordre change
5-fonctions-aggregations-sql-indispensables · 25

GROUPING SETS, ROLLUP et CUBE

-- ROLLUP : sous-totaux hiérarchiques
SELECT
COALESCE(pays, 'TOTAL') AS pays,
COALESCE(statut, 'Tous statuts') AS statut,
COUNT(*) AS nb,
SUM(montant) AS ca
FROM orders
GROUP BY ROLLUP(pays, statut)
ORDER BY pays, statut;
-- Retourne : lignes détaillées + totaux par pays + total général
 
-- CUBE : toutes les combinaisons de dimensions
SELECT pays, statut, categorie, SUM(montant)
FROM orders
GROUP BY CUBE(pays, statut, categorie);
-- Retourne toutes les combinaisons possibles de ces 3 dimensions
 
-- GROUPING SETS : choisir exactement quelles combinaisons
SELECT pays, statut, SUM(montant)
FROM orders
GROUP BY GROUPING SETS (
(pays, statut), -- combinaison 1
(pays), -- combinaison 2 : totaux par pays
() -- combinaison 3 : total général
);
5-fonctions-aggregations-sql-indispensables · 26

Cas business — Rapport multi-dimensionnel e-commerce

WITH ventes AS (
SELECT
p.categorie,
u.pays,
DATE_TRUNC('quarter', o.created_at) AS trimestre,
SUM(ol.prix_unitaire * ol.quantite) AS ca,
COUNT(DISTINCT o.id) AS nb_commandes,
COUNT(DISTINCT o.user_id) AS nb_clients
FROM order_lines ol
JOIN orders o ON ol.order_id = o.id
JOIN products p ON ol.product_id = p.id
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01'
AND o.statut = 'livré'
GROUP BY p.categorie, u.pays, DATE_TRUNC('quarter', o.created_at)
)
SELECT
COALESCE(categorie, 'Toutes catégories') AS categorie,
COALESCE(pays, 'Tous pays') AS pays,
COALESCE(CAST(trimestre AS VARCHAR), 'Total') AS trimestre,
SUM(ca) AS ca_total,
SUM(nb_commandes) AS nb_commandes,
SUM(nb_clients) AS nb_clients,
ROUND(SUM(ca) / NULLIF(SUM(nb_commandes), ), ) AS panier_moyen
FROM ventes
GROUP BY ROLLUP(categorie, pays, trimestre)
ORDER BY categorie, pays, trimestre;
5-fonctions-aggregations-sql-indispensables · 27

🧪 Teste GROUP BY avancé en conditions réelles

ROLLUP, CUBE, GROUP BY sur des expressions, gestion des NULL dans les groupes : Modai Lab propose ces exercices sur de vraies données avec feedback immédiat.

👉 Pratiquer sur modai-lab.com

_Scénarios data réels · Feedback senior · Progression mesurée_

PARTIE 6 — Prompt Claude pour auditer tes agrégations

Prompt d'audit complet

Tu es un expert SQL data analytics. Audite les agrégations de la requête suivante.
 
REQUÊTE À AUDITER :
[coller ta requête]
 
CONTEXTE :
- Objectif : [décrire ce que la requête doit calculer]
- Volume de données : [ordre de grandeur]
- Présence de NULL connue : [oui / non / inconnu]
 
Analyse ces points dans l'ordre :
 
1. COUNT()
- Y a-t-il une confusion entre COUNT(*), COUNT(col) et COUNT(DISTINCT col) ?
- Un COUNT(DISTINCT) masque-t-il un fan-out de JOIN ?
- Le dénominateur utilisé pour les taux est-il correct ?
 
2. SUM() et AVG()
- Les NULL sont-ils gérés intentionnellement (ignorés ou remplacés par 0) ?
- Y a-t-il un fan-out de JOIN qui multiplie les valeurs sommées ?
- AVG() est-il calculé sur le bon périmètre ? (pas de filtre qui biaise le dénominateur)
 
3. MAX() / MIN()
- MAX() est-il utilisé pour récupérer une ligne entière ? (→ préférer ROW_NUMBER)
- Les NULL dans MAX/MIN sont-ils gérés ?
 
4. GROUP BY
- Toutes les colonnes non agrégées sont-elles dans le GROUP BY ?
- Y a-t-il des colonnes avec NULL qui forment un groupe inattendu ?
- L'ordre des colonnes dans GROUP BY est-il cohérent avec l'intention ?
 
Format : ❌ pour chaque problème, ✅ pour les points corrects. Requête corrigée si nécessaire.
5-fonctions-aggregations-sql-indispensables · 28

Prompt rapide

Audite les agrégations COUNT, SUM, AVG, MAX, MIN et GROUP BY de cette requête SQL.
Identifie les erreurs silencieuses liées aux NULL, aux fan-outs et aux dénominateurs incorrects.
Propose la version corrigée avec commentaires.
 
[coller ta requête]
5-fonctions-aggregations-sql-indispensables · 29

Récapitulatif — Les 5 fonctions et leurs pièges

Fonction

Piège principal

Ce qui le révèle

La correction

COUNT(*)

Compte les NULL

Résultat > attendu

COUNT(col) ou COUNT(DISTINCT col)

COUNT(col)

Ignore les NULL

Résultat < attendu

Diagnostic NULL avant d'interpréter

SUM()

Ignore les NULL, multipliable par fan-out

CA sous-estimé ou gonflé

COALESCE, agréger avant JOIN

AVG()

Dénominateur réduit silencieusement, sensible aux outliers

Moyenne faussée

Afficher taux de réponse, comparer à la médiane

MAX/MIN()

Ne retourne pas la ligne, ignore les NULL

Donnée incomplète

ROW_NUMBER, COALESCE

GROUP BY

NULL forme un groupe, colonnes manquantes

Erreur SQL ou groupe fantôme

COALESCE, vérifier le SELECT

✅ Checklist entretien — Les 5 fonctions

  • ☐ Je sais expliquer la différence entre COUNT(*), COUNT(col) et COUNT(DISTINCT col)

  • ☐ Je sais pourquoi SUM() peut être multiplié après un JOIN

  • ☐ Je sais pourquoi AVG() peut être faussé sans erreur

  • ☐ Je sais utiliser MAX(date) vs ROW_NUMBER pour récupérer la dernière ligne

  • ☐ Je sais que GROUP BY crée un groupe pour les NULL

  • ☐ Je sais utiliser ROLLUP et GROUPING SETS pour les rapports multi-dimensionnels

  • ☐ Je peux construire un taux avec le bon dénominateur et NULLIF pour éviter la division par zéro

🚀 Pratiquer sur Modai Lab

Les 5 fonctions de ce guide sont testées dans 90% des entretiens data. La différence entre un candidat qui "connaît la syntaxe" et un candidat qui "sait les utiliser" se voit en 30 secondes sur des données réelles.

👉 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 ♻️_