Условие

У каждого пользователя есть набор транзакций с временем. Используя таблицу транзакций (user_id, transaction_time, item_name, ...), напишите наиболее оптимальные SQL-запросы:

Для каждого пользователя — первое наименование товара, которое он заказал (первое по времени транзакции).
Сколько транзакций в среднем делает каждый пользователь в течение 72 часов с момента первой транзакции.

Решение

Подход

Q1 — классика «first per group»: окно ROW_NUMBER() или FIRST_VALUE().

Q2 — две идеи:

Сначала вычислить first_tx_time для каждого пользователя.
Затем посчитать число транзакций в окне [first_tx_time; first_tx_time + 72h).
Усреднить по пользователям.

Реализация

-- Q1: первое наименование товара пользователя
-- Способ A: ROW_NUMBER (универсально)
WITH ranked AS (
    SELECT
        user_id,
        item_name,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY transaction_time
        ) AS rn
    FROM transactions
)
SELECT user_id, item_name AS first_item
FROM ranked
WHERE rn = 1;
 
-- Способ B: FIRST_VALUE (читаемее)
SELECT DISTINCT
    user_id,
    FIRST_VALUE(item_name) OVER (
        PARTITION BY user_id
        ORDER BY transaction_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_item
FROM transactions;

-- Q2: среднее число транзакций за 72 часа от первой
WITH first_tx AS (
    SELECT
        user_id,
        MIN(transaction_time) AS first_ts
    FROM transactions
    GROUP BY user_id
),
tx_in_72h AS (
    SELECT
        f.user_id,
        COUNT(*) AS tx_count
    FROM first_tx f
    JOIN transactions t
      ON t.user_id = f.user_id
     AND t.transaction_time >= f.first_ts
     AND t.transaction_time <  f.first_ts + INTERVAL '72 hours'
    GROUP BY f.user_id
)
SELECT AVG(tx_count) AS avg_tx_in_72h
FROM tx_in_72h;

Анализ результата

В Q1 ROW_NUMBER() гарантирует одну строку на пользователя. Если две транзакции в одну миллисекунду — выбор детерминирован только при дополнительном тай-брейке (ORDER BY transaction_time, transaction_id).
В Q2 знаменатель — все пользователи с первой транзакцией. Так как первая транзакция всегда попадает в 72-часовое окно, у каждого пользователя tx_count ≥ 1. Среднее > 1.
Если хочется среднее число дополнительных транзакций (после первой), выводите AVG(tx_count - 1).

Альтернатива через окно (без CTE)

SELECT AVG(cnt)
FROM (
    SELECT user_id,
           COUNT(*) FILTER (
               WHERE transaction_time <
                     MIN(transaction_time) OVER (PARTITION BY user_id)
                     + INTERVAL '72 hours'
           ) AS cnt
    FROM transactions
    GROUP BY user_id, transaction_time
) t;

(Постгрес — синтаксис разнится между диалектами. CTE-вариант надёжнее.)

Оптимизация

Индекс по (user_id, transaction_time) — обязателен. Иначе сортировка в окнах будет дорогой.
На больших объёмах: партиционирование по дате транзакции, чтобы не сканировать всё.
Если транзакций в день миллионы — Q2 в виде «MIN-затем-JOIN» работает быстрее, чем «всё через окно».

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

Тай-брейки. Если у двух транзакций одинаковое время — ROW_NUMBER выбирает «случайную». Добавьте сортировку по transaction_id или другому стабильному полю.
Знаменатель в Q2. Усредняем по уникальным пользователям, а не по транзакциям. Группировка перед AVG обязательна.
Полу-открытое окно. [first; first+72h) исключает ровно 72-часовую отметку. На практике незначимо, но в строгих формулировках важно.
SELECT DISTINCT с window-функциями. Может быть медленнее, чем ROW_NUMBER + WHERE rn=1. На больших объёмах предпочитайте второй.
Часовой пояс. Если transaction_time хранится без TZ — убедитесь, что все клиенты пишут в одной TZ.
NULL в item_name. Если первое событие — это «service», не «purchase», и item_name=NULL — задача может требовать «первая покупка с item_name IS NOT NULL».

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

Q1: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_time) = 1.

Q2: вычисляем first_ts = MIN(transaction_time) per user → join transactions в окне 72ч → group by user, count → AVG. Знаменатель — уникальные пользователи. Индекс (user_id, transaction_time) обязателен для скорости.

ВК/ОК: первый заказ и среднее число транзакций за 72 часа