5 вопросов на собеседовании про A/B-тестирование в SQL25.03.2025 21:46

5de893fcd4b21745e157c06b042ed97d.jpg

Привет, Хабр!

Сегодня рассмотрим 5 вопросов на собеседовании про A/B‑тестирование в SQL.

Как посчитать конверсии и относительную разницу между группами A и B?

Это классика жанра: есть две группы A и B, и нужно посчитать конверсии, а затем — lift и delta. Подход с использованием CTE и группировки — это понятный способ решения задачи, который можно масштабировать на проекты.

Представим, что есть таблица events, где хранится информация о том, какая группа прошла тест и конвертировалась ли она (флаг converted): 

WITH conv AS (
    SELECT
        group_id,
        COUNT(*) AS total_visits,
        SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS conversions
    FROM events
    GROUP BY group_id
)
SELECT
    group_id,
    conversions,
    total_visits,
    ROUND((conversions::numeric / total_visits) * 100, 2) AS conversion_rate_percent,
    ROUND(
        (conversions::numeric / total_visits) -
        LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id),
    4) AS delta,
    ROUND(
        CASE
            WHEN LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id) = 0 THEN NULL
            ELSE ((conversions::numeric / total_visits) - LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id)) /
                 LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id)
        END,
    4) AS lift
FROM conv;

CTE помогает собрать агрегированные данные по группам, что делает основной запрос более читабельным. LAG используется для получения предыдущего значения, что позволяет посчитать разницу delta и относительный прирост lift.

Как использовать PERCENTILE_CONT для анализа медианных различий?

Когда дело доходит до A/B‑тестирования, среднее значение порой обманывает, особенно если в выборке есть выбросы. Именно медиана часто дает более точное представление о центральной тенденции. В Postgres для расчета медианы используется функция PERCENTILE_CONT.

Пример запроса для вычисления медианы по метрике в группах A и B: 

SELECT
    group_id,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY metric_value) AS median_value
FROM experiments
GROUP BY group_id;

Почему медиана важнее среднего? Скажем так: если в выборке 1000 значений и пару из них — настоящие аутсайдеры (напр., огромные значения, которые не отражают типичное поведение пользователей), то среднее может сдвинуться в одну сторону, тогда как медиана останется более стабильной.

Как интерпретировать p-value из SQL-запроса?

p-value позволяет понять, насколько наблюдаемые различия между группами значимы. В Postgres и ClickHouse есть возможности для проведения статистических тестов напрямую в SQL (хотя порой может потребоваться подключение сторонних расширений).

Например, если установлено расширение для проведения t‑теста, запрос может выглядеть следующим образом: 

SELECT
    student_t_test(
        ARRAY(SELECT metric_value FROM experiments WHERE group_id = 'A'),
        ARRAY(SELECT metric_value FROM experiments WHERE group_id = 'B')
    ) AS p_value;

На самом деле, таких возможностей мало, и зачастую приходится делать предварительную обработку данных в SQL, а затем выгружать их в Python или R для большего анализа. Вот как это может выглядеть, если мы решили доверить p‑value Python

-- Выгрузка данных для дальнейшего анализа
COPY (
    SELECT group_id, metric_value FROM experiments
) TO '/tmp/experiment_data.csv' WITH CSV HEADER;

А дальше, в Python: 

import pandas as pd
from scipy import stats

# Загрузка данных
data = pd.read_csv('/tmp/experiment_data.csv')

# Разделение на группы
group_a = data[data['group_id'] == 'A']['metric_value']
group_b = data[data['group_id'] == 'B']['metric_value']

# Проведение t-теста
t_stat, p_value = stats.ttest_ind(group_a, group_b, equal_var=False)
print("P-value:", p_value)

Даже если p‑value меньше 0.05 — это не всегда значит, что эффект практически значим.

Как агрегировать поведение пользователей при множественных визитах?

Один пользователь может совершать десятки, а то и сотни визитов. Нужно агрегировать эти данные, не потеряв при этом ценную информацию о каждом взаимодействии.

Предположим, есть таблица user_events с колонками user_id, event_time и action. Можно выделить первый визит каждого пользователя: 

SELECT
    user_id,
    event_time,
    action
FROM (
    SELECT
        user_id,
        event_time,
        action,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time ASC) AS visit_rank
    FROM user_events
) sub
WHERE visit_rank = 1;

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time ASC) нумерует визиты каждого пользователя по времени, начиная с самого раннего. Внешний запрос фильтрует результат, оставляя только первую запись visit_rank = 1 для каждого user_id.

Как сегментировать пользователей и посчитать разницу эффекта между сегментами?

Разные пользователи ведут себя по‑разному. Важно понимать, как различается эффект в зависимости от сегментации — будь то возраст, пол, источник трафика или геолокация. Здесь на помощь приходит конструкция CASE WHEN.

Пример запроса для сегментации пользователей по возрасту: 

SELECT
    CASE WHEN age > 30 THEN 'old' ELSE 'young' END AS age_group,
    COUNT(*) AS user_count,
    AVG(metric_value) AS avg_metric
FROM users
GROUP BY age_group;

Если нужно учесть и другие параметры, можно усложнить запрос: 

SELECT
    CASE WHEN age > 30 THEN 'old' ELSE 'young' END AS age_group,
    gender,
    COUNT(*) AS user_count,
    AVG(metric_value) AS avg_metric
FROM users
GROUP BY age_group, gender;

Стратификация по нескольким признакам позволяет получить более детальное понимание поведения аудитории. Например, может оказаться, что мужчины старше 30 ведут себя существенно иначе, чем женщины той же возрастной категории. Такие инсайты помогают корректировать маркетинговые стратегии и A/B‑тестирование, чтобы максимизировать ROI.

Статья подготовлена для будущих студентов специализации «Системный аналитик». Хорошая новость: в рамках этого курса студенты получат поддержку карьерного центра Otus. Узнать подробнее

© Habrahabr.ru