Полный набор задач для пробного собеседования
Задачи на время от начала до конца, объединяющие соединения, оконные функции и CTE в условиях собеседования
«Полный набор задач для пробного собеседования» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.
Как проходит раунд собеседования по языку структурированных запросов
В этом итоговом разделе Вы решите полные пробные задачи, объединяющие соединения, оконные функции и CTE в условиях собеседования. Сначала разберём важный навык: как вести себя во время собеседования.
- Переформулируйте задачу и подтвердите схему.
- Уточните особые случаи (NULL, одинаковые значения, дубликаты) до написания запроса.
- Озвучьте свой подход, а затем напишите запрос.
- Проверьте решение мысленно на небольшом примере.
На собеседовании оценивают не только итоговый запрос, но и Ваш процесс.
Общая схема
Во всех следующих задачах используется небольшая схема интернет-магазина. Прочитайте её один раз, чтобы каждый запрос был понятен.
customers(id, name, country)orders(id, customer_id, order_date, status, amount)order_items(order_id, product_id, quantity)products(id, name, category, price)
Учитывайте эту схему: в остальной части урока используются ссылки на эти таблицы.
-- orders.status is one of: 'paid','pending','cancelled'
-- amount is the order total in the customer's currencyЗадача 1: Клиенты с наибольшими расходами
«Верните 3 клиентов с наибольшей общей суммой оплат, их имена и итоговые суммы».
Подход: отфильтруйте оплаченные заказы, выполните агрегацию по клиенту, отсортируйте результат и ограничьте его. Укажите, что исключаете отменённые и ожидающие заказы — это особый случай, который интервьюеры намеренно добавляют в условие.
SELECT c.name,
SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY total_spend DESC
LIMIT 3;Задача 2: Клиенты, которые никогда не делали заказов
«Перечислите клиентов, которые никогда не размещали заказ». Это шаблон антисоединения. Есть два понятных решения: LEFT JOIN с IS NULL или NOT EXISTS.
Предпочтительнее NOT EXISTS, поскольку он безопасен при NULL (в отличие от NOT IN). Упомяните это различие: именно его интервьюер и хочет проверить.
-- NULL-safe anti-join
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);Задача 3: Вторая по величине сумма заказа
«Найдите вторую по величине отличающуюся сумму заказа». Самое простое решение, не зависящее от одинаковых значений, использует DENSE_RANK, поэтому одинаковые суммы получают один ранг.
Особый случай, который стоит отметить: если второго отдельного значения нет, результат не содержит строк. Это может быть приемлемо или потребовать обёртки COALESCE — в зависимости от требований.
SELECT amount
FROM (
SELECT amount,
DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk
FROM orders
) ranked
WHERE rnk = 2;Задача 4: Последний заказ каждого клиента
«Верните самый свежий заказ каждого клиента». Это шаблон выбора последней строки для каждого ключа, который решается с помощью ROW_NUMBER, разделения по клиенту и сортировки по убыванию даты.
Добавьте дополнительный критерий (идентификатор заказа), чтобы результат был однозначным, когда у двух заказов одна дата. Это деталь, которую включают в сильные решения.
SELECT customer_id, id AS order_id, order_date, amount
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, id DESC
) AS rn
FROM orders o
) t
WHERE rn = 1;Задача 5: Изменение по сравнению с предыдущим месяцем
«Вычислите месячную оплачиваемую выручку и её процентное изменение относительно предыдущего месяца». Здесь агрегация в CTE объединяется с LAG.
На первом шаге данные агрегируются по месяцам, на втором каждый месяц сравнивается с предыдущим с помощью LAG. Предусмотрите проверку перед делением, чтобы для первого месяца, у которого нет предыдущего, не возникла ошибка.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS mth,
SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT mth,
revenue,
LAG(revenue) OVER (ORDER BY mth) AS prev_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY mth))
/ NULLIF(LAG(revenue) OVER (ORDER BY mth), 0), 2
) AS pct_change
FROM monthly
ORDER BY mth;Задача 6: Лучший товар в каждой категории
«Для каждой категории верните самый продаваемый товар по общему количеству». Это шаблон поиска N лучших в каждой группе: выполните агрегацию, ранжируйте внутри группы и отфильтруйте строки с рангом 1.
Если одинаковые результаты важны, замените ROW_NUMBER на RANK, чтобы вывести всех лидеров. Упоминание этого выбора показывает, что Вы понимаете разницу.
WITH sales AS (
SELECT p.category,
p.name AS product,
SUM(oi.quantity) AS qty
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category, p.name
)
SELECT category, product, qty
FROM (
SELECT s.*,
ROW_NUMBER() OVER (
PARTITION BY category ORDER BY qty DESC
) AS rn
FROM sales s
) r
WHERE rn = 1;Задача 7: Накопительный итог выручки
«Покажите накопительный итог оплаченной выручки по дням». Оконная функция SUM с упорядоченной рамкой вычисляет накопительный итог без самосоединения.
Упомяните рамку ROWS для настоящего построчного накопления: рамка RANGE по умолчанию может неожиданно вести себя при одинаковых датах.
SELECT order_date,
SUM(daily) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM (
SELECT order_date, SUM(amount) AS daily
FROM orders
WHERE status = 'paid'
GROUP BY order_date
) d
ORDER BY order_date;Задача 8: Последовательные активные дни
«Найдите пользователей, у которых есть как минимум 3 последовательных дня с оплаченным заказом». Это вариант задачи о разрывах и последовательных группах, использующий трюк с разностью номеров строк.
Вычитание номера строки для каждого пользователя из даты даёт постоянное значение внутри последовательного отрезка, поэтому данные можно сгруппировать по этой константе и подсчитать строки. Это показатель уровня старшего специалиста.
WITH days AS (
SELECT DISTINCT customer_id, order_date
FROM orders WHERE status = 'paid'
),
grp AS (
SELECT customer_id, order_date,
order_date - (ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_date
) * INTERVAL '1 day') AS island
FROM days
)
SELECT customer_id, COUNT(*) AS streak_len
FROM grp
GROUP BY customer_id, island
HAVING COUNT(*) >= 3;Производительность и распространённые ошибки
После правильного запроса интервьюеры спросят: «Как бы Вы его ускорили?» — и будут искать классические ошибки. Держите этот список под рукой:
- Создавайте индексы для столбцов, используемых при соединении и фильтрации (например,
orders(customer_id, status)); избегайте функций над индексированными столбцами в WHERE. - Для больших антисоединений предпочитайте EXISTS конструкции IN;
NOT INс NULL молча возвращает пустой результат. - Фильтрация столбца внешнего соединения в WHERE незаметно превращает его во внутреннее соединение.
- Всегда добавляйте дополнительный критерий, чтобы результаты поиска N лучших были однозначными.
- Проверяйте план EXPLAIN на наличие последовательного сканирования больших таблиц.
Быстрая проверка
Вам нужно получить единственный самый свежий заказ каждого клиента, причём у двух заказов может быть одна дата.
Повторение: полный набор пробных задач для собеседования
Вы решили от начала до конца наиболее часто встречающиеся задачи на собеседованиях:
- Агрегация + LIMIT для получения N самых больших расходов.
- Антисоединения с NOT EXISTS (безопасные при NULL).
- DENSE_RANK для N-го значения по величине, ROW_NUMBER для выбора последней строки по ключу и лучшей строки в группе.
- LAG для сравнения месяцев, SUM OVER для накопительных итогов.
- Трюк с номерами строк для задач о разрывах и последовательных группах.
- Завершайте каждый ответ обсуждением индексов, EXPLAIN и распространённых ошибок.
Часто задаваемые вопросы
Урок «Полный набор задач для пробного собеседования» бесплатный?
Да — полный текст урока «Полный набор задач для пробного собеседования» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.
Чему я научусь в уроке «Полный набор задач для пробного собеседования»?
Задачи на время от начала до конца, объединяющие соединения, оконные функции и CTE в условиях собеседования Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать Coding Interview Prep?
Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.
Сколько времени занимает урок «Полный набор задач для пробного собеседования»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке Coding Interview Prep?
Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Нормализация до третьей нормальной формы
- ER-моделирование и кардинальность связей
- Звёздная схема и проектирование хранилищ данных
- Полный набор задач для пробного собеседования