Ce cas pratique referme le module entier en combinant, dans une seule requête, un classement par filière (RANK), une mention (CASE sur une moyenne de fenêtre) et une progression (FIRST_VALUE/LAST_VALUE) — trois techniques indépendantes, réunies comme au chapitre 6.

WITH bilan AS (
  SELECT DISTINCT e.nom, e.filiere,
    AVG(n.note) OVER w AS moyenne_finale,
    RANK() OVER (PARTITION BY e.filiere ORDER BY AVG(n.note) OVER w DESC) AS rang_filiere,
    LAST_VALUE(n.note) OVER w - FIRST_VALUE(n.note) OVER w AS ecart_total
  FROM notes_examens n
  JOIN etudiants e ON e.id = n.etudiant_id
  WINDOW w AS (
    PARTITION BY n.etudiant_id ORDER BY n.session
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
)
SELECT nom, filiere, moyenne_finale, rang_filiere,
  CASE
    WHEN moyenne_finale >= 16 THEN $q2$mention tres bien$q2$
    WHEN moyenne_finale >= 14 THEN $q2$mention bien$q2$
    WHEN moyenne_finale >= 12 THEN $q2$mention assez bien$q2$
    ELSE $q2$admis sans mention$q2$
  END AS mention,
  ecart_total
FROM bilan
ORDER BY filiere, rang_filiere;

Étape par étape :

1. La fenêtre nommée w (avec son cadre complet) calcule à la fois la moyenne finale, un classement par filière et l'écart de progression, en une seule définition partagée.
2. rang_filiere classe chaque étudiant parmi les membres de sa propre filière selon sa moyenne finale.
3. mention transforme cette moyenne en étiquette lisible.
4. Résultat final : Amina (info, rang 1, moyenne 14, mention bien, écart +4) ; Bruno (info, rang 2, moyenne 12, mention assez bien, écart +4) ; Chiara (gestion, rang 1, moyenne 16, mention tres bien, écart +3) ; Driss (gestion, rang 2, moyenne 13, mention assez bien, écart +4).

À retenir :

• Ce rapport final combine, dans une seule requête lisible, la quasi-totalité des techniques vues dans ce module : PARTITION BY, RANK, une fenêtre nommée à cadre complet, FIRST_VALUE/LAST_VALUE et CASE — la synthèse de tout le parcours sur les fonctions de fenêtrage.

À vous de jouer : quelle mention obtient l'étudiant classé rang_filiere = 1 en filière gestion ?