SQL avancé : 7 astuces pour analyser vos données comme un pro

Imaginez un tableau de transactions comme un tableau Excel géant où chaque ligne représente un achat, un abonnement ou un remboursement. Maintenant, imaginez devoir répondre à des questions comme : « Combien de jours s’écoulent entre deux achats pour un même client ? », « Quels clients sont passés du forfait basique au premium ? » ou encore « Quels sont les clients qui dépensent le plus ? »
Avec des requêtes SQL basiques, ces analyses deviennent un casse-tête. Mais avec les bonnes techniques, tout devient simple, rapide et même élégant.

CRÉER UNE TABLE DE TRANSACTIONS : LE POINT DE DÉPART

Commençons par créer une table qui ressemble à ce que l’on trouve dans une entreprise de logiciels en abonnement. Voici comment elle se présente :

CREATE TABLE transactions (
    transaction_id   SERIAL PRIMARY KEY,
    customer_id      INT,
    plan_type        VARCHAR(20),   -- 'starter', 'pro', 'enterprise'
    amount           NUMERIC(10,2),
    status           VARCHAR(20),   -- 'completed', 'refunded', 'failed'
    created_at       TIMESTAMP
);

Cette table contient 36 transactions réparties sur 7 clients, entre septembre 2023 et juin 2024. Elle permet de stocker toutes les informations nécessaires : qui a acheté quoi, à quelle date, pour quel montant, et si la transaction a abouti.

TECHNIQUE 1 : CALCULER LES JOURS ENTRE DEUX TRANSACTIONS AVEC LAG()

Première question : combien de jours s’écoulent entre deux achats pour un même client ? Pour répondre, on utilise LAG(), une fonction qui permet de récupérer la valeur de la ligne précédente dans une même colonne.

Voici la requête qui fait le travail :

SELECT
    customer_id,
    created_at,
    LAG(created_at) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
    ) AS previoustransactionat,
    ROUND(
        EXTRACT(EPOCH FROM (
            createdat - LAG(createdat) OVER (
                PARTITION BY customer_id
                ORDER BY created_at
            )
        )) / 86400
    ) AS dayssincelast
FROM transactions
WHERE status = 'completed'
ORDER BY customerid, createdat;

Le résultat ressemble à ceci :

customerid |     createdat      | previoustransactionat | dayssincelast
-------------+---------------------+-------------------------+-----------------
        3317 | 2024-01-03 11:02:00 |                         |
        3317 | 2024-03-15 10:45:00 | 2024-01-03 11:02:00     |              72
        3317 | 2024-05-22 09:30:00 | 2024-03-15 10:45:00     |              68
        4482 | 2023-09-10 09:00:00 |                         |
        4482 | 2023-10-10 09:00:00 | 2023-09-10 09:00:00     |              30
        4482 | 2023-11-10 09:14:00 | 2023-10-10 09:00:00     |              31
        .
(29 rows)

EXTRACT(EPOCH FROM .) convertit l’intervalle de temps en secondes, puis on divise par 86400 (le nombre de secondes dans une journée) pour obtenir le nombre de jours. La première ligne de chaque client affiche NULL car il n’y a pas de transaction précédente à comparer.

TECHNIQUE 2 : DÉTECTER LES CHANGEMENTS DE FORFAIT AVEC UN SELF-JOIN

Deuxième question : quels clients sont passés d’un forfait basique (« starter ») à un forfait premium (« pro » ou « enterprise ») ? Ici, on utilise un self-join, c’est-à-dire une jointure de la table avec elle-même.

La requête suivante permet de trouver ces clients :

SELECT DISTINCT t1.customer_id
FROM transactions t1
JOIN transactions t2
    ON  t1.customerid = t2.customerid
    AND t1.plan_type   = 'starter'
    AND t2.plan_type   = 'pro'
    AND t2.createdat  > t1.createdat
WHERE t1.status = 'completed'
  AND t2.status = 'completed'
ORDER BY t1.customer_id;

Le résultat est le suivant :

customer_id
-------------
        4482
        6204
        7891
(3 rows)

L’astuce ici est d’utiliser deux alias (t1 et t2) pour représenter deux moments différents pour le même client. La condition t2.createdat > t1.createdat garantit que l’on compare bien un forfait antérieur à un forfait ultérieur. DISTINCT évite les doublons si un client a plusieurs transactions « starter » avant de passer à « pro ».

Cette méthode fonctionne aussi pour détecter des baisses de forfait, des réactivations ou toute autre évolution temporelle.

TECHNIQUE 3 : RÉCUPÉRER LA MEILLEURE TRANSACTION PAR CLIENT AVEC ROW_NUMBER()

Troisième question : quelle est la transaction la plus importante (en montant) pour chaque client ? Pour répondre, on utilise ROW_NUMBER() dans une expression de table commune (CTE).

Voici comment faire :

WITH ranked AS (
    SELECT
        customer_id,
        transaction_id,
        amount,
        plan_type,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC, created_at DESC
        ) AS rn
    FROM transactions
    WHERE status = 'completed'
)
SELECT customerid, transactionid, amount, plan_type
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

Le résultat obtenu :

customerid  | transactionid  | amount  | plan_type
-------------+----------------+--------+------------
        3317 |             12 |  19.00 | starter
        4482 |              8 | 299.00 | enterprise
        5901 |             19 | 299.00 | enterprise
        6103 |             25 | 299.00 | enterprise
        6204 |             28 |  79.00 | pro
        7891 |             32 |  79.00 | pro
        8810 |             36 |  79.00 | pro
(7 rows)

ROW_NUMBER() attribue le numéro 1 à la ligne qui arrive en premier dans chaque partition (ici, par client). La clause ORDER BY amount DESC, created_at DESC permet de classer les transactions d’abord par montant décroissant, puis par date décroissante en cas d’égalité. Si vous voulez inclure les ex-aequo, remplacez ROW_NUMBER() par RANK() ou DENSE_RANK().

TECHNIQUE 4 : SEGMENTER LES CLIENTS PAR NIVEAU DE DÉPENSE AVEC NTILE()

Quatrième question : comment répartir les clients en quatre groupes selon leur dépense totale ? La fonction NTILE(4) divise les lignes en quatre groupes à peu près égaux et attribue un numéro à chaque ligne.

Voici la requête :

WITH customer_spend AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spend,
        COUNT(*) AS total_transactions
    FROM transactions
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spend,
    total_transactions,
    NTILE(4) OVER (ORDER BY totalspend) AS spendquartile
FROM customer_spend
ORDER BY total_spend DESC;

Le résultat obtenu :

customerid | totalspend | totaltransactions | spendquartile
-------------+-------------+--------------------+----------------
        5901 |     1495.00 |                  5 |              4
        6103 |      835.00 |                  5 |              3
        4482 |      653.00 |                  7 |              3
        8810 |      237.00 |                  3 |              2
        6204 |      177.00 |                  3 |              2
        7891 |      177.00 |                  3 |              1
        3317 |       57.00 |                  3 |              1
(7 rows)

NTILE(4) crée des quartiles automatiques. Le quartile 4 regroupe les plus gros dépensiers, tandis que le quartile 1 regroupe les plus petits. Contrairement à un CASE WHEN qui fixe des seuils statiques, NTILE() s’adapte automatiquement à l’évolution de la base de données.

TECHNIQUE 5 : LISSEZ VOS DONNÉES AVEC UNE MOYENNE MOBILE SUR 3 MOIS

Cinquième question : comment lisser les variations mensuelles pour mieux voir la tendance générale ? Une moyenne mobile sur trois mois permet de réduire le bruit et de faire ressortir les tendances.

Voici comment calculer cette moyenne mobile :

WITH monthly AS (
    SELECT
        DATETRUNC('month', createdat)::DATE AS month,
        SUM(amount) AS monthly_revenue
    FROM transactions
    WHERE status = 'completed'
    GROUP BY DATETRUNC('month', createdat)
)
SELECT
    month,
    monthly_revenue,
    ROUND(AVG(monthly_revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS revenue3moavg
FROM monthly
ORDER BY month;

Le résultat obtenu :

month    | monthlyrevenue | revenue3mo_avg
-------------+-----------------+-----------------
 2023-09-01  |           19.00 |           19.00
 2023-10-01  |           19.00 |           19.00
 2023-11-01  |           79.00 |           39.00
 2024-01-01  |          275.00 |          124.33
 2024-02-01  |          476.00 |          276.67
 2024-03-01  |          555.00 |          435.33
 2024-04-01  |          835.00 |          622.00
 2024-05-01  |          775.00 |          721.67
 2024-06-01  |          598.00 |          736.00
(9 rows)

La clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW indique que l’on veut calculer la moyenne sur la ligne actuelle et les deux lignes précédentes. Les deux premières lignes utilisent moins de données, car il n’y a pas de données antérieures disponibles. Pour une moyenne sur six mois, remplacez 2 PRECEDING par 5 PRECEDING.

TECHNIQUE 6 : AGRÉGER PLUSIEURS CONDITIONS EN UNE SEULE REQUÊTE AVEC FILTER

Sixième question : comment obtenir le chiffre d’affaires, les remboursements et le nombre d’échecs par mois, le tout dans une seule requête ? La clause FILTER permet d’appliquer une condition à une agrégation spécifique, sans multiplier les sous-requêtes.

Voici comment faire :

SELECT
    DATETRUNC('month', createdat) AS month,
    SUM(amount) FILTER (WHERE status = 'completed') AS revenue_completed,
    SUM(amount) FILTER (WHERE status = 'refunded')  AS revenue_refunded,
    COUNT(*)    FILTER (WHERE status = 'failed')    AS failed_count
FROM transactions
GROUP BY DATETRUNC('month', createdat)
ORDER BY month;

Le résultat obtenu :

month             | revenuecompleted | revenuerefunded | failed_count
------------------------+-------------------+------------------+--------------
 2023-09-01 00:00:00+00 |             19.00 |                  |            0
 2023-10-01 00:00:00+00 |             19.00 |                  |            0
 2023-11-01 00:00:00+00 |             79.00 |                  |            0
 2024-01-01 00:00:00+00 |            275.00 |                  |            0
 2024-02-01 00:00:00+00 |            476.00 |            79.00 |            1
 2024-03-01 00:00:00+00 |            555.00 |            79.00 |            0
 2024-04-01 00:00:00+00 |            835.00 |           299.00 |            0
 2024-05-01 00:00:00+00 |            775.00 |                  |            1
 2024-06-01 00:00:00+00 |            598.00 |                  |            2
(9 rows)

L’alternative à FILTER serait d’écrire trois sous-requêtes distinctes et de les fusionner avec des jointures. Cela rendrait la requête plus longue, moins lisible et souvent plus lente. Notez que SUM() avec FILTER retourne NULL si aucune ligne ne correspond à la condition, ce qui est logique : il n’y a eu aucun remboursement en septembre 2023, par exemple.

TECHNIQUE 7 : DÉTECTER LES SÉRIES CONSÉCUTIVES AVEC DES FONCTIONS DE FENÊTRE

Septième question : comment identifier les clients qui ont été actifs plusieurs mois de suite sans interruption ? Trouver des séries consécutives est l’un des problèmes les plus complexes en SQL. La solution classique utilise une fonction de fenêtre pour regrouper les mois actifs en séries sans recourir à une CTE récursive.

Voici la méthode :

WITH monthly_activity AS (
    SELECT
        customer_id,
        DATETRUNC('month', createdat)::DATE AS active_month
    FROM transactions
    WHERE status = 'completed'
    GROUP BY customerid, DATETRUNC('month', created_at)
),
with_prev AS (
    SELECT
        customer_id,
        active_month,
        LAG(active_month) OVER (
            PARTITION BY customer_id
            ORDER BY active_month
        ) AS prev_month
    FROM monthly_activity
),
streak_groups AS (
    SELECT
        customer_id,
        active_month,
        SUM(CASE WHEN activemonth = prevmonth + INTERVAL '1 month' THEN 0 ELSE 1 END)
            OVER (PARTITION BY customerid ORDER BY activemonth) AS streak_id
    FROM with_prev
),
streaks AS (
    SELECT
        customer_id,
        streak_id,
        MIN(activemonth) AS streakstart,
        MAX(activemonth) AS streakend,
        COUNT(*) AS streaklengthmonths
    FROM streak_groups
    GROUP BY customerid, streakid
)
SELECT customerid, streakstart, streakend, streaklength_months
FROM streaks
ORDER BY customerid, streakstart;

Le résultat obtenu :

customerid | streakstart | streakend | streaklength_months
-------------+--------------+------------+----------------------
        3317 | 2024-01-01   | 2024-01-01 |                    1
        3317 | 2024-03-01   | 2024-03-01 |                    1
        3317 | 2024-05-01   | 2024-05-01 |                    1
        4482 | 2023-09-01   | 2023-11-01 |                    3
        4482 | 2024-01-01   | 2024-01-01 |                    1
        4482 | 2024-03-01   | 2024-05-01 |                    3
        5901 | 2024-02-01   | 2024-06-01 |                    5
        6103 | 2024-01-01   | 2024-04-01 |                    4
        6103 | 2024-06-01   | 2024-06-01 |                    1
        .
(16 rows)

L’astuce repose sur l’attribution d’un numéro de série (streak_id) à chaque mois actif. Si deux mois sont consécutifs, ils reçoivent le même streak_id. Dès qu’il y a un écart, un nouveau streak_id est créé. La requête finale regroupe ensuite ces séries pour calculer leur durée.

Cette technique permet de détecter des séries de paiements réguliers, des abonnements actifs sans interruption, ou toute autre séquence où la continuité est importante.

POURQUOI CES TECHNIQUES CHANGENT TOUT

Ces sept techniques SQL ne sont pas de simples astuces : elles transforment vos requêtes en Outils puissants pour analyser des données complexes. Que ce soit pour calculer des écarts entre événements, détecter des évolutions dans le temps, segmenter des clients ou lisser des données, elles permettent de gagner un temps précieux et d’obtenir des résultats plus précis.

Avec ces méthodes, vos analyses ne seront plus limitées par la complexité des requêtes. Elles deviendront plus rapides, plus propres et plus faciles à mettre à l’échelle. Et surtout, elles vous donneront des insights bien plus pertinents pour prendre des décisions éclairées.

Alors, prêt à passer au niveau supérieur ? Copiez ces requêtes, testez-les sur vos propres données, et observez la différence. Vos analyses ne seront plus jamais les mêmes.