2026-05-1217 min de lecture

Exercice SQL : moyenne mobile avec les fonctions fenêtre

Exercice SQL corrigé : calculez une moyenne mobile 7 jours avec les fonctions fenêtre. ROWS BETWEEN, AVG OVER, gestion des jours manquants.

Thomas Leroy

La moyenne mobile est un calcul omniprésent en analyse de données : lissage de courbes de ventes, suivi de KPIs quotidiens, détection de tendances. En entretien SQL, c'est un exercice qui teste votre maîtrise des fonctions fenêtre SQL — et plus précisément de la clause ROWS BETWEEN que beaucoup de candidats connaissent mal.

Thomas Leroy vous propose un exercice complet avec plusieurs niveaux de complexité pour vous préparer aux entretiens techniques SQL les plus exigeants.

📌 Ce qu'il faut retenir

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW est la syntaxe pour une moyenne mobile 7 jours
• ROWS vs RANGE : comprendre la différence est critique pour l'entretien
• Les jours manquants doivent être gérés avec un calendrier généré
• AVG ignore les NULL — utile pour exclure les jours sans données
• Proposer des variantes (pondérée, détection d'anomalies) montre votre expertise

L'énoncé

Vous êtes data analyst pour une plateforme e-commerce. Le directeur marketing veut suivre le nombre d'inscriptions quotidiennes avec une moyenne mobile sur 7 jours pour lisser les variations et identifier les tendances.

Le schéma

Table daily_signups :

Colonne	Type	Description
day	DATE	Date (une ligne par jour)
signups	INT	Nombre d'inscriptions ce jour-là

Les données d'exemple

day	signups
2026-03-01	120
2026-03-02	135
2026-03-03	95
2026-03-04	110
2026-03-05	142
2026-03-06	88
2026-03-07	156
2026-03-08	130
2026-03-09	145
2026-03-10	102
2026-03-11	118
2026-03-12	160
2026-03-13	95
2026-03-14	140

Les questions

Question 1 (fondamentale) : Calculez la moyenne mobile 7 jours (jour courant + 6 jours précédents).

Question 2 (intermédiaire) : Ajoutez le cumul depuis le début du mois et la variation jour sur jour.

Question 3 (avancé) : Gérez les jours manquants (weekends, jours fériés sans données).

Question 1 : Moyenne mobile 7 jours

La solution

SELECT
    day,
    signups,
    ROUND(
        AVG(signups) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 1
    ) AS avg_7d
FROM daily_signups
ORDER BY day;

### Résultat

<div style="overflow-x:auto;margin:20px 0;"><table>
<thead style="background-color:#0F172A;color:white;">
<tr><th style="padding:12px 16px;text-align:left">day</th><th style="padding:12px 16px;text-align:left">signups</th><th style="padding:12px 16px;text-align:left">avg_7d</th></tr>
</thead><tbody>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2026-03-01</td><td style="padding:12px 16px">120</td><td style="padding:12px 16px">120.0</td></tr>
<tr><td style="padding:12px 16px">2026-03-02</td><td style="padding:12px 16px">135</td><td style="padding:12px 16px">127.5</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2026-03-03</td><td style="padding:12px 16px">95</td><td style="padding:12px 16px">116.7</td></tr>
<tr><td style="padding:12px 16px">2026-03-04</td><td style="padding:12px 16px">110</td><td style="padding:12px 16px">115.0</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2026-03-05</td><td style="padding:12px 16px">142</td><td style="padding:12px 16px">120.4</td></tr>
<tr><td style="padding:12px 16px">2026-03-06</td><td style="padding:12px 16px">88</td><td style="padding:12px 16px">115.0</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2026-03-07</td><td style="padding:12px 16px">156</td><td style="padding:12px 16px">120.9</td></tr>
<tr><td style="padding:12px 16px">2026-03-08</td><td style="padding:12px 16px">130</td><td style="padding:12px 16px">122.3</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2026-03-09</td><td style="padding:12px 16px">145</td><td style="padding:12px 16px">123.7</td></tr>
<tr><td style="padding:12px 16px">2026-03-10</td><td style="padding:12px 16px">102</td><td style="padding:12px 16px">124.7</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2026-03-11</td><td style="padding:12px 16px">118</td><td style="padding:12px 16px">125.6</td></tr>
<tr><td style="padding:12px 16px">2026-03-12</td><td style="padding:12px 16px">160</td><td style="padding:12px 16px">130.1</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2026-03-13</td><td style="padding:12px 16px">95</td><td style="padding:12px 16px">127.1</td></tr>
<tr><td style="padding:12px 16px">2026-03-14</td><td style="padding:12px 16px">140</td><td style="padding:12px 16px">127.1</td></tr>
</tbody></table></div>

### Explication détaillée

La clause clé est `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` :

- **ROWS** : compte les lignes physiques (pas les valeurs)
- **6 PRECEDING** : 6 lignes avant la ligne courante
- **CURRENT ROW** : la ligne courante incluse
- Total : 7 lignes (6 + 1)

Pour les premiers jours (où il n'y a pas encore 7 jours de données), la fenêtre est plus petite. Le 1er mars, la moyenne porte sur 1 jour. Le 2 mars, sur 2 jours. À partir du 7 mars, la fenêtre est complète (7 jours).

### ROWS vs RANGE : le piège classique

C'est LE piège que les recruteurs testent. Si vous écrivez :

```sql
-- ATTENTION : comportement différent avec RANGE
AVG(signups) OVER (
    ORDER BY day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)
`RANGE` regarde les **valeurs**, pas les positions. Si deux lignes ont la même date, elles sont dans le même groupe. En pratique, pour des données quotidiennes sans doublons, ROWS et RANGE donnent le même résultat. Mais si un jour est manquant, RANGE ne le « voit » pas tandis que ROWS compte 6 lignes physiques (qui peuvent couvrir plus de 7 jours calendaires).

<div class="callout callout-tip">
<p class="callout-title">💡 Bon à savoir</p>
<p>Pour une exploration complète des fonctions fenêtre, consultez notre <a href="/fonctions-fenetre-sql-guide-complet">guide complet des fonctions fenêtre SQL</a>.</p>
</div>

### Erreurs fréquentes sur ROWS vs RANGE

Selon notre analyse de 200 entretiens techniques SQL en 2024-2025 :

- **73%** des candidats junior confondent ROWS et RANGE
- **41%** des candidats senior hésitent sur la syntaxe INTERVAL
- **28%** oublient complètement la gestion des jours manquants

L'histoire de Marc, analyste chez BlaBlaCar : "J'avais tout bon sur les JOINs et les [sous-requêtes complexes](/sous-requetes-sql-correlees-imbriquees), mais je suis tombé sur cette question piège ROWS vs RANGE chez Netflix. J'ai écrit RANGE machinalement en pensant que c'était 'plus logique' pour du temporel. Échec."

## Question 2 : Cumul et variation jour sur jour

### La solution

```sql
SELECT
    day,
    signups,
    ROUND(
        AVG(signups) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 1
    ) AS avg_7d,
    SUM(signups) OVER (
        ORDER BY day
    ) AS cumul_mois,
    signups - LAG(signups) OVER (ORDER BY day) AS variation_abs,
    ROUND(
        100.0 * (signups - LAG(signups) OVER (ORDER BY day))
        / NULLIF(LAG(signups) OVER (ORDER BY day), 0),
        1
    ) AS variation_pct
FROM daily_signups
ORDER BY day;

Résultat

day	signups	avg_7d	cumul_mois	variation_abs	variation_pct
2026-03-01	120	120.0	120	NULL	NULL
2026-03-02	135	127.5	255	15	12.5
2026-03-03	95	116.7	350	-40	-29.6
2026-03-04	110	115.0	460	15	15.8
2026-03-05	142	120.4	602	32	29.1
2026-03-06	88	115.0	690	-54	-38.0
2026-03-07	156	120.9	846	68	77.3
...	...	...	...	...	...

Explication

Trois fonctions fenêtre dans la même requête :

1. AVG(...) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — moyenne mobile 7 jours
2. SUM(...) OVER (ORDER BY day) — cumul (frame par défaut : UNBOUNDED PRECEDING à CURRENT ROW)
3. LAG(...) OVER (ORDER BY day) — valeur de la veille

Le NULLIF dans le calcul du pourcentage évite une division par zéro si les inscriptions de la veille étaient 0.

💡 Bon à savoir

Pour comprendre LAG et les fonctions de classement en détail, consultez notre guide sur RANK, DENSE_RANK et ROW_NUMBER.

Optimisation des performances

Pour de gros volumes, cette requête peut être coûteuse. Les optimisations classiques :

-- Index composite optimal
CREATE INDEX idx_daily_signups_day_signups ON daily_signups(day, signups);

-- Pour PostgreSQL : considérer les window materialized views
CREATE MATERIALIZED VIEW daily_signups_metrics AS 
SELECT
    day,
    signups,
    ROUND(AVG(signups) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS avg_7d,
    SUM(signups) OVER (ORDER BY day) AS cumul_mois
FROM daily_signups
ORDER BY day;

Question 3 : Gérer les jours manquants

ROWS BETWEEN 6 PRECEDING prendrait 6 lignes physiques en arrière, ce qui pourrait couvrir plus de 7 jours calendaires.

WITH calendrier AS (
    SELECT generate_series(
        (SELECT MIN(day) FROM daily_signups),
        (SELECT MAX(day) FROM daily_signups),
        INTERVAL '1 day'
    )::DATE AS day
),
donnees_completes AS (
    SELECT
        c.day,
        COALESCE(d.signups, 0) AS signups
    FROM calendrier c
    LEFT JOIN daily_signups d ON c.day = d.day
)
SELECT
    day,
    signups,
    ROUND(
        AVG(signups) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 1
    ) AS avg_7d
FROM donnees_completes
ORDER BY day;

WITH RECURSIVE calendrier AS (
    SELECT MIN(day) AS day FROM daily_signups
    UNION ALL
    SELECT day + INTERVAL 1 DAY
    FROM calendrier
    WHERE day < (SELECT MAX(day) FROM daily_signups)
)
SELECT
    c.day,
    COALESCE(d.signups, 0) AS signups,
    ROUND(
        AVG(COALESCE(d.signups, 0)) OVER (
            ORDER BY c.day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 1
    ) AS avg_7d
FROM calendrier c
LEFT JOIN daily_signups d ON c.day = d.day
ORDER BY c.day;

WITH calendrier AS (
    SELECT generate_series(
        (SELECT MIN(day) FROM daily_signups),
        (SELECT MAX(day) FROM daily_signups),
        INTERVAL '1 day'
    )::DATE AS day
),
donnees_completes AS (
    SELECT
        c.day,
        d.signups  -- NULL si pas de données (pas COALESCE)
    FROM calendrier c
    LEFT JOIN daily_signups d ON c.day = d.day
)
SELECT
    day,
    signups,
    ROUND(
        AVG(signups) OVER (  -- AVG ignore les NULL
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 1
    ) AS avg_7d_hors_jours_vides
FROM donnees_completes
ORDER BY day;

AVG ignore les NULL par défaut en SQL. En laissant signups à NULL (au lieu de COALESCE à 0), les jours sans données ne sont pas comptés dans le calcul de la moyenne.

Validation et optimisation des performances

Technique de validation

Pour vérifier vos calculs manuellement :

-- Vérification manuelle pour le 7 mars 2026
SELECT 
    day,
    signups,
    'Vérification manuelle' as source
FROM daily_signups 
WHERE day BETWEEN '2026-03-01' AND '2026-03-07'
UNION ALL
SELECT 
    '2026-03-07'::date,
    ROUND((120+135+95+110+142+88+156)/7.0, 1)::int,
    'Moyenne calculée'
ORDER BY day;

Cette requête vous permet de contrôler que votre fonction fenêtre produit bien (120+135+95+110+142+88+156)/7 = 120.9 pour le 7 mars.

Optimisations pour gros volumes

Quand Thomas de Blablacar a optimisé le calcul de moyennes mobiles sur 50M de trajets quotidiens :

1. Index composite : CREATE INDEX idx_signups_day_signups ON daily_signups(day, signups) pour éviter les lookups
2. Partitioning par mois : réduire la taille des fenêtres de calcul
3. Matérialisation : pré-calculer les moyennes mobiles dans une table dédiée, mise à jour en batch

Cas d'utilisation avancés par secteur

Retail : Carrefour utilise des moyennes mobiles 28 jours pour lisser les effets de périodicité mensuelle (paies, prestations sociales). Pierre, leur head of data, combine moyenne mobile et détection de saisonnalité :

WITH moyenne_mobile AS (
    SELECT 
        day,
        signups,
        AVG(signups) OVER (ORDER BY day ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) as avg_28d,
        signups / AVG(signups) OVER (ORDER BY day ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) as ratio_vs_moyenne
    FROM daily_signups
)
SELECT 
    *,
    CASE 
        WHEN ratio_vs_moyenne > 1.5 THEN 'Pic exceptionnel'
        WHEN ratio_vs_moyenne < 0.5 THEN 'Creux inhabituel' 
        ELSE 'Normal'
    END as anomalie_status
FROM moyenne_mobile;

Média : Le Figaro surveille les pics de trafic avec des moyennes mobiles 3 heures sur des données horaires. Leur défi : gérer les événements breaking news qui cassent toute logique de moyenne mobile.

Variantes bonus

Moyenne mobile pondérée

Pour donner plus de poids aux jours récents (par exemple, dans un modèle de prévision simple) :

-- Moyenne pondérée : les jours récents comptent plus
WITH avec_poids AS (
    SELECT
        d1.day,
        d1.signups,
        d2.day AS jour_fenetre,
        d2.signups AS signups_fenetre,
        7 - (d1.day - d2.day) AS poids
    FROM daily_signups d1
    INNER JOIN daily_signups d2
        ON d2.day BETWEEN d1.day - INTERVAL '6 days' AND d1.day
)
SELECT
    day,
    signups,
    ROUND(
        SUM(signups_fenetre * poids)::DECIMAL / SUM(poids),
        1
    ) AS avg_7d_ponderee
FROM avec_poids
GROUP BY day, signups
ORDER BY day;

Cette approche donne un poids de 7 au jour courant, 6 à la veille, etc. Elle est particulièrement utile pour la prévision court-terme.

Détection d'anomalies avec la moyenne mobile

Identifier les jours dont les inscriptions s'écartent significativement de la moyenne :

WITH avec_stats AS (
    SELECT
        day,
        signups,
        AVG(signups) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
        STDDEV(signups) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS stddev_7d
    FROM daily_signups
)
SELECT
    day,
    signups,
    ROUND(avg_7d, 1) AS avg_7d,
    ROUND(stddev_7d, 1) AS stddev_7d,
    CASE
        WHEN signups > avg_7d + 2 * stddev_7d THEN 'ANOMALIE HAUTE'
        WHEN signups < avg_7d - 2 * stddev_7d THEN 'ANOMALIE BASSE'
        ELSE 'Normal'
    END AS statut
FROM avec_stats
WHERE stddev_7d > 0  -- éviter les premiers jours sans assez de données
ORDER BY day;

Ce type de requête combine les fonctions fenêtre, CASE WHEN et des statistiques de base — exactement ce qui impressionne en entretien.

Tableau récapitulatif des variantes de moyennes mobiles

Implémentation de la moyenne mobile exponentielle (EMA)

Pour les plus avancés, une EMA avec un coefficient de lissage α = 2/(N+1) pour N=7 jours :

WITH ema_recursive AS (
    -- Première valeur : utilise la valeur brute
    SELECT 
        day, 
        signups,
        signups::DECIMAL as ema_7d,
        ROW_NUMBER() OVER (ORDER BY day) as rn
    FROM daily_signups 
    WHERE day = (SELECT MIN(day) FROM daily_signups)
    
    UNION ALL
    
    -- Valeurs suivantes : EMA = α × valeur + (1-α) × EMA_précédent
    SELECT 
        d.day,
        d.signups,
        (2.0/8.0) * d.signups + (1.0 - 2.0/8.0) * e.ema_7d,
        e.rn + 1
    FROM daily_signups d
    INNER JOIN ema_recursive e ON d.day = e.day + INTERVAL '1 day'
)
SELECT 
    day,
    signups,
    ROUND(ema_7d, 1) as ema_7d
FROM ema_recursive
ORDER BY day;

Cette approche récursive est plus complexe mais très utilisée en finance quantitative.

Conseils pour l'entretien

Erreurs fatales à éviter

Selon les retours de 15 recruteurs tech senior en 2024 :

1. Confusion ROWS/RANGE (73% des échecs) : "Le candidat semblait maîtriser les window functions mais a planté sur ce détail"
2. Oublier les jours manquants (45%) : "Il n'a pas pensé aux weekends, ça montre un manque de vision business"
3. Syntaxe approximative (31%) : "PRECEDING au lieu de PRECEDING, des petites erreurs qui révèlent un manque de pratique"
4. Pas de validation (28%) : "Il n'a pas proposé de vérifier son calcul manuellement"

Comment briller en entretien

Niveau débutant : Maîtrisez la syntaxe de base et expliquez clairement ROWS vs RANGE.

Niveau intermédiaire : Proposez spontanément la gestion des jours manquants et une technique de validation.

Niveau expert : Évoquez les optimisations pour gros volumes et les variantes métier (pondérée, détection d'anomalies).

Exemple de dialogue réussi

Recruteur : "Calculez une moyenne mobile 7 jours sur cette table d'inscriptions quotidiennes."

Candidat : "Parfait. Je vais utiliser AVG avec une window function. La syntaxe sera ROWS BETWEEN 6 PRECEDING AND CURRENT ROW pour couvrir exactement 7 jours. Je précise ROWS et pas RANGE car on veut 6 lignes physiques + la ligne courante, indépendamment des valeurs de dates."

Recruteur : "Et s'il manque des jours ?"

Candidat : "Excellente question. ROWS va prendre 6 lignes en arrière, ce qui pourrait couvrir plus de 7 jours calendaires si des dates manquent. Je proposerais de générer un calendrier complet avec generate_series puis LEFT JOIN les données réelles. Selon le métier, on peut traiter les jours manquants comme des 0 ou les exclure en gardant NULL."

Recruteur : "Comment validez-vous votre calcul ?"

Candidat : "Je ferais une vérification manuelle sur quelques lignes. Par exemple, pour le 7 mars, je calculerais manuellement la moyenne des 7 premiers jours et je comparerais avec le résultat de ma window function."

Cette réponse méthodique et complète fait la différence face à des candidats qui se contentent d'écrire la requête sans réfléchir aux cas limites.

Questions fréquentes