Glassdoor (Airbnb) — определить Superhost по 3 критериям

Условие

Таблицы:

• listings(host_id, listing_id)
• reviews(listing_id, review_date, rating)
• bookings(listing_id, check_in, check_out)

Хост — Superhost, если за последние 12 месяцев одновременно:

1. ≥ 10 завершённых бронирований,
2. средний rating ≥ 4.8,
3. < 1% отменённых бронирований (если есть колонка cancelled, иначе игнорируем третий критерий).

Верните host_id и три метрики для всех хостов; флаг is_superhost.

Решение

WITH window_dates AS (
  SELECT CURRENT_DATE - INTERVAL '12 months' AS d_start
),
host_stats AS (
  SELECT
    l.host_id,
    COUNT(b.*) AS completed_bookings,
    AVG(r.rating) AS avg_rating
  FROM listings l
  LEFT JOIN bookings b
    ON b.listing_id = l.listing_id
   AND b.check_out >= (SELECT d_start FROM window_dates)
   AND b.check_out <= CURRENT_DATE
  LEFT JOIN reviews r
    ON r.listing_id = l.listing_id
   AND r.review_date >= (SELECT d_start FROM window_dates)
  GROUP BY l.host_id
)
SELECT
  host_id,
  completed_bookings,
  ROUND(avg_rating::NUMERIC, 2) AS avg_rating,
  CASE
    WHEN completed_bookings >= 10 AND avg_rating >= 4.8 THEN TRUE
    ELSE FALSE
  END AS is_superhost
FROM host_stats;

Деталь — двойной LEFT JOIN раздувает счётчики

LEFT JOIN listings × bookings × reviews без агрегации каждой стороны → декартово произведение. COUNT(b.*) может задвоить на reviews. Правильно — посчитать каждую метрику в отдельном CTE:

WITH booking_count AS (
  SELECT l.host_id, COUNT(*) AS completed_bookings
  FROM listings l JOIN bookings b ON ...
  GROUP BY l.host_id
),
rating_avg AS (
  SELECT l.host_id, AVG(r.rating) AS avg_rating
  FROM listings l JOIN reviews r ON ...
  GROUP BY l.host_id
)
SELECT bc.host_id, bc.completed_bookings, ra.avg_rating, ...
FROM booking_count bc
LEFT JOIN rating_avg ra ON ra.host_id = bc.host_id;

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

1. JOIN-задвоение. Это самый частый баг в multi-criteria segmentation. Метрики должны считаться отдельными CTE.
2. COUNT(b.*) vs COUNT(b.booking_id). Через LEFT JOIN с NULL b.* иногда даёт NULL — COUNT пропускает. Лучше явно COUNT(b.booking_id).
3. Хост без бронирований и без отзывов. Должен попасть в выход с 0 и NULL — для отчётов.
4. Граница 12 месяцев. >= CURRENT_DATE - INTERVAL '12 months' включает сегодняшний день минус год. Если хочется ровно с начала прошлого месяца — DATE_TRUNC.

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

Считать каждую метрику в отдельном CTE по host_id, потом LEFT JOIN всех CTE и CASE для is_superhost. Никогда не считать всё в одном COUNT/AVG с двойным JOIN.