SQL Avance pour Entretiens Data Analyst : Sous-requetes, Pivots et Optimisation des Requetes en 2026

Les processus de recrutement pour les postes de data analyst ont considerablement evolue ces dernieres annees. En 2026, les evaluations techniques SQL ne se limitent plus aux requetes SELECT basiques et aux jointures elementaires. Les entreprises du secteur technologique — qu'il s'agisse de scale-ups, de cabinets de conseil en donnees ou de grands groupes — attendent des candidats une maitrise approfondie des sous-requetes correlees, des transformations pivot et de l'optimisation de requetes. Ces competences techniques representent le principal critere de distinction entre un profil junior et un analyste de donnees confirme. Cet article detaille les patterns SQL avances qui apparaissent dans les evaluations techniques, accompagnes d'exemples concrets executables sur PostgreSQL 17.

Sous-requetes Correlees vs. Sous-requetes Classiques

La distinction entre sous-requete correlee et sous-requete classique constitue un fondamental evalue dans la quasi-totalite des entretiens techniques SQL. Une sous-requete classique (non correlee) s'execute une seule fois et produit un resultat independant du contexte de la requete externe. Le moteur de base de donnees calcule le resultat de la sous-requete, puis l'utilise comme valeur fixe pour filtrer les lignes de la requete principale.

Dans l'exemple suivant, la sous-requete calcule la moyenne globale des montants de commande. Cette valeur est determinee une seule fois, puis chaque ligne de la table orders est comparee a ce seuil unique.

-- regular_subquery_threshold.sql
-- Find all orders above the average order value
SELECT
  order_id,
  customer_id,
  total_amount
FROM orders
WHERE total_amount > (
  -- Executes once, returns a single scalar value
  SELECT AVG(total_amount)
  FROM orders
);

Une sous-requete correlee, en revanche, fait reference a une ou plusieurs colonnes de la requete externe. Le moteur doit donc reevaluer la sous-requete pour chaque ligne du jeu de resultats externe. Ce comportement a des implications directes sur les performances, en particulier sur les tables volumineuses.

La question classique d'entretien illustrant ce mecanisme : identifier les employes dont le salaire depasse la moyenne salariale de leur propre departement.

-- correlated_subquery_department.sql
-- Employees earning above their department average
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
WHERE e.salary > (
  -- Re-evaluated for each employee's department
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

Cette requete recalcule la moyenne salariale pour le departement de chaque employe examine. Sur une table de plusieurs centaines de milliers de lignes, le cout en temps d'execution devient prohibitif. L'approche optimisee consiste a precalculer les moyennes departementales dans un CTE (Common Table Expression), puis a effectuer une jointure simple avec la table principale.

-- optimized_with_cte.sql
-- Same result, single pass over the data
WITH dept_avg AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

La version CTE parcourt la table employees une seule fois pour calculer les moyennes, puis effectue la jointure. Le gain de performance est souvent spectaculaire sur les volumes de production. En entretien, la capacite a ecrire la version correlee puis a la refactoriser spontanement en CTE constitue un indicateur fort de maturite technique.

Patterns de Sous-requetes Courants en Entretien Data Analyst

Trois patterns de sous-requetes reviennent systematiquement dans les evaluations techniques pour les postes de data analyst : EXISTS pour les verifications d'appartenance, les sous-requetes scalaires dans les clauses SELECT, et les tables derivees dans les clauses FROM.

EXISTS vs. IN — La clause EXISTS interrompt l'evaluation des qu'une correspondance est trouvee. Sur les jeux de donnees volumineux, cette strategie de court-circuit offre un avantage de performance significatif par rapport a IN, qui materialise l'integralite du resultat de la sous-requete avant de proceder a la comparaison.

-- exists_vs_in.sql
-- Customers who placed at least one order in 2026 (EXISTS — preferred)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2026-01-01'
);

-- Equivalent with IN (slower on large datasets)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= '2026-01-01'
);

Les optimiseurs modernes de PostgreSQL peuvent dans certains cas reecrire un IN en semi-jointure, reduisant l'ecart de performance. Neanmoins, EXISTS reste la formulation recommandee car elle exprime plus clairement l'intention du developpeur et gere correctement les cas impliquant des valeurs NULL.

Sous-requete scalaire dans SELECT — Ce pattern permet d'ajouter une colonne calculee au jeu de resultats sans necessiter un JOIN explicite. La sous-requete retourne une valeur unique pour chaque ligne de la requete principale.

-- scalar_subquery_select.sql
-- Each product with its category's total revenue
SELECT
  p.product_id,
  p.product_name,
  p.category,
  (
    SELECT SUM(oi.quantity * oi.unit_price)
    FROM order_items oi
    JOIN products p2 ON oi.product_id = p2.product_id
    WHERE p2.category = p.category
  ) AS category_total_revenue
FROM products p;

Ce pattern s'avere pertinent pour enrichir un jeu de resultats d'une ou deux colonnes agregees. Au-dela, un CTE combine a un JOIN produit un code plus lisible et generalement plus performant, car le calcul d'agregation n'est effectue qu'une seule fois par categorie.

Requetes Pivot avec Agregation Conditionnelle

Les requetes pivot constituent un sujet incontournable des entretiens data analyst. Le principe consiste a transformer des donnees stockees ligne par ligne en un format colonnaire — typiquement, convertir des mois enregistres comme valeurs dans une colonne en colonnes distinctes comme jan_revenue, feb_revenue. Bien que certains SGBD proposent un operateur PIVOT natif, l'approche portable et universellement attendue en entretien repose sur des expressions CASE a l'interieur de fonctions d'agregation.

Le premier exemple illustre un pivot mensuel du chiffre d'affaires par produit. Chaque expression CASE isole les lignes correspondant a un mois specifique, tandis que la fonction SUM agrege les montants.

-- pivot_monthly_revenue.sql
-- Monthly revenue pivot: one row per product, one column per month
SELECT
  product_id,
  product_name,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1
    THEN quantity * unit_price ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2
    THEN quantity * unit_price ELSE 0 END) AS feb_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3
    THEN quantity * unit_price ELSE 0 END) AS mar_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4
    THEN quantity * unit_price ELSE 0 END) AS apr_revenue,
  -- Repeat for remaining months
  SUM(quantity * unit_price) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-01'
GROUP BY product_id, product_name
ORDER BY total_revenue DESC;

La meme technique d'agregation conditionnelle s'applique a des analyses multidimensionnelles. L'exemple suivant segmente l'activite utilisateur par type d'appareil et calcule le pourcentage de sessions mobiles — un indicateur frequemment demande dans les entretiens orientes produit.

-- pivot_user_activity.sql
-- User activity: sessions by day of week and device type
SELECT
  user_id,
  COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) AS mobile_sessions,
  COUNT(CASE WHEN device_type = 'desktop' THEN 1 END) AS desktop_sessions,
  COUNT(CASE WHEN device_type = 'tablet' THEN 1 END) AS tablet_sessions,
  ROUND(
    COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) * 100.0 / COUNT(*),
    1
  ) AS mobile_pct
FROM user_sessions
WHERE session_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) >= 5  -- Only users with meaningful activity
ORDER BY mobile_pct DESC;

La clause HAVING filtre les utilisateurs avec un nombre de sessions insuffisant pour etre statistiquement significatif. Ce type de filtrage post-agregation est un point regulierement aborde en entretien pour verifier la comprehension de l'ordre d'execution des clauses SQL.

Pivot Dynamique avec CROSSTAB sous PostgreSQL

Lorsque les categories du pivot ne sont pas connues a l'avance, l'extension tablefunc de PostgreSQL met a disposition la fonction CROSSTAB. Cette approche est particulierement utile pour les rapports dont les dimensions varient dynamiquement — par exemple, un pivot trimestriel dont les trimestres futurs ne sont pas encore disponibles dans les donnees.

-- crosstab_dynamic_pivot.sql
-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Revenue by product and quarter using CROSSTAB
SELECT *
FROM crosstab(
  $$
    SELECT
      product_name,
      'Q' || EXTRACT(QUARTER FROM order_date)::TEXT AS quarter,
      SUM(quantity * unit_price) AS revenue
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE EXTRACT(YEAR FROM o.order_date) = 2026
    GROUP BY product_name, quarter
    ORDER BY product_name, quarter
  $$,
  $$ VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4') $$
) AS pivot_table(
  product_name TEXT,
  q1_revenue NUMERIC,
  q2_revenue NUMERIC,
  q3_revenue NUMERIC,
  q4_revenue NUMERIC
);

La premiere sous-requete dollar-quoted produit les donnees source au format (categorie_ligne, categorie_colonne, valeur). La seconde sous-requete definit explicitement les categories attendues en colonnes. Cette separation permet au planificateur de gerer les valeurs manquantes (un produit sans ventes au Q3, par exemple) en inserant automatiquement des NULL.

Optimisation de Requetes SQL : Plans EXPLAIN et Diagnostic de Performance

La capacite a diagnostiquer une requete lente distingue les analystes de donnees operationnels des profils purement theoriques. La commande EXPLAIN ANALYZE constitue l'outil principal pour comprendre comment le planificateur PostgreSQL execute une requete et identifier les goulots d'etranglement.

-- explain_analyze_example.sql
-- Analyze a slow query to identify bottlenecks
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count,
  SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id, c.name
HAVING SUM(o.total_amount) > 1000
ORDER BY lifetime_value DESC;

Plusieurs indicateurs dans la sortie EXPLAIN meritent une attention particuliere. Un Seq Scan (parcours sequentiel) sur une table volumineuse signale generalement l'absence d'un index adequat. La comparaison entre les lignes estimees (rows) et les lignes reellement traitees (actual rows) revele la precision des statistiques du planificateur : un ecart important indique des statistiques obsoletes, corrigeables par un ANALYZE sur la table concernee. Les informations BUFFERS distinguent les pages lues depuis le cache (shared hit) de celles lues depuis le disque (shared read), fournissant un indicateur direct de l'efficacite du cache de PostgreSQL.

Le type de jointure selectionne (Nested Loop, Hash Join ou Merge Join) depend de la taille des jeux de donnees et de la presence d'index. Un Hash Join est generalement choisi pour les grandes jointures sans index, tandis qu'un Nested Loop avec Index Scan est optimal pour les jointures ou une table est significativement plus petite que l'autre.

Strategies d'Indexation pour l'Optimisation SQL

L'indexation represente le levier d'optimisation le plus direct pour ameliorer les temps de reponse des requetes. Les entretiens techniques evaluent la connaissance des differents types d'index et la capacite a choisir la strategie appropriee en fonction du pattern de requetes.

Un index simple sur une colonne frequemment utilisee dans les clauses WHERE ameliore les filtres sur cette colonne. Un index composite couvre plusieurs colonnes, et l'ordre de declaration des colonnes determine les requetes pouvant en beneficier : la regle du prefixe gauche stipule que l'index n'est exploitable que si la requete filtre sur les premieres colonnes declarees.

-- indexing_strategies.sql
-- Index on order_date: filters a small percentage of rows
CREATE INDEX idx_orders_date ON orders (order_date);

-- Composite index for queries filtering on both columns
CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, order_date);

-- Covering index: includes columns needed in SELECT
-- Enables Index Only Scan (no table access)
CREATE INDEX idx_orders_covering
  ON orders (customer_id, order_date)
  INCLUDE (total_amount, status);

L'index couvrant (covering index) avec la clause INCLUDE ajoute des colonnes supplementaires aux feuilles de l'arbre B-tree sans les integrer aux cles de recherche. Cette technique permet un Index Only Scan — la requete est entierement satisfaite par l'index sans acces a la table sous-jacente, eliminant une categorie complete d'operations d'entree/sortie.

Les index partiels offrent une optimisation supplementaire en ne couvrant qu'un sous-ensemble des lignes de la table. Sur des tables ou la majorite des donnees sont archivees ou inactives, un index partiel sur les lignes actives produit un index considerablement plus petit et plus rapide a parcourir.

-- partial_index.sql
-- Only index active orders (smaller, faster index)
CREATE INDEX idx_orders_active
  ON orders (customer_id, order_date)
  WHERE status = 'active';

-- Only index recent data for dashboard queries
CREATE INDEX idx_orders_recent
  ON orders (order_date)
  WHERE order_date >= '2026-01-01';

Le planificateur PostgreSQL n'utilise un index partiel que lorsque la clause WHERE de la requete correspond a la condition de l'index. Cette specificite est regulierement testee en entretien pour verifier la comprehension fine du fonctionnement des index.

Anti-patterns de Requetes Courants et Leurs Corrections

Certains patterns d'ecriture SQL, bien que syntaxiquement valides, neutralisent les optimisations du moteur de base de donnees. Savoir les reconnaitre et les corriger fait partie des competences evaluees dans les entretiens techniques avances.

L'application d'une fonction sur une colonne indexee constitue l'anti-pattern le plus repandu. Lorsqu'une fonction transforme la valeur d'une colonne avant la comparaison, le planificateur ne peut plus utiliser l'index existant sur cette colonne, car les valeurs stockees dans l'index ne correspondent plus aux valeurs comparees.

-- anti_patterns.sql
-- BAD: function on indexed column disables the index
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- GOOD: range comparison uses the index
SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

La reecriture en comparaison de plage preserve l'utilisation de l'index tout en produisant un resultat fonctionnellement identique. Ce refactoring represente un gain de performance souvent considerable sur les tables volumineuses.

Le second anti-pattern concerne l'utilisation de NOT IN avec des sous-requetes susceptibles de contenir des valeurs NULL. Ce piege est particulierement insidieux car la requete s'execute sans erreur mais retourne un jeu de resultats vide. En logique ternaire SQL, toute comparaison avec NULL produit UNKNOWN, et NOT IN se comporte comme une conjonction de conditions NOT EQUAL — si l'une d'elles est UNKNOWN, l'ensemble de la condition l'est egalement.

-- not_in_null_trap.sql
-- BAD: returns empty if any customer_id is NULL in subquery
SELECT * FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders
);

-- GOOD: NOT EXISTS handles NULLs correctly
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

NOT EXISTS traite correctement les valeurs NULL car la condition porte sur l'existence d'une correspondance, et non sur une comparaison de valeurs. Ce pattern est systematiquement preferable a NOT IN pour les exclusions basees sur des sous-requetes.

Conclusion

La preparation aux entretiens SQL pour les postes de data analyst en 2026 repose sur la maitrise de plusieurs domaines complementaires. Les points essentiels a retenir :

• Les sous-requetes correlees se reexecutent pour chaque ligne du jeu de resultats externe — les refactoriser en CTEs ou en fonctions de fenetre ameliore significativement les performances sur les tables volumineuses
• EXISTS surpasse IN pour les verifications d'appartenance, en particulier lorsque la sous-requete retourne un grand nombre de lignes ou contient des valeurs NULL
• L'agregation conditionnelle avec CASE constitue la technique pivot universelle et portable — la maitriser constitue un prealable avant d'aborder les syntaxes PIVOT specifiques a chaque SGBD
• EXPLAIN ANALYZE revele le plan d'execution reel de la requete ; l'attention doit se porter sur les Seq Scan, la precision des estimations de lignes et la strategie de jointure selectionnee
• L'ordre des colonnes dans les index composites doit correspondre aux patterns de filtrage des requetes — la regle du prefixe gauche s'applique systematiquement
• Les index partiels et couvrants reduisent les operations d'entree/sortie pour des patterns de requetes specifiques sans la surcharge d'index couvrant la totalite de la table
• Les fonctions appliquees sur des colonnes indexees, NOT IN avec des valeurs NULL et SELECT * dans les requetes de production constituent des anti-patterns a eliminer systematiquement