Посчитайте медиану чека по каждому городу

ЗадачаSeniorРедко

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

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

Посчитайте медиану упорядоченным агрегатом: percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) с GROUP BY city. При четном числе заказов он берет среднее двух центральных значений, percentile_disc(0.5) вернет меньшее из них, то есть реальный чек. Пустые суммы оба пропускают. avg медианой не считается: один крупный заказ сдвигает среднее, медиана почти не меняется.

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

Медиану спрашивают, чтобы проверить, отличаете ли вы среднее от типичного значения и знаете ли агрегаты PostgreSQL сверх sum и avg. От senior заодно ждут разговора о пустых значениях и цене сортировки.

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

Сеть цветочных магазинов хранит заказы в таблице orders(id, city, amount). Маркетинг хочет знать типичный чек в каждом городе: среднее портят редкие корпоративные заказы. Посчитайте медиану суммы заказа по каждому городу и отсортируйте результат по названию города. В Казани четыре заказа, у одного заказа в Самаре сумма не записана.

idcityamount
1Казань1200
2Казань1500
3Казань2100
4Казань6000
5Самара900
6Самара1300
7СамараNULL
8Самара4800
9Тула700
10Тула1100
11Тула1600

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

  1. Решите с заказчиком, что считать медианой при четном числе заказов: среднее двух центральных или реальный чек
  2. Возьмите percentile_cont(0.5) с WITHIN GROUP (ORDER BY amount) и сгруппируйте по городу
  3. Проверьте, как агрегат обходится с пустой суммой: строку с NULL он пропускает
  4. Выведите рядом percentile_disc и avg, чтобы показать разницу на данных

Решение

WITHIN GROUP (ORDER BY amount) задает порядок, в котором ищется середина, а GROUP BY делит заказы по городам. Строку без суммы агрегат пропускает, поэтому в Самаре медиана считается по трем заказам. percentile_cont возвращает double precision: для денег округлите результат или приведите его к numeric.

SQL
SELECT city,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY amount) AS median_disc,
       round(avg(amount), 2) AS avg_amount
FROM orders
GROUP BY city
ORDER BY city;
Вывод
  city  | median | median_disc | avg_amount
--------+--------+-------------+------------
 Казань |   1800 |     1500.00 |    2700.00
 Самара |   1300 |     1300.00 |    2333.33
 Тула   |   1100 |     1100.00 |    1133.33
(3 rows)

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

  • Как посчитать медиану без percentile_cont, например в базе, где его нет?

  • Можно ли вывести медиану города рядом с каждым заказом через OVER?

  • Как посчитать сразу медиану и 90-й процентиль?

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

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

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

  • «Медиана — это avg(amount)»

    Среднее и медиана совпадают, например, на симметричных данных, а чеки обычно скошены вправо. В Казани один заказ на 6000 поднимает среднее до 2700, медиана остается 1800

  • «percentile_disc и percentile_cont дают одно и то же»

    Только при нечетном числе значений. При четном percentile_cont берет среднее двух центральных, percentile_disc возвращает одно из них. Уточните, нужен ли реальный чек из данных

  • «Заменю пустую сумму нулем через coalesce, чтобы не терять строки»

    Ноль сдвинет середину: в Самаре медиана станет 1100 вместо 1300. Пустая сумма значит, что данных нет, и агрегат правильно ее пропускает

  • «Отсортирую заказы и возьму строку с номером count(*) / 2»

    При четном числе это одна из двух средних строк, при нечетном целочисленное деление дает строку левее середины. Встроенный percentile_cont избавляет от этих ошибок

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

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

Интересуется, для чего нужна цифра, и предлагает показывать рядом с медианой число заказов, чтобы маленький город не вводил в заблуждение

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

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

Красный флаг

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

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

  • Как найти медиану в PostgreSQL?
  • Чем percentile_cont отличается от percentile_disc?
  • Посчитайте медианную сумму заказа для каждой группы
  • Почему для типичного чека медиана лучше среднего и как получить ее в SQL?

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

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

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

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

  • GROUP BY
  • COUNT
242
ЗнаниеJuniorЧасто

Чем оконная функция отличается от GROUP BY?

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

  • оконные функции
  • GROUP BY
274
ЗадачаMiddleИногда

Найдите вторую по величине зарплату в каждом отделе, если у нескольких сотрудников она одинаковая

Пронумеруйте зарплаты внутри каждой клиники через dense_rank() OVER (PARTITION BY clinic ORDER BY salary DESC) и во внешнем запросе оставьте строки с рангом 2. DENSE_RANK дает равным зарплатам один ранг и не пропускает номера, поэтому ранг 2 — это вторая по величине сумма. Добавьте DISTINCT: вторую зарплату могут получать несколько врачей.

  • DENSE_RANK
  • оконные функции
156
ЗадачаJuniorИногда

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

Средний чек — выручка, деленная на число заказов. Когда каждая позиция лежит отдельной строкой, сначала соберите заказы: в CTE сложите price * qty по order_id, потом по месяцам возьмите AVG от сумм заказов. Короткая проверка — SUM(price * qty) / COUNT(DISTINCT order_id). AVG(price * qty) по строкам дает среднюю стоимость позиции, это другая метрика.

  • средний чек
  • CTE
172

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

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

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

Желательно: на задаче для senior его ждут без подсказок. Запомните схему «функция с долей, затем WITHIN GROUP с сортировкой». Если забыли, скажите, что ищете упорядоченный агрегат, и решите задачу нумерацией строк. Ход мысли интервьюер оценит выше точной памяти.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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