Gain, significativité et garde-fous en SQL
Calculer le gain de conversion et les contrôles de données qui signalent une expérience défaillante
Gain, significativité et garde-fous en SQL est une leçon Coding Interview Prep gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage Coding Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Coding Interview Prep comprend 4 leçons au total.
Des indicateurs à la décision
La conversion par variante n'est qu'un début. La question posée en entretien est la suivante : le traitement a-t-il réellement gagné ? Cela signifie calculer le gain, déterminer si la différence est réelle ou due au bruit, et vérifier les indicateurs garde-fous qui permettent de détecter une expérience défaillante.
Vous n'exécuterez pas un ensemble complet d'outils statistiques en SQL, mais vous pouvez calculer les données nécessaires et fournir le signal approximatif de significativité que les recruteurs veulent voir.
La CTE récapitulative par variante
Tout ce qui suit repose sur un récapitulatif clair : pour chaque variante, le nombre d'utilisateurs n, le nombre d'utilisateurs convertis c et le taux de conversion p. Calculez-le une seule fois dans une CTE et réutilisez-le.
WITH summary AS (
SELECT
variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat
GROUP BY variant
)
SELECT
variant, n, c,
1.0 * c / n AS p
FROM summary;Gain absolu ou relatif
Il existe deux définitions du gain, et les recruteurs attendent par défaut la définition relative :
- Gain absolu = p_traitement - p_contrôle (points de pourcentage).
- Gain relatif = (p_traitement - p_contrôle) / p_contrôle (amélioration en pourcentage).
Dire « une augmentation de 2 points » ou « un gain relatif de 20 % » peut décrire le même résultat. Soyez explicite.
Calculer le gain avec un auto-pivot
Pour comparer deux variantes sur une seule ligne, placez le contrôle et le traitement côte à côte au moyen d'une agrégation conditionnelle, puis effectuez le calcul.
Vous évitez ainsi une auto-jointure fragile et conservez une formule de gain facile à lire.
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
),
rates AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p_ctrl,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p_trt
FROM s
)
SELECT
p_ctrl, p_trt,
p_trt - p_ctrl AS abs_lift,
ROUND(100.0 * (p_trt - p_ctrl) / p_ctrl, 2) AS rel_lift_pct
FROM rates;Pourquoi une différence peut être due au bruit
Un taux plus élevé pour le traitement peut être dû à la chance dans l'échantillonnage aléatoire. La significativité pose la question suivante : quelle est la probabilité d'observer un écart de cette ampleur si les variantes étaient réellement identiques ?
L'élément essentiel est l'erreur standard de chaque taux, qui diminue lorsque la taille de l'échantillon augmente. Les grands échantillons rendent les faibles gains fiables ; les petits échantillons rendent suspects même les gains importants.
Erreur standard d'une proportion
Pour un taux de conversion p calculé sur n utilisateurs, l'erreur standard est sqrt(p * (1 - p) / n). Calculez-la directement en SQL pour chaque variante.
Cela quantifie les fluctuations de chaque taux avant de les comparer.
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
)
SELECT
variant, n,
1.0 * c / n AS p,
SQRT( (1.0*c/n) * (1 - 1.0*c/n) / n ) AS std_err
FROM s;Un score z pour deux proportions
Un signal approximatif de significativité est le score z pour deux proportions : la différence entre les taux divisée par l'erreur standard de cette différence. Une valeur absolue supérieure à environ 1.96 correspond au seuil courant de 95 %.
Précisez qu'il s'agit d'une approximation et non d'un remplacement d'une analyse statistique appropriée, mais qu'elle permet de répondre en SQL à la question « ce résultat est-il vraisemblablement réel ? »
WITH r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p1,
MAX(CASE WHEN variant='control' THEN n END) AS n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p2,
MAX(CASE WHEN variant='treatment' THEN n END) AS n2
FROM (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
) s
)
SELECT
p2 - p1 AS abs_lift,
(p2 - p1) / SQRT( p1*(1-p1)/n1 + p2*(1-p2)/n2 ) AS z_score
FROM r;Interpréter le score z
Transformez le nombre en conclusion afin que le recruteur perçoive votre sens des enjeux métier, et pas uniquement les mathématiques :
|z| >= 1.96: la différence est significative avec un niveau de confiance d'environ 95 %.|z| < 1.96: les preuves sont insuffisantes ; le gain peut être dû au bruit.
Utilisez CASE pour produire une étiquette lisible et associez toujours la significativité à l'ampleur concrète du gain.
SELECT
z_score,
CASE WHEN ABS(z_score) >= 1.96
THEN 'significant at 95%'
ELSE 'not significant' END AS verdict
FROM (
SELECT 2.3 AS z_score
) t;Écart de répartition de l'échantillon (SRM)
Le premier garde-fou examiné par les recruteurs est le suivant : la répartition réelle des utilisateurs correspond-elle à celle prévue ? Une expérience 50/50 qui aboutit à 53/47 avec des millions d'utilisateurs est un signal d'alerte : la randomisation ou la journalisation ne fonctionne pas.
Comparez les nombres observés à la répartition attendue. Un écart important invalide toute l'expérience avant même que vous n'examiniez l'indicateur.
WITH cnt AS (
SELECT variant, COUNT(DISTINCT user_id) AS n
FROM experiment_flat GROUP BY variant
),
tot AS (SELECT SUM(n) AS total FROM cnt)
SELECT
c.variant, c.n,
ROUND(100.0 * c.n / t.total, 2) AS observed_pct,
50.0 AS expected_pct
FROM cnt c CROSS JOIN tot t;Indicateurs garde-fous
Un garde-fou est un indicateur qui ne doit pas se dégrader, même si l'indicateur principal s'améliore. Les garde-fous classiques sont la latence de page, le taux de remboursement, le taux de désabonnement et le taux d'erreur.
Présentez-les par variante aux côtés de l'indicateur de réussite. Un traitement qui améliore la conversion mais double les remboursements n'est pas une réussite. Calculer spontanément les garde-fous témoigne d'un bon jugement produit.
SELECT
variant,
AVG(load_ms) AS avg_latency_ms,
ROUND(100.0 * SUM(refunded) / COUNT(*), 2) AS refund_rate_pct,
ROUND(100.0 * SUM(errored) / COUNT(*), 2) AS error_rate_pct
FROM experiment_flat
GROUP BY variant;Le bilan complet
Un bilan complet d'expérience apprécié des recruteurs réunit quatre éléments dans un même résultat : les taux par variante, le gain relatif, la conclusion de significativité et la vérification SRM. Organisez les CTE en couches et présentez le tout dans un tableau unique prêt à guider la décision.
Terminez en indiquant : résultat significatif, ampleur du gain acceptable, garde-fous sains, répartition équilibrée, donc déployer ou attendre.
WITH s AS (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
),
r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) p1,
MAX(CASE WHEN variant='control' THEN n END) n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) p2,
MAX(CASE WHEN variant='treatment' THEN n END) n2
FROM s
)
SELECT
ROUND(100.0*(p2-p1)/p1, 2) AS rel_lift_pct,
CASE WHEN ABS((p2-p1)/SQRT(p1*(1-p1)/n1 + p2*(1-p2)/n2)) >= 1.96
THEN 'significant' ELSE 'not significant' END AS verdict,
CASE WHEN ABS(1.0*n2/(n1+n2) - 0.5) > 0.02
THEN 'SRM warning' ELSE 'split ok' END AS srm_check
FROM r;Vérification rapide
Le traitement affiche un gain relatif de 25 % sur la conversion, mais chaque variante ne compte que 40 utilisateurs. Quelle conclusion faut-il tirer ?
Récapitulatif : gain, significativité et garde-fous
Vous pouvez maintenant transformer les indicateurs bruts des variantes en décision :
- Distinguez le gain absolu (en points) du gain relatif (en pourcentage).
- Calculez l'erreur standard de chaque taux et un score z pour deux proportions comme signal approximatif de significativité (|z| >= 1.96, soit environ 95 %).
- Effectuez la vérification SRM pour confirmer que la répartition correspond à la conception prévue.
- Présentez les indicateurs garde-fous afin qu'une réussite ne dissimule pas une régression.
- Présentez un bilan unique prêt à guider la décision et associez toujours la significativité à l'ampleur concrète du gain.
L'analyse des entonnoirs et des expériences A/B en SQL est maintenant terminée.
Questions Fréquemment Posées
La leçon « Gain, significativité et garde-fous en SQL » est-elle gratuite ?
Oui — le texte complet de « Gain, significativité et garde-fous en SQL » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours Coding Interview Prep, passe à CoddyKit PRO. Le cours Coding Interview Prep comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Gain, significativité et garde-fous en SQL » ?
Calculer le gain de conversion et les contrôles de données qui signalent une expérience défaillante Tu pratiques Coding Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer Coding Interview Prep ?
Aucune expérience préalable n'est requise. Coding Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.
Combien de temps prend la leçon « Gain, significativité et garde-fous en SQL » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon Coding Interview Prep ?
Oui. Chaque leçon Coding Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Construire un entonnoir en plusieurs étapes
- Événements ordonnés et fenêtres temporelles
- Attribution des tests A/B et indicateurs
- Gain, significativité et garde-fous en SQL