Une CTE peut calculer plusieurs colonnes issues de fonctions de fenêtre différentes, puis la requête englobante peut les combiner dans un même filtre avec AND.

Table reprise du chapitre 3 :

scores_manche
id | joueur | manche | score
1  | Nadia  | 1      | 10
2  | Nadia  | 2      | 14
3  | Nadia  | 3      | 14
4  | Boris  | 1      | 8
5  | Boris  | 2      | 12
6  | Boris  | 3      | 9

WITH progression AS (
  SELECT joueur, manche, score,
    score - LAG(score) OVER (PARTITION BY joueur ORDER BY manche) AS variation,
    MAX(score) OVER (PARTITION BY joueur ORDER BY manche) AS meilleur_score_jusquici
  FROM scores_manche
)
SELECT joueur, manche, score
FROM progression
WHERE variation > 0 AND score = meilleur_score_jusquici
ORDER BY joueur, manche;

MAX(score) OVER (PARTITION BY joueur ORDER BY manche) calcule, comme SUM ou AVG au chapitre 4, un maximum cumulé — le meilleur score obtenu jusqu'à cette manche incluse. Le filtre garde uniquement les manches où le score a progressé (variation > 0) et où ce score est un nouveau record personnel. Pour Nadia : la manche 2 (score 14, variation +4, nouveau record) est gardée ; la manche 3 (variation 0) ne l'est pas. Pour Boris : la manche 2 (score 12, variation +4, nouveau record) est gardée ; la manche 3 (variation -3) ne l'est pas.

À retenir :

• Une CTE peut préparer plusieurs colonnes de fenêtre indépendantes (ici, une variation via LAG et un maximum cumulé via MAX), pour les combiner ensuite dans un filtre unique.
• MAX() OVER (... ORDER BY ...), comme SUM et AVG au chapitre précédent, calcule par défaut un maximum cumulé — la même logique de cadre s'applique à toutes les fonctions d'agrégat utilisées en fenêtre.

À vous de jouer : combien de lignes ce résultat final contient-il au total ?