SQL task: calculate conversion by experiment groups (exp_id=90) from Epic Match Start event to Epic Fight Win based on users, with correct event attribution to the experiment via join on time.

To calculate conversion rates by experiment groups (exp_id=90) from the Epic Match Start event to the Epic Fight Win event with proper attribution through a time join, you need to:

1. Identify users and their participation time in the experiment with exp_id=90.
2. Join the Epic Match Start and Epic Fight Win events to users, considering that the event should occur during the user's participation period in the experiment.
3. Count for each experiment group the number of users who had the Epic Match Start event, and how many of them reached the Epic Fight Win event.

Example SQL query (table structure is conditional):

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;

In this query, we:

• Filter users by experiment 90.
• Join start and win events, considering the experiment time frames for each user.
• Calculate conversion as the ratio of users with a win to users with a start.

It is important that the time join guarantees that events are specifically related to the user's participation period in the experiment.