Les bons tricks SQL servent à analyser des comportements, pas juste à compter des lignes. Je parle ici de délais entre transactions, d’upgrades, de réactivations, de segmentation et de séries temporelles. C’est souvent là que SQL devient vraiment utile pour un data scientist.
Pourquoi SELECT ne suffit plus ?
SELECT, WHERE et GROUP BY, c’est la base. Je les utilise tous les jours. Mais si votre question business ressemble à “Qu’est-ce qui se passe avant qu’un client change d’offre ?” ou “Combien de temps entre deux paiements complétés ?”, là, un simple export agrégé devient vite trop court.
Le problème, c’est que les analyses vraiment utiles portent rarement sur un total isolé. Elles portent sur une trajectoire. Un client fait une action, puis une autre, puis il paie, puis il upgrade, puis il disparaît peut-être. Si on écrase tout ça dans un GROUP BY par mois ou par offre, on perd l’ordre des événements. Et souvent, c’est justement l’ordre qui explique le business.
Sur un jeu de données SaaS, ça arrive tout le temps. On veut comprendre des enchaînements simples, mais impossibles à lire proprement avec seulement les requêtes habituelles :
- Repérer des séries d’activité chez un client avant un paiement.
- Mesurer le délai entre deux paiements complétés.
- Comparer deux offres souscrites par le même client.
- Segmenter les clients selon leur niveau de dépense.
- Lisser une série temporelle pour éviter de surinterpréter un pic isolé.
- Suivre les changements d’offre dans le temps, pas juste l’offre actuelle.
C’est là que les patrons SQL deviennent intéressants. Je pars souvent d’une table transactions toute simple, avec un client, une date, une offre, un montant, un statut. Puis j’utilise des fonctions comme LAG et LEAD. LAG permet de regarder la ligne précédente d’un client. LEAD permet de regarder la ligne suivante. C’est très pratique pour comparer un événement avec celui d’avant ou celui d’après, sans sortir les données de la base.
La self-join aussi reste un classique. C’est juste une table jointe avec elle-même. Ça paraît bizarre au début, mais c’est très utile pour comparer deux transactions du même client, par exemple un paiement initial et un paiement suivant.
J’ai souvent vu des équipes data perdre du temps à refaire en notebook ce que SQL sait déjà très bien préparer proprement. Python est très utile, évidemment. Mais si la base peut déjà ordonner, comparer, segmenter et préparer les trajectoires, autant lui laisser faire ce travail proprement avant d’exporter quoi que ce soit.
Quel dataset utiliser pour tester ?
Pour tester des tricks SQL, j’aime partir d’un dataset petit, lisible, mais pas trop propre. Le but n’est pas d’avoir 10 millions de lignes. Le but, c’est d’avoir assez de matière pour vérifier un raisonnement analytique sans passer son temps à comprendre les données.
J’utilise une table transactions qui simule l’activité d’une entreprise SaaS. Chaque ligne représente un événement de paiement ou de souscription. Le dataset contient 36 transactions pour 7 clients, entre septembre 2023 et juin 2024. C’est volontairement compact, mais déjà suffisant pour faire apparaître des cas réalistes : plusieurs transactions par client, des changements d’offre, des montants différents, des délais variables entre deux événements.
Les colonnes sont simples. La colonne transaction_id identifie chaque transaction. La colonne customer_id permet de rattacher plusieurs événements au même client. La colonne plan_type indique l’offre choisie, par exemple basic, pro ou enterprise. La colonne amount contient le montant payé. La colonne status sert à distinguer les transactions, notamment celles qui sont completed. La colonne created_at permet d’ordonner les événements dans le temps, ce qui devient vite indispensable pour travailler sur la rétention, les upgrades ou les délais entre achats.
En pratique, je charge ce dataset depuis un fichier seed.sql, puis j’exécute mes requêtes dessus. C’est le genre de base que j’utilise souvent avec des clients pour valider une logique avant de la brancher sur les vraies tables, souvent beaucoup plus sales.
CREATE TABLE transactions (transaction_id INTEGER, customer_id INTEGER, plan_type TEXT, amount NUMERIC, status TEXT, created_at TIMESTAMP);
| Colonne | Rôle analytique | Exemple d’usage |
| transaction_id | Identifier chaque événement | Compter les transactions uniques |
| customer_id | Relier les transactions à un client | Calculer le revenu par client |
| plan_type | Analyser l’offre choisie | Repérer les upgrades ou downgrades |
| amount | Mesurer la valeur financière | Calculer le chiffre d’affaires |
| status | Filtrer les transactions valides | Garder uniquement les paiements completed |
| created_at | Ordonner les événements dans le temps | Mesurer les délais entre deux achats |
Comment mesurer le délai entre événements ?
Pour mesurer le délai entre deux événements en SQL, j’utilise presque toujours LAG. LAG récupère la valeur de la ligne précédente dans une fenêtre ordonnée. LEAD fait l’inverse, il récupère la ligne suivante. C’est parfait pour calculer le temps entre deux transactions d’un même client, sans écrire une self-join inutile. Une self-join, c’est quand on rejoint une table avec elle-même, et là franchement, ça alourdit souvent la requête pour rien.
Le raisonnement est simple. Je filtre d’abord les transactions completed si mon analyse porte sur les paiements réellement validés. Je partitionne ensuite par customer_id, pour ne jamais mélanger les clients entre eux. Puis j’ordonne par created_at, parce que le délai n’a de sens que si la chronologie est propre. Après ça, je compare la date courante avec la date précédente.
WITH completed_transactions AS (
SELECT
customer_id,
transaction_id,
created_at
FROM transactions
WHERE status = 'completed'
),
transactions_with_previous AS (
SELECT
customer_id,
transaction_id,
created_at,
LAG(created_at) OVER (
PARTITION BY customer_id
ORDER BY created_at
) AS previous_created_at
FROM completed_transactions
)
SELECT
customer_id,
transaction_id,
created_at,
previous_created_at,
EXTRACT(EPOCH FROM (created_at - previous_created_at)) / 86400 AS days_since_previous_transaction
FROM transactions_with_previous
ORDER BY customer_id, created_at;Le résultat attendu est assez intuitif. La première transaction de chaque client aura NULL dans previous_created_at, parce qu’il n’existe pas d’événement précédent pour ce client. Ce NULL est normal, ce n’est pas une erreur. Il dit juste “je n’ai rien avant”.
LEAD fonctionne exactement de la même manière, mais pour regarder vers l’avant. Au lieu de calculer le délai depuis la transaction précédente, je peux calculer le délai jusqu’à la prochaine transaction.
Dans une analyse réelle, je vérifie toujours deux choses avant d’interpréter les délais :
- Que l’ordre temporel est fiable, surtout si plusieurs événements ont le même created_at.
- Que le statut est cohérent avec la question métier, parce qu’une transaction annulée ou en échec peut complètement fausser la lecture.
J’ai déjà vu des analyses de réachat gonflées juste parce que les transactions failed étaient incluses. Sur le papier, le client “revenait”. Dans la réalité, il n’avait jamais payé.
Quand utiliser une self-join ?
Une self-join, je l’utilise quand je veux comparer une ligne avec d’autres lignes de la même table. C’est très utile quand on cherche une relation entre deux événements distincts du même client. Typiquement : trouver les clients qui sont passés d’une offre starter à une offre pro.
La logique est simple. On prend la table transactions deux fois, avec deux alias. t1 représente l’événement de départ, ici l’achat ou l’activation du plan starter. t2 représente un événement suivant, ici le passage au plan pro. On joint les deux sur customer_id, puis on ajoute t2.created_at > t1.created_at pour garder uniquement les événements qui arrivent après. Sinon, on pourrait raconter une fausse histoire dans le mauvais sens, et ça arrive vite sur des données réelles.
SELECT DISTINCT t1.customer_id, t1.created_at AS starter_date, t2.created_at AS pro_date
FROM transactions t1
JOIN transactions t2
ON t1.customer_id = t2.customer_id
AND t2.created_at > t1.created_at
WHERE t1.plan_type = 'starter'
AND t2.plan_type = 'pro'
AND t1.status = 'completed'
AND t2.status = 'completed';Le DISTINCT est important ici. Un même client peut avoir plusieurs transactions starter avant une transaction pro. La jointure va alors créer plusieurs paires possibles entre les événements. Ce n’est pas forcément un bug. C’est juste la conséquence logique de la self-join : elle compare toutes les lignes compatibles entre elles.
J’ai déjà vu ce point créer de la confusion chez un client. Ils pensaient avoir des doublons sales dans leur base. En fait non, leur modèle capturait plusieurs tentatives, plusieurs paiements, puis une conversion pro. La requête faisait exactement ce qu’on lui demandait.
La même structure sert aussi à repérer des downgrades, des retours après interruption ou des changements d’offre. Le principe ne change pas : une ligne représente l’état avant, une autre ligne représente l’état après, et la condition de date impose la chronologie.
| Approche | Meilleur usage | Avantage | Point d’attention |
| LAG / LEAD | Comparer une ligne avec la ligne précédente ou suivante dans une séquence. | Très lisible pour suivre un parcours ordonné. | Moins pratique si plusieurs événements possibles doivent être comparés. |
| Self-join | Comparer une ligne avec plusieurs autres lignes de la même table. | Très flexible pour détecter des relations entre événements. | Peut générer plusieurs paires pour un même client, ce qui est normal. |
Alors, vous le gardez dans SQL ou vous l’exportez ?
SQL ne sert pas seulement à sortir un total par mois ou une liste de clients. Avec LAG, LEAD et les self-joins, je peux déjà répondre à des questions beaucoup plus proches du business : combien de temps entre deux transactions, qui upgrade, qui revient, qui change de trajectoire. Le point important, c’est l’ordre des événements et le bon niveau de comparaison. Si les données sont bien préparées dans SQL, l’analyse devient plus propre derrière, que vous continuiez dans un dashboard, Python ou un modèle IA. Le bénéfice pour vous : moins de bricolage, plus de réponses fiables.
FAQ
- Pourquoi un data scientist doit connaître des tricks SQL avancés ?
Parce que beaucoup de questions analytiques commencent dans la base de données. Mesurer un délai entre deux événements, suivre un changement d’offre ou comparer plusieurs transactions d’un même client se fait souvent plus proprement en SQL avant de passer à Python, à un dashboard ou à un modèle. - À quoi sert LAG en SQL ?
LAG sert à récupérer une valeur située sur la ligne précédente dans une fenêtre ordonnée. Dans une table de transactions, je peux par exemple récupérer la date de transaction précédente d’un client, puis calculer le nombre de jours entre les deux paiements. - Quelle différence entre LAG et LEAD ?
LAG regarde vers l’événement précédent, LEAD regarde vers l’événement suivant. Les deux fonctionnent avec une clause OVER, souvent avec PARTITION BY customer_id et ORDER BY created_at quand on analyse une chronologie client. - Quand utiliser une self-join en SQL ?
J’utilise une self-join quand je dois comparer deux événements de la même table. Par exemple, une transaction starter et une transaction pro du même client, avec une condition temporelle pour vérifier que l’upgrade arrive après l’offre initiale. - Pourquoi filtrer sur les transactions completed ?
Parce qu’une transaction échouée, annulée ou non finalisée peut fausser l’analyse. Si je mesure un délai entre paiements ou un passage d’offre, je veux généralement travailler sur des événements réellement validés.
A propos de l’auteur
Je suis Franck Scandolera, expert et formateur en tracking avancé server-side, Analytics Engineering, automatisation No/Low Code avec n8n, intégration de l’IA en entreprise et SEO/GEO. J’accompagne des équipes data, marketing et business sur des sujets très concrets : fiabiliser la donnée, automatiser les analyses, structurer les pipelines et rendre les décisions plus propres. J’ai travaillé avec des références comme Logis Hôtel, Yelloh Village, BazarChic, la Fédération Française de Football ou Texdecor. Je dirige l’agence webAnalyste et l’organisme Formations Analytics. Si vous voulez mettre vos données au travail pour de vrai, contactez-moi.
⭐ Analytics engineer, Data Analyst et Automatisation IA indépendant ⭐
Ref clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Football Français, Texdecor…
Mon terrain de jeu :
Data Analyst & Analytics engineering : tracking avancé (GA4, Matomo, Piano, GTM server, Tealium, Commander Act, e-commerce, CAPI, RGPD), entrepôt de données (BigQuery, Snowflake, PostgreSQL, ClickHouse), modèles (Airflow, dbt, Dataform), dashboards décisionnels (Looker, Power BI, Metabase, SQL, Python).
Automatisation IA des taches Data, Marketing, RH, compta etc : conception de workflows intelligents robustes (n8n, App Script, scraping) connectés aux API de vos outils et LLM (OpenAI, Mistral, Claude…).
Engineering IA pour créer des applications et agent IA sur mesure : intégration de LLM (OpenAI, Mistral…), RAG, assistants métier, génération de documents complexes, APIs, backends Node.js/Python.