StrataScratch (Airbnb) — Host Popularity Rental Prices: бины по числу отзывов

Условие

Таблица airbnb_host_searches(price, room_type, host_since, zipcode, number_of_reviews). Сначала сводим к уникальным хостам (предполагаем, что (price, room_type, host_since, zipcode, number_of_reviews) идентифицирует листинг).

Разбейте хостов на бины по number_of_reviews:

• New: 0
• Rising: 1–5
• Trending Up: 6–15
• Popular: 16–40
• Hot: > 40

Для каждого бина верните min_price, avg_price, max_price.

Решение

WITH dedup AS (
  SELECT DISTINCT price, room_type, host_since, zipcode, number_of_reviews
  FROM airbnb_host_searches
),
binned AS (
  SELECT
    price,
    CASE
      WHEN number_of_reviews = 0 THEN 'New'
      WHEN number_of_reviews BETWEEN 1 AND 5 THEN 'Rising'
      WHEN number_of_reviews BETWEEN 6 AND 15 THEN 'Trending Up'
      WHEN number_of_reviews BETWEEN 16 AND 40 THEN 'Popular'
      WHEN number_of_reviews > 40 THEN 'Hot'
    END AS host_pop_rating
  FROM dedup
)
SELECT
  host_pop_rating,
  MIN(price) AS min_price,
  ROUND(AVG(price)::NUMERIC, 2) AS avg_price,
  MAX(price) AS max_price
FROM binned
GROUP BY host_pop_rating
ORDER BY MIN(number_of_reviews) -- если хочется по возрастанию популярности
;

Порядок бинов

ORDER BY host_pop_rating отсортирует лексикографически: Hot, New, Popular, Rising, Trending. Если важен бизнес-порядок — сортируйте по «нижней границе бина» (например, через дополнительный CASE с числом 0/1/6/16/41).

Без DISTINCT

Если в таблице на одного хоста уже одна строка — DISTINCT лишний. Но в оригинале на StrataScratch один хост может фигурировать многократно (с одинаковым набором атрибутов) — DISTINCT нужен.

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

1. Перекрывающиеся бины. Если написать WHEN ... <= 5 ... WHEN ... <= 15 ... — границы вроде 5 и 15 уйдут в первый WHEN. Проверьте, что границы не двойные.
2. NULL в number_of_reviews. CASE без ELSE даст NULL — попадёт в GROUP BY как отдельная группа. ELSE 'Unknown' или WHERE number_of_reviews IS NOT NULL.
3. AVG(int) в MySQL. Без ::NUMERIC округление может уйти не туда. Явное приведение и ROUND(..., 2).
4. Дубли хостов. Без DISTINCT MIN/MAX устойчивы, но AVG задвоится.

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

DISTINCT по идентификатору хоста → CASE с бинами → GROUP BY бин → MIN/AVG/MAX(price). Сортировать по «номеру» бина, не по строке.