SQL-запрос по описанию

На какой вопрос отвечает запрос

Доля поисков, у которых есть хотя бы одна бронь со статусом completed, по источникам поиска за предыдущий полный календарный месяц — и средний чек завершённых броней по этим же источникам.

Допущения

• «Последний полный месяц» — предыдущий календарный месяц целиком. Запрос привязан к дате запуска: при запуске в августе он считает июль. Если отчётный месяц фиксирован, период лучше задать датами явно.
• Поиск считается конвертированным, если у него есть хотя бы одна бронь completed. Несколько завершённых броней у одного поиска — это один конвертированный поиск.
• Средний чек считается только по брони со статусом completed; cancelled и pending не участвуют.
• Средний чек считается по всем завершённым броням периода без группировки: AVG(gross_booking_value) на уровне брони. Осреднение средних по поискам исказило бы метрику — поиск с одной бронью и поиск с пятью получили бы одинаковый вес.
• Поиск мог произойти в июне, а бронь по нему — в июле: брони привязаны к периоду по дате создания брони, поэтому отчёт за закрытый месяц может меняться задним числом. Если это недопустимо, нужен явный промежуток между поиском и бронью — во входных данных его нет, это допущение.
• В таблице bookings нет колонки валюты: если gross_booking_value приходит в разных валютах, средний чек по источнику будет некорректен. Запрос ниже считает, что значения сравнимы.

SQL

-- 1. Поиски за предыдущий полный календарный месяц
WITH month_searches AS (
    SELECT
        s.id,
        s.source
    FROM searches AS s
    WHERE s.created_at >= date_trunc('month', current_date) - interval '1 month'
      AND s.created_at <  date_trunc('month', current_date)
),

-- 2. Завершённые брони того же месяца: средний чек считается
--    на уровне брони, а не средним по поискам
month_bookings AS (
    SELECT
        b.search_id,
        b.gross_booking_value
    FROM bookings AS b
    WHERE b.status = 'completed'   -- отменённые и ожидающие не учитываем
      AND b.created_at >= date_trunc('month', current_date) - interval '1 month'
      AND b.created_at <  date_trunc('month', current_date)
)

-- 3. Конверсия поиска в бронь и средний чек брони по источникам
SELECT
    ms.source,
    COUNT(*)                                                AS searches,
    COUNT(DISTINCT mb.search_id)                            AS searches_with_completed,
    ROUND(100.0 * COUNT(DISTINCT mb.search_id)
        / NULLIF(COUNT(*), 0), 2)                           AS conversion_pct,
    ROUND(AVG(mb.gross_booking_value), 2)                   AS avg_completed_value
FROM month_searches AS ms
LEFT JOIN month_bookings AS mb
    ON mb.search_id = ms.id
GROUP BY ms.source
ORDER BY searches DESC;

Одним запросом ответить можно: LEFT JOIN оставляет поиски без завершённых броней, и они попадают в знаменатель доли.

Что проверить перед запуском на проде

• Период и таймзона: date_trunc привязан к текущей дате сервера; убедиться, что created_at хранится в ожидаемой таймзоне, иначе граница месяца сдвинется.
• Дубликаты броней: если одна бронь меняет статус и остаётся несколькими строками, completed_cnt завышен; проверить уникальность bookings.id.
• Значения статусов: расчёт опирается на перечень completed, cancelled, pending из схемы; если в данных есть статусы вне перечня (например возврат), уточнить, чем их считать.
• Объём данных: сначала оценить количество поисков за месяц; на больших объёмах добавить индекс по created_at или считать по партициям.
• Права доступа: bookings содержит платёжные суммы — доступ на чтение может быть ограничен.
• Валюта: сверить, в одной ли валюте gross_booking_value (см. последнее допущение).

Проверьте факты

• Состав статусов bookings (completed, cancelled, pending) взят из схемы: подтвердить, что в данных нет других значений.
• Привязка «последний полный месяц» к дате запуска — допущение: у владельца вопроса уточнить фиксированный период.
• Колонки bookings.currency в схеме нет — сравнимость сумм по валюте не проверена.
• Правило «один поиск — один конвертированный поиск» при нескольких бронях — допущение: сверить с аналитиком.