Senior

SQL užduotis: apskaičiuoti konversiją pagal eksperimentų grupes (exp_id=90) nuo įvykio Epic Match Start iki Epic Fight Win pagal vartotojus, su teisingu įvykių priskyrimu eksperimentui per laiko jungimą.

sobes.tech AI

Atsakymas iš AI

Для подсчёта конверсии по группам эксперимента (exp_id=90) из события Epic Match Start в Epic Fight Win с правильной атрибуцией через join по времени, нужно:

Определить пользователей и время их участия в эксперименте с exp_id=90.
Присоединить события Epic Match Start и Epic Fight Win к пользователям, учитывая, что событие должно происходить в период участия пользователя в эксперименте.
Посчитать для каждой группы эксперимента количество пользователей, у которых было событие Epic Match Start, и сколько из них дошли до Epic Fight Win.

Пример SQL-запроса (структура таблиц условная):

WITH experiment_users AS (
  SELECT user_id, exp_group, start_time, end_time
  FROM experiments
  WHERE exp_id = 90
),
match_start AS (
  SELECT user_id, event_time
  FROM events
  WHERE event_name = 'Epic Match Start'
),
fight_win AS (
  SELECT user_id, event_time
  FROM events
  WHERE event_name = 'Epic Fight Win'
),
user_events AS (
  SELECT
    eu.user_id,
    eu.exp_group,
    ms.event_time AS match_start_time,
    fw.event_time AS fight_win_time
  FROM experiment_users eu
  LEFT JOIN match_start ms ON ms.user_id = eu.user_id
    AND ms.event_time BETWEEN eu.start_time AND eu.end_time
  LEFT JOIN fight_win fw ON fw.user_id = eu.user_id
    AND fw.event_time BETWEEN eu.start_time AND eu.end_time
    AND fw.event_time >= ms.event_time
)
SELECT
  exp_group,
  COUNT(DISTINCT user_id) AS users_started,
  COUNT(DISTINCT CASE WHEN fight_win_time IS NOT NULL THEN user_id END) AS users_won,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN fight_win_time IS NOT NULL THEN user_id END) / NULLIF(COUNT(DISTINCT user_id), 0), 2) AS conversion_percent
FROM user_events
GROUP BY exp_group;

В этом запросе мы:

Фильтруем пользователей по эксперименту 90.
Присоединяем события старта и победы, учитывая временные рамки эксперимента для каждого пользователя.
Считаем конверсию как отношение пользователей с победой к пользователям со стартом.

Важно, что join по времени гарантирует, что события относятся именно к периоду участия пользователя в эксперименте.

Ankstesnis Kitas