SQL pour les Data Analysts : fonctions de fenetrage, CTE et requetes avancees

Les fonctions de fenetrage SQL, les CTE (Common Table Expressions) et les patterns de requetes avancees constituent le socle technique de tout data analyst. Que ce soit pour preparer un entretien technique ou pour resoudre des problematiques de reporting complexes, ces techniques transforment des sous-requetes imbriquees et difficiles a lire en SQL propre, performant et maintenable.

Comprendre les fonctions de fenetrage et la clause OVER

Une fonction de fenetrage applique un calcul sur une "fenetre" de lignes definie par la clause OVER. Cette clause determine quelles lignes entrent dans la fenetre et dans quel ordre elles sont traitees. Contrairement aux fonctions d'agregation avec GROUP BY, les fonctions de fenetrage conservent chaque ligne individuelle dans le jeu de resultats.

La syntaxe generale suit un schema coherent sur tous les principaux moteurs de bases de donnees : PostgreSQL, MySQL 8+, BigQuery, SQL Server et Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY divise les donnees en groupes (similaire a GROUP BY, mais sans reduire les lignes). ORDER BY definit la sequence au sein de chaque partition. La clause de cadrage optionnelle (ROWS BETWEEN ...) restreint la fenetre a une plage specifique de lignes. Bien maitriser ces trois elements est indispensable pour repondre aux questions SQL posees en entretien data analyst.

ROW_NUMBER, RANK et DENSE_RANK : les differences essentielles

Ces trois fonctions de classement se ressemblent en apparence, mais leur comportement diverge lorsque des valeurs identiques (egalites) existent. Choisir la mauvaise fonction est une source frequente de bugs dans les requetes analytiques et un piege classique lors des entretiens techniques.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER attribue toujours un entier sequentiel unique. Meme en cas d'egalite, une ligne recoit arbitrairement le numero inferieur. RANK attribue le meme numero aux lignes a egalite mais saute les numeros suivants (1, 1, 3). DENSE_RANK gere egalement les egalites mais ne saute jamais de numero (1, 1, 2). Pour les requetes de type top-N ou les doublons doivent partager la meme position, DENSE_RANK est generalement le choix correct.

LAG et LEAD : analyse de periode a periode

LAG et LEAD permettent d'acceder aux donnees des lignes precedentes ou suivantes sans recourir a des auto-jointures. Ces fonctions sont indispensables pour calculer la croissance d'une periode a l'autre, detecter des tendances et identifier des variations. C'est l'un des patterns les plus frequemment testes lors des entretiens SQL pour les postes de data analyst.

-- period_over_period.sql
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Le deuxieme argument de LAG/LEAD specifie le decalage (la valeur par defaut est 1). Un troisieme argument optionnel fournit une valeur par defaut lorsqu'aucune ligne n'existe a ce decalage, ce qui est utile pour eviter les valeurs NULL sur la premiere ou la derniere ligne. Le NULLIF dans le calcul de croissance previent les erreurs de division par zero.

NTILE et segmentation par quantiles

NTILE distribue les lignes en un nombre specifie de groupes de taille approximativement egale. Les data analysts l'utilisent pour l'analyse par quartiles, le scoring par deciles et la segmentation client.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

Le quartile 1 contient les plus gros acheteurs, le quartile 4 les plus faibles. Ce pattern correspond directement a la segmentation RFM (Recence, Frequence, Montant) utilisee en analytics marketing. En entretien, il est courant de devoir expliquer comment ce type de segmentation eclaire les decisions metier : ciblage des campagnes, identification des clients a risque de desabonnement ou priorisation des efforts de fidelisation.

Common Table Expressions : remplacer les sous-requetes imbriquees

Les CTE (clauses WITH) decomposent des requetes complexes en etapes nommees et lisibles. Chaque CTE agit comme un jeu de resultats temporaire nomme qui n'existe que pendant la duree de la requete. Au-dela de la lisibilite, les CTE facilitent le debogage puisque chaque etape peut etre testee independamment.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

Cette approche en trois etapes remplace ce qui serait autrement une sous-requete profondement imbriquee. Chaque CTE a une responsabilite claire : agregation, classement, puis filtrage. Lors d'un entretien, structurer une requete complexe de cette maniere demontre une pensee methodique et une maitrise des bonnes pratiques SQL.

CTE recursives pour les donnees hierarchiques

Les CTE recursives resolvent des problemes impliquant des donnees hierarchiques ou de type graphe : organigrammes, arborescences de categories, nomenclatures de produits et requetes de recherche de chemins.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Le cas de base selectionne les noeuds racines (les employes sans responsable hierarchique). Le cas recursif effectue une jointure avec la CTE elle-meme, construisant la hierarchie niveau par niveau. La colonne management_chain concatene les noms pour afficher le chemin complet de reporting. La plupart des bases de donnees limitent la profondeur de recursion pour eviter les boucles infinies : PostgreSQL fixe par defaut cette limite a 100 iterations.

Patterns analytiques avances : gaps, islands et totaux cumulatifs

Le pattern gaps-and-islands identifie des sequences consecutives dans les donnees : periodes d'abonnement actives, jours de connexion consecutifs ou series de production ininterrompues. Cette technique combine ROW_NUMBER avec l'arithmetique sur les dates et figure regulierement parmi les questions avancees posees en entretien SQL.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Le principe fondamental repose sur une observation mathematique : pour des dates consecutives, soustraire un numero de ligne incrementiel a la date produit toujours la meme valeur. Lorsqu'un ecart survient, la valeur resultante change, creant un nouveau groupe. Cette requete identifie toutes les series de connexions d'au moins 3 jours consecutifs par utilisateur. Ce type de requete est couramment utilise dans les equipes produit pour mesurer l'engagement utilisateur.

Combiner les fonctions de fenetrage avec CASE pour des analyses conditionnelles

Les requetes analytiques en conditions reelles combinent frequemment des fonctions de fenetrage avec des expressions CASE pour calculer des metriques conditionnelles au sein d'une meme requete. Cette capacite a ecrire des requetes SQL avancees en une seule passe, sans sous-requetes ni auto-jointures, est un marqueur de competence valorise en entretien.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Cette requete unique calcule une moyenne mobile sur 7 jours, un cumul depuis le debut du trimestre qui se reinitialise a chaque trimestre, et un indicateur de valeurs aberrantes. Tout cela sans sous-requete ni auto-jointure. Comprendre comment combiner ces differentes fenetres dans une meme requete est un atout majeur lors des entretiens techniques.

Optimisation des performances des requetes analytiques

Les fonctions de fenetrage et les CTE sont des outils puissants, mais ils peuvent devenir des goulots d'etranglement sur de gros volumes de donnees. Plusieurs techniques permettent de maintenir des performances satisfaisantes a grande echelle.

Premierement, indexer les colonnes utilisees dans les clauses PARTITION BY et ORDER BY. Un index composite correspondant a la definition de la fenetre elimine les operations de tri couteuses.

Deuxiemement, filtrer les donnees avant d'appliquer les fonctions de fenetrage. Placer les conditions dans la clause WHERE (avant l'evaluation des fonctions de fenetrage) reduit le jeu de donnees que la fenetre doit traiter.

Troisiemement, eviter les definitions de fenetres redondantes. Les fenetres nommees reduisent la repetition et signalent a l'optimiseur que plusieurs fonctions partagent le meme cadrage :

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

La clause WINDOW (supportee par PostgreSQL, MySQL 8+ et BigQuery) definit la fenetre une seule fois et la reutilise pour plusieurs fonctions. Cela ameliore a la fois la lisibilite et le potentiel d'optimisation du plan d'execution de la requete.

Conclusion

• Les fonctions de fenetrage (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) operent sur des ensembles de lignes sans les reduire, contrairement aux agregats avec GROUP BY
• Les CTE decomposent les requetes complexes en etapes nommees et testables, remplacant les sous-requetes profondement imbriquees
• Les CTE recursives gerent les donnees hierarchiques comme les organigrammes et les arborescences de categories, mais necessitent des limites de profondeur et des index sur les colonnes de jointure
• Le pattern gaps-and-islands (ROW_NUMBER + arithmetique sur les dates) identifie les sequences consecutives dans les donnees temporelles
• Les fenetres nommees (clause WINDOW) reduisent la duplication de code et peuvent ameliorer le plan d'execution des requetes
• La combinaison de fonctions de fenetrage avec CASE permet de calculer des metriques conditionnelles complexes en une seule requete