Напиши SQL-запрос для вычисления среднего времени между первым и вторым заказом пользователя для оценки удержания (retention).

Для вычисления среднего времени между первым и вторым заказом пользователя можно использовать оконные функции SQL. Ниже приведён универсальный запрос, совместимый с PostgreSQL, BigQuery и большинством современных СУБД.

sql
WITH ranked_orders AS (
SELECT
user_id,
order_id,
created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at ASC) AS order_rank
FROM orders
),
first_and_second AS (
SELECT
r1.user_id,
r1.created_at AS first_order_date,
r2.created_at AS second_order_date,
EXTRACT(EPOCH FROM (r2.created_at — r1.created_at)) / 86400.0 AS days_between
FROM ranked_orders r1
JOIN ranked_orders r2
ON r1.user_id = r2.user_id
AND r1.order_rank = 1
AND r2.order_rank = 2
)
SELECT
ROUND(AVG(days_between), 2) AS avg_days_between_first_and_second_order
FROM first_and_second;

**Как работает запрос:**

1. **CTE `ranked_orders`** — нумерует все заказы каждого пользователя в хронологическом порядке с помощью `ROW_NUMBER()`. Первый заказ получает ранг 1, второй — ранг 2 и т.д.

2. **CTE `first_and_second`** — соединяет первый и второй заказ одного пользователя через `JOIN` по `user_id` и рангам. Вычисляет разницу в днях между двумя заказами. `EXTRACT(EPOCH FROM …)` возвращает разницу в секундах, деление на 86400 переводит в дни.

3. **Финальный SELECT** — вычисляет среднее значение по всем пользователям, у которых есть хотя бы два заказа.

**Адаптация для других СУБД:**
— **MySQL**: вместо `EXTRACT(EPOCH FROM …)` используйте `DATEDIFF(r2.created_at, r1.created_at)`.
— **BigQuery**: используйте `DATE_DIFF(DATE(r2.created_at), DATE(r1.created_at), DAY)`.
— **SQL Server**: используйте `DATEDIFF(day, r1.created_at, r2.created_at)`.

**Интерпретация результата:**
Если `avg_days_between_first_and_second_order` равно, например, 14 — в среднем пользователи возвращаются за вторым заказом через 2 недели. Чем меньше это значение, тем лучше удержание аудитории.

**Дополнительные метрики retention на основе этого запроса:**
— Можно добавить `COUNT(*)` в финальный SELECT, чтобы узнать, сколько пользователей вообще сделали второй заказ.
— Разделить на когорты по дате первого заказа (`DATE_TRUNC(‘month’, first_order_date)`) для трендового анализа.
— Сравнить медиану (`PERCENTILE_CONT(0.5)`) со средним, чтобы исключить влияние выбросов.

**Строка для CSV:**
`user_id,first_order_date,second_order_date,days_between`