StrataScratch (TikTok) — % подтверждённых регистраций в 24 часа

Условие

Таблицы:

• signups(user_id, signup_time)
• confirmations(user_id, confirm_time) — может содержать множественные попытки.

Найдите долю пользователей, которые подтвердили email в течение 24 часов после регистрации.

Решение

WITH first_confirm AS (
  SELECT user_id, MIN(confirm_time) AS first_confirm
  FROM confirmations
  GROUP BY user_id
)
SELECT
  ROUND(
    SUM(CASE
          WHEN fc.first_confirm IS NOT NULL
           AND fc.first_confirm <= s.signup_time + INTERVAL '24 hours'
          THEN 1 ELSE 0
        END)::DECIMAL / COUNT(*),
    4
  ) AS confirmation_rate_24h
FROM signups s
LEFT JOIN first_confirm fc ON fc.user_id = s.user_id;

Почему MIN(confirm_time)

У одного юзера может быть несколько подтверждений (повторно прислал/прошёл «реверификацию»). Логично смотреть первое подтверждение — оно решает, попадает ли он в 24 часа.

Что в знаменателе

COUNT(*) по signups — все зарегистрированные. Это правильный знаменатель «доли подтверждённых». Если бы делили на «всех, у кого есть confirm» — это уже другая метрика (confirmation success среди тех, кто пытался).

Подводные камни

1. + INTERVAL '24 hours' в MySQL. Синтаксис DATE_ADD(s.signup_time, INTERVAL 24 HOUR).
2. Подтверждение до регистрации. Маловероятно, но возможно из-за рассогласования часов или back-dating. first_confirm < signup_time — это аномалия; решите, считать ли её «подтверждённым в срок».
3. LEFT JOIN важен. INNER JOIN выкинет тех, кто не подтверждал — знаменатель занизится.
4. Round(..., 4) для процентов. Если просят в %, умножьте на 100; иначе доля 0.XXXX.

Эталонный ответ

LEFT JOIN signups с MIN(confirm_time) per user, проверка first_confirm <= signup_time + 24h, отношение к COUNT(*).