# SQL pour les Data Analysts : fonctions de fenetrage, CTE et requetes avancees > Guide complet sur les fonctions de fenetrage SQL (window functions), les CTE et les requetes analytiques avancees. Syntaxe, exemples concrets et patterns utilises en entretien data analyst. - Published: 2026-04-09 - Updated: 2026-04-09 - Author: SharpSkill - Tags: sql, data-analytics, window-functions, cte, interview - Reading time: 10 min --- 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. > **Repere cle** > > Les fonctions de fenetrage effectuent des calculs sur un ensemble de lignes liees a la ligne courante, sans les reduire en une seule ligne de sortie comme le fait GROUP BY. Combinees aux CTE, elles rendent les requetes analytiques complexes lisibles et faciles a maintenir. ## 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. ```sql -- 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. ```sql -- 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. ```sql -- 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. ```sql -- 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. ```sql -- 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. ```sql -- 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. > **Performance des CTE recursives** > > Les CTE recursives peuvent etre lentes sur de gros volumes de donnees. Il est recommande d'inclure systematiquement une limite de profondeur (WHERE depth < 10) et de s'assurer que la colonne de jointure (manager_id) est indexee. Pour les hierarchies tres profondes, les patterns de chemin materialise (materialized path) ou d'ensembles imbriques (nested sets) constituent des alternatives plus performantes. ## 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. ```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. ```sql -- 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. > **Ordre d'execution des fonctions de fenetrage** > > Les fonctions de fenetrage s'executent apres WHERE, GROUP BY et HAVING, mais avant ORDER BY et LIMIT. Cela signifie qu'elles ne peuvent pas etre utilisees directement dans une clause WHERE. Pour filtrer sur le resultat d'une fonction de fenetrage, il faut encapsuler la requete dans une CTE ou une sous-requete. ## 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 : ```sql -- 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 --- Source: SharpSkill (https://sharpskill.dev), tech interview preparation for your real stack. HTML version of this page: https://sharpskill.dev/fr/blog/data-analytics/sql-window-functions-ctes-advanced-queries