Время отклика на письма

Решение

Задача требует нахождения времени отклика на письма, полученные на адрес поддержки. Нужно соединить исходное письмо с первым ответом и вычислить разницу во времени.

Подход к решению

Логика:

1. Найти все письма, полученные на support@company.com
2. Для каждого письма найти первый ответ (письмо от support обратно отправителю)
3. Вычислить разницу во времени в минутах
4. Учитывать только первый ответ

SQL-запрос (рекомендуемый вариант)

WITH incoming_emails AS (
  SELECT 
    id,
    subject,
    from_email,
    to_email,
    timestamp
  FROM emails
  WHERE to_email = 'support@company.com'
),
responses AS (
  SELECT 
    ie.id AS incoming_id,
    ie.subject,
    ie.from_email,
    ie.timestamp AS incoming_time,
    e.timestamp AS response_time,
    EXTRACT(EPOCH FROM (e.timestamp - ie.timestamp)) / 60 AS response_time_minutes,
    ROW_NUMBER() OVER (
      PARTITION BY ie.id 
      ORDER BY e.timestamp ASC
    ) AS response_rank
  FROM incoming_emails ie
  LEFT JOIN emails e ON (
    e.from_email = 'support@company.com'
    AND e.to_email = ie.from_email
    AND e.timestamp > ie.timestamp
  )
)
SELECT 
  incoming_id,
  subject,
  response_time_minutes
FROM responses
WHERE response_rank = 1 OR response_rank IS NULL
ORDER BY incoming_id;

Пошаговое объяснение

1. CTE incoming_emails

• Выделяем все письма, поступившие на support@company.com
• Это исходные письма, на которые нужно найти ответы

2. CTE responses

• LEFT JOIN emails e ON ... — соединяем с потенциальными ответами
• Условия соединения:

     - `e.from_email = 'support@company.com'` — письмо отправлено от support
     - `e.to_email = ie.from_email` — письмо отправлено отправителю исходного письма
     - `e.timestamp > ie.timestamp` — письмо отправлено ПОСЛЕ исходного письма
   

3. Расчёт времени отклика

• EXTRACT(EPOCH FROM (e.timestamp - ie.timestamp)) / 60 — разница в минутах
• EPOCH в PostgreSQL возвращает секунды, делим на 60 для получения минут
• Для MySQL: TIMESTAMPDIFF(MINUTE, ie.timestamp, e.timestamp)

4. ROW_NUMBER()

• PARTITION BY ie.id — для каждого входящего письма
• ORDER BY e.timestamp ASC — ответы в хронологическом порядке
• ROW_NUMBER() ... = 1 — выбираем только первый ответ

5. WHERE условие

• response_rank = 1 — первый ответ
• OR response_rank IS NULL — письма без ответа (NULL при LEFT JOIN)

Вариант для MySQL

WITH incoming_emails AS (
  SELECT 
    id,
    subject,
    from_email,
    to_email,
    timestamp
  FROM emails
  WHERE to_email = 'support@company.com'
),
responses AS (
  SELECT 
    ie.id AS incoming_id,
    ie.subject,
    ie.from_email,
    ie.timestamp AS incoming_time,
    e.timestamp AS response_time,
    TIMESTAMPDIFF(MINUTE, ie.timestamp, e.timestamp) AS response_time_minutes,
    ROW_NUMBER() OVER (
      PARTITION BY ie.id 
      ORDER BY e.timestamp ASC
    ) AS response_rank
  FROM incoming_emails ie
  LEFT JOIN emails e ON (
    e.from_email = 'support@company.com'
    AND e.to_email = ie.from_email
    AND e.timestamp > ie.timestamp
  )
)
SELECT 
  incoming_id,
  subject,
  response_time_minutes
FROM responses
WHERE response_rank = 1 OR response_rank IS NULL
ORDER BY incoming_id;

Альтернативный вариант с FIRST_VALUE

SELECT DISTINCT
  ie.id,
  ie.subject,
  EXTRACT(EPOCH FROM (
    FIRST_VALUE(e.timestamp) OVER (
      PARTITION BY ie.id 
      ORDER BY e.timestamp ASC
    ) - ie.timestamp
  )) / 60 AS response_time_minutes
FROM emails ie
LEFT JOIN emails e ON (
  e.from_email = 'support@company.com'
  AND e.to_email = ie.from_email
  AND e.timestamp > ie.timestamp
)
WHERE ie.to_email = 'support@company.com'
ORDER BY ie.id;

Пример выполнения

Исходные данные:

id | subject | from_email | to_email | timestamp
---|---------|------------|----------|---------------------
1  | Question | user@mail.com | support@company.com | 2024-01-01 10:00:00
2  | Re: Question | support@company.com | user@mail.com | 2024-01-01 10:30:00
3  | Another Q | user@mail.com | support@company.com | 2024-01-01 11:00:00
4  | Re: Another Q | support@company.com | user@mail.com | 2024-01-01 13:15:00

Анализ:

• Письмо 1 (входящее): от user@mail.com на support

  • Ответ: письмо 2 от support на user@mail.com в 10:30
  • Время отклика: 30 минут

• Письмо 3 (входящее): от user@mail.com на support

  • Ответ: письмо 4 от support на user@mail.com в 13:15
  • Время отклика: 135 минут

Результат:

incoming_id | subject | response_time_minutes
------------|---------|---------------------
1           | Question | 30
3           | Another Q | 135

Расширенный вариант с метриками

WITH incoming_emails AS (
  SELECT 
    id,
    subject,
    from_email,
    timestamp
  FROM emails
  WHERE to_email = 'support@company.com'
),
responses AS (
  SELECT 
    ie.id,
    ie.subject,
    TIMESTAMPDIFF(MINUTE, ie.timestamp, e.timestamp) AS response_time_minutes
  FROM incoming_emails ie
  LEFT JOIN emails e ON (
    e.from_email = 'support@company.com'
    AND e.to_email = ie.from_email
    AND e.timestamp > ie.timestamp
  )
  WHERE e.id = (
    SELECT MIN(e2.id)
    FROM emails e2
    WHERE e2.from_email = 'support@company.com'
    AND e2.to_email = ie.from_email
    AND e2.timestamp > ie.timestamp
  )
)
SELECT 
  id,
  subject,
  response_time_minutes,
  CASE 
    WHEN response_time_minutes IS NULL THEN 'No response'
    WHEN response_time_minutes <= 60 THEN '< 1 hour'
    WHEN response_time_minutes <= 240 THEN '1-4 hours'
    WHEN response_time_minutes <= 1440 THEN '4 hours - 1 day'
    ELSE '> 1 day'
  END AS response_category,
  ROUND(response_time_minutes::NUMERIC / 60, 2) AS response_hours
FROM responses
ORDER BY id;

Это добавляет категоризацию и время в часах.

Важные моменты

✓ LEFT JOIN — критично для включения писем без ответа (NULL значения)

✓ ROW_NUMBER() — гарантирует выбор только первого ответа

✓ timestamp > incoming_timestamp — исключает одновременно отправленные письма

✓ EXTRACT(EPOCH) в PostgreSQL — переводит интервал в секунды

✓ TIMESTAMPDIFF в MySQL — прямое вычисление разницы в минутах

✓ Обработка NULL — письма без ответа могут быть отфильтрованы или показаны как NULL

✓ Индексы — рекомендуется индекс на (to_email, timestamp) для оптимизации