Клиенты с тратами выше среднего

Задача

Отдел лояльности ищет ядро аудитории: «Дай клиентов, которые потратили больше, чем в среднем тратит клиент. Не выше среднего чека, а выше средних суммарных трат на человека — это наши самые ценные».

Верни имя, фамилию и суммарные траты клиентов, у которых эта сумма выше средней по всем клиентам.

Схема данных

Учебная база проката dvdrental:

• customer(customer_id, first_name, last_name, ...) — клиенты.
• payment(payment_id, customer_id, amount, ...) — платежи; у клиента их много.

Решение

WITH spend AS (
  SELECT c.customer_id, c.first_name, c.last_name,
         sum(p.amount) AS total_spent
  FROM payment p
  JOIN customer c USING (customer_id)
  GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT first_name, last_name, total_spent
FROM spend
WHERE total_spent > (SELECT avg(total_spent) FROM spend)
ORDER BY total_spent DESC, customer_id;

Разбор

Здесь легко попасть в ловушку формулировки. «Выше среднего» — но среднего чего? Среднего чека (одного платежа) или средних трат на человека? Задача про второе: сначала сворачиваем каждого клиента в его итог, а потом сравниваем эти итоги между собой. Это два разных «средних», и путаница между ними — классическая ошибка на собеседовании.

Задача двухэтажная, и WITH (CTE) делает её читаемой. CTE spend — это как временная именованная таблица, живущая только внутри запроса: она считает суммарные траты каждого клиента через обычный GROUP BY. Дальше основной запрос работает уже с этой таблицей итогов, будто она настоящая. Когда брать CTE, а когда обычный подзапрос или временную таблицу, — в вопросе подзапрос, CTE или временная таблица.

Самое приятное — spend можно переиспользовать дважды. Один раз мы из неё выбираем строки, второй раз — считаем по ней среднее в подзапросе (SELECT avg(total_spent) FROM spend). Без CTE пришлось бы дублировать всю агрегацию из платежей в двух местах: и в основном запросе, и в подзапросе для среднего. CTE убирает этот копипаст — тот же расчёт описан один раз.

Логика фильтра: подзапрос отдаёт одно число — средний итог по всем клиентам, — а WHERE total_spent > <это число> оставляет тех, кто выше. Здесь важно, что сравнение идёт уже после агрегации, поэтому оно и вынесено на верхний уровень, а не в HAVING: мы фильтруем строки готовой витрины spend, а не группы во время их сборки.

ORDER BY total_spent DESC, customer_id даёт стабильный порядок — сначала самые щедрые, tie-break по customer_id.

Если нужно не просто отобрать, а проставить клиентам места, — смотри задачу ранжирование клиентов по тратам.

Ожидаемый результат

first_name | last_name | total_spent
--- | --- | ---
Eleanor | Hunt | 211.55
Karl | Seal | 208.58
Marion | Snyder | 194.61
Rhonda | Kennedy | 191.62
Clara | Shaw | 189.60
Tommy | Collazo | 183.63
Ana | Bradley | 167.67
Curtis | Irby | 167.62
Marcia | Dean | 166.61
Mike | Way | 162.67
… всего строк: 285 (показаны первые 10)