Посчитайте по каждому клиенту сумму заказов и сумму оплат так, чтобы строки не размножились после JOIN

ЗадачаMiddleИногда

Проверила команда СопроводОбновлено

Короткий ответ

Сверните каждую таблицу до одной строки на клиента и только потом соединяйте. Посчитайте SUM(amount) по client_id отдельно в orders и в payments, а результаты присоедините к clients через LEFT JOIN. Если соединить все три таблицы сразу, каждый заказ повторится столько раз, сколько у клиента оплат, и обе суммы вырастут.

Почему этот вопрос задают

Размножение строк после соединения — частая причина неверных цифр в отчетах. Запрос не падает и выдает правдоподобные суммы, поэтому важно знать, откуда берется задвоение.

Условие задачи

Оптовый поставщик стройматериалов ведет три таблицы: clients(id, name), orders(id, client_id, amount) и payments(id, client_id, amount). Бухгалтер просит сверку: по каждому клиенту сумма отгруженных заказов и сумма полученных оплат. Аналитик соединил три таблицы двумя JOIN и получил у «Каменного двора» сумму заказов 400 000 вместо 200 000. Напишите запрос с верными суммами, отсортируйте по id клиента.

КлиентЗаказыОплаты
1 Каменный двор120 000, 80 000150 000, 50 000
2 Стройлайн45 000, 30 000, 25 00060 000, 20 000
3 Кровля Плюснет40 000

Как подойти к решению

  1. Посчитайте, сколько строк даст соединение: заказы клиента умножатся на его оплаты
  2. Сверните orders до одной строки на клиента через GROUP BY client_id
  3. Повторите то же для payments и присоедините оба итога к clients через LEFT JOIN
  4. Замените NULL на ноль для клиентов без заказов или без оплат

Решение

Каждый подзапрос возвращает одну строку на клиента, поэтому соединение уже ничего не размножит. LEFT JOIN сохраняет «Кровлю Плюс»: у нее есть аванс 40 000 и нет заказов. По затратам это одно чтение каждой таблицы и две группировки.

SQL
SELECT c.id, c.name,
       COALESCE(o.total, 0) AS orders_total,
       COALESCE(p.total, 0) AS paid_total
FROM clients c
LEFT JOIN (
  SELECT client_id, SUM(amount) AS total FROM orders GROUP BY client_id
) o ON o.client_id = c.id
LEFT JOIN (
  SELECT client_id, SUM(amount) AS total FROM payments GROUP BY client_id
) p ON p.client_id = c.id
ORDER BY c.id;
Вывод
 id |     name      | orders_total | paid_total
----+---------------+--------------+------------
  1 | Каменный двор |    200000.00 |  200000.00
  2 | Стройлайн     |    100000.00 |   80000.00
  3 | Кровля Плюс   |            0 |   40000.00
(3 rows)

Что спросят следом

  • Можно ли исправить исходный запрос через SUM(DISTINCT amount)?

  • Как вывести только клиентов с долгом?

  • Есть ли разница, писать подзапросы в FROM или вынести их в WITH?

Доступно по подписке

Ответы на эти вопросы

Частые ошибки

  • «Соединю три таблицы и сгруппирую по клиенту, GROUP BY все соберет»

    Группировка складывает то, что уже размножилось на соединении. Скажите, что два заказа и две оплаты клиента дают четыре строки, и каждая сумма войдет в итог дважды

  • «Поставлю DISTINCT после SELECT, и дубли уйдут»

    DISTINCT убирает одинаковые строки результата, а суммы к этому моменту уже посчитаны с повторами. Чинить нужно до группировки: свернуть каждую таблицу отдельно

  • «Возьму INNER JOIN, клиенты без заказов в сверке не нужны»

    Уточните у бухгалтера: клиент с авансом без заказов для сверки как раз важен. С INNER JOIN «Кровля Плюс» с оплатой 40 000 пропадет из отчета

  • «Поделю сумму заказов на число оплат, и все сойдется»

    Такая поправка ломается, как только у клиента нет оплат или меняется число строк. Лечите причину: сверните каждую таблицу до одной строки на клиента до соединения

Сигналы для интервьюера

Сильный ответ

До запроса называет связь «один ко многим» у обеих таблиц и заранее говорит, во сколько раз вырастут суммы

Тревожный сигнал

Чинит запрос по шаблону, но не может посчитать, сколько строк дало двойное соединение у «Каменного двора»

Красный флаг

Не видит проблемы в суммах вдвое больше реальных и предлагает сверять их с бухгалтерией уже после выгрузки

Как еще могут спросить

  • Почему после JOIN двух таблиц сумма в отчете выросла вдвое?
  • Как посчитать суммы из двух дочерних таблиц, чтобы соединение не задвоило значения?
  • Что такое размножение строк при соединении и как его избежать?
  • Выведите для каждого клиента итог по заказам и по платежам одним запросом

Похожие вопросы

ЗадачаMiddleЧасто

Посчитайте по каждому городу число клиентов, число клиентов с телефоном и их долю в процентах

Сгруппируйте клиентов по city. COUNT(*) даст всех клиентов города, COUNT(phone) — только тех, у кого телефон заполнен: агрегат пропускает NULL. Долю считайте как ROUND(100.0 * COUNT(phone) / COUNT(*), 1). Множитель 100.0 переводит расчет в numeric, без него 3 / 4 в PostgreSQL дает 0: деление целых отбрасывает дробную часть.

  • GROUP BY
  • COUNT
242
ЗадачаSeniorИногда

Посчитайте retention первой недели по когортам недели регистрации

Определите когорту как неделю регистрации: date_trunc('week', registered_at). Присоедините к пользователям сессии через LEFT JOIN с условием, что визит пришелся на 7–13 день после регистрации. Вернувшихся считайте через COUNT(DISTINCT s.user_id), размер когорты — через COUNT(DISTINCT c.id). Делите на всю когорту, включая тех, кто ни разу не зашел.

  • retention
  • когорты
166
ЗнаниеJuniorЧасто

Какие виды JOIN бывают и чем LEFT JOIN отличается от INNER JOIN?

Основные виды: INNER, LEFT, RIGHT, FULL и CROSS JOIN. INNER JOIN оставляет только строки, у которых нашлась пара по условию ON. LEFT JOIN сохраняет все строки левой таблицы, а там, где пары нет, заполняет столбцы правой значением NULL. Поэтому LEFT JOIN берут, когда важны и записи без совпадений, например читатели без выдач.

  • JOIN
261
ЗадачаMiddleЧасто

Посчитайте ARPU и ARPPU за месяц по таблицам активных пользователей и платежей

ARPU делит выручку месяца на всех активных пользователей, ARPPU — только на платящих. Сверните платежи октября до пользователя, присоедините их к активным через LEFT JOIN и посчитайте обе метрики одной строкой. Неплатящие остаются в знаменателе ARPU, ARPPU делит на число платящих. AVG(amount) по платежам дает средний платеж, это третья метрика.

  • ARPU
  • ARPPU
  • монетизация
219

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Возьмите одного клиента и посчитайте вслух: два заказа, две оплаты, после соединения четыре строки. Каждый заказ встретился дважды, поэтому сумма заказов удвоилась. Нарисуйте эти четыре строки на доске.

В отчетах, где к заказам присоединяют позиции, оплаты, возвраты или промокоды: выручка вдруг удваивается после новой таблицы. Проверяйте число строк после каждого нового соединения, особенно если ключ справа может повторяться.

Можно, но выйдет длиннее: окна не уменьшают число строк, и повторы после соединения все равно придется убирать. Подзапросы с GROUP BY короче, поэтому покажите их, а окна назовите как вариант.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

Расскажите, какие задачи хотите решать через несколько лет и чему для этого научитесь. Свяжите план с тем, что дает эта компания, и покажите первый шаг, который уже сделали. Точную должность называть не обязательно. Важнее, чтобы план был реальным и вел через эту работу, а не мимо нее.

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

Назовите одно достижение, близкое к задачам новой работы, и расскажите его как историю: какая была проблема, что сделали лично вы и что изменилось в цифрах. Сильнее всего звучит результат, который держится и после вас: новый порядок работы, правило, схема. Если результат общий, прямо скажите, какая часть в нем ваша.

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

Назовите свое правило: что вы отдаете команде и что оставляете себе, и покажите его на одной задаче. Расскажите, кому и почему передали, какие полномочия и рамки дали, как проверяли без мелкого контроля. Закончите честным итогом: что получилось хуже или медленнее, чем у вас, и что вы выиграли.

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

Поговорите сначала с каждым по отдельности: что случилось, с чего началось и что мешает работать. Потом соберите обоих вместе и обсуждайте работу: кто за какие данные и шаги отвечает. За такой ссорой часто стоит размытая ответственность. Запишите договоренности, назовите новый срок для сорванного отчета и через пару недель проверьте, что все работает.

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

Возьмите одного человека и покажите путь: каким он пришел, какие задачи вы ему давали по нарастающей и чем помогали, кем он стал и за какой срок. Назовите цифру роста: новая должность, своя зона, люди в подчинении. Скажите честно, где он вырос сам, а где сработали ваши решения.

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

Назовите цель с цифрой и честный итог. Главное в ответе — момент, когда вы поняли, что цель срывается, и по какой цифре это увидели. Дальше расскажите, кому и когда сказали, что пересобрали, чтобы спасти главное, и какой один вывод сделали для следующего плана.

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

Назовите два-три показателя, которые отражают то, ради чего существует ваша команда, и их текущие цифры. Добавьте показатель качества, который не дает гнаться за одним числом. Скажите, как часто смотрите на цифры, кто их видит и какое решение вы приняли по ним в последний раз.

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227