Senior

SQL-Aufgabe: Berechnung der Conversion nach Experimentgruppen (exp_id=90) vom Ereignis Epic Match Start bis Epic Fight Win pro Benutzer, mit korrekter Zuordnung der Ereignisse zum Experiment durch einen Join nach Zeit.

sobes.tech KI

Antwort von 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 по времени гарантирует, что события относятся именно к периоду участия пользователя в эксперименте.

Zurück Als nächstes