Недавно нам прилетело большое тестовое задание от Тиньков-Банка на должность аналитика данных. Там очень много задач, но сегодня мы разберем несколько — остановимся на мелочах и обратим внимание на тонкие моменты.
Материал создан командой Симулятора «SQL для анализа данных».
Описание базы данных
Итак, нам дана такая структура таблиц:
В какой СУБД мы будем работать — не сказано. По косвенным признакам мы предполагаем, что это PostgreSQL.
Хотя, по сути, это не особо важно — отличаться будут только некоторые функции. Все базовые операторы будут одинаковыми.
Кстати, интересный вопрос: представьте, что вы можете попросить интервьюера дать вам пример любого запроса.
Какой запрос вы попросите написать, чтобы понять, с какой СУБД вы имеете дело?
Ответ на этот вопрос предлагаем обсудить в комментариях, а мы переходим к задачам! 🙃
Задача 1
Условие
Необходимо получить список сотрудников в формате: «Иванова — Наталья – Юрьевна». ФИО должно быть прописано в одном столбике, разделение —.
Вывести: новое поле, назовем его fio, birth_dt.
Решение
Эта задача достаточно простая — здесь даже нет необходимости джойнить другие таблицы, достаточно поработать с таблицей Employees.
Основная проблема — вывести ФИО через заданный разделитель. Многие решают эту задачу с помощью простой конкатенации:
Но мы работаем в PostgreSQL, поэтому воспользуемся плюшкой — функцией CONCAT_WS. Она тоже делает конкатенацию строк, но первым аргументом принимает разделитель:
Выглядит посимпатичней. Заодно и перед интервьюером блеснули знаниями 😅
Задача 2
Условие
Вывести %% дозвона для каждого дня. Период с 01.10.2020 по текущий день.
%% дозвона – это доля принятых звонков (dozv_flg=1) от всех поступивших звонков (dozv_flg = 1 or dozv_flg = 0).
Вывести: date, sla (%% дозвона)
Решение
Здесь задача уже поинтересней — мы все еще работаем с одной таблицей, но многие соискатели на таких задачах начинают городить многоэтажные подзапросы.
А на самом деле, все просто — достаточно просто знать, что условный оператор CASE можно использовать внутри агрегатных функций — например, COUNT.
Итак, чтобы посчитать SLA, нам нужно:
- посчитать кол-во звонков с dozv_flg = 1
- посчитать общее количество звонков
- разделить одно на другое
Давайте сделаем это в одном запросе, без подзапросов и CTE.
Вот, собственно, и все. Но проговорим несколько важных моментов:
Почему мы написали не count(*), а count(case when dozv_flg in (1, 0) then 1 end)?
Мы просто перестраховались — вдруг там еще какие-то значения могут быть. Например, 2. Лишним не будет, в любом случае.
Зачем мы делаем преобразование с помощью ::date?
А потому что оператор between потеряет все записи за сегодня, если не преобразовать эти поля в дату (это особенность сравнения даты-времени в PostgreSQL). Опять же — мы просто перестраховались.
Задача 3
Условие
Дана таблица clinets:
- id клиента
- calendar_at - дата входа в мобильное приложение
Нужно написать запрос для расчета MAU.
Решение
Если что, MAU - monthly active users: количество уникальных клиентов, проявляющих активность в приложении в течение месяца.
Многие по ошибке выводят MAU в виде таблицы со столбцами Месяц — Кол-во активных клиентов. Это неправильно - MAU всегда должно быть одним числом.
Соответственно, решение задачи сводится к следующим пунктам:
- посчитать количество уникальных клиентов за каждый месяц
- усреднить данные по всем месяцам
Для решения задачи мы будем использовать CTE и оператор DISTINCT внутри COUNT:
Сразу отметим - MAU можно считать и по-другому. Например:
- сразу брать цифры на примере одного месяца
- находить медиану
- как-то еще
Мы просто показали один из вариантов 😇
Эпилог
На сегодня остановимся на этих 3 задачах. У нас в запасе еще много интересного — так что если вам зашло, мы скоро вернемся с новым разбором!
В целом, задачи не очень сложные. Но мы постарались «подсветить» некоторые тонкие моменты, без которых решение этих задач может стать проблемой.
Хотите глубже разобраться в том, как писать крутые SQL-запросы, делать эдхоки и считать продуктовые метрики? Пройдите обучение в Симуляторе по SQL от ребят из Simulative - там много крутых штук 👍
- Симулятор «SQL для анализа данных» - поможет глубоко изучить SQL на бизнесовых кейсах. Вас ждет работа с реальными данными с интересной сюжетной линией.
- Хотите оценить свои навыки? Попробуйте решить задачи с собеседования в Альфа-Банк.
- Еще больше интересных материалов - в нашем телеграм канале.
Айтишники, если вам предлагают большое тестовое задание - шлите лесом. Не тратьте свои силы и время.
Вообще, не удивлён что именно у тинькофа при приеме есть тестовые задания.
В прошлом году искал работу, созванивался с HR из Тинька, в конце 40-минутного созвона узнал что все последующие этапы собеседования займут 4 (ЧЕТЫРЕ, КАРЛ!!!) часа на помидорскую позицию.
И это при том что «чтобы получать запрашиваемую вами зарплату надо идеально пройти все этапы», хотя я запрашивал в рамках рынка и не накидывал 100k сверх верхней границы.
Жестко)) Тщательный отбор))
А надо было накидывать: вас же ментально насилуют! Сколько там этапов было?4?
Ну это смотря какая должность - все-таки на джуна это плюс-минус приемлемо. Представляете, какой там поток людей? :)
Ну и к тому же - во многих других областях тестовые тоже дают, так что в теории это норм)
а это какого уровня позиция с такими тестовыми?
а то на вид - совсем изян
Ну там есть посложней задания, но это на джуна.
Что-то на айтишном, не смог прочитать.
Вирус такой, IT 2019
Вирус? Что-то для винды.
Для наглядности, каждый case when имеет смысл завершать явным else. Да и count заменить на sum.
SUM да, хорошее предложение. А про else - что вы имеете ввиду?
То значение, которое присваивается "иначе" - которое по умолчанию нулл. Ну и при использовании count и when, значение then может быть любым, хоть 'yes' - главное, что не null :)
Ну так мы тут как раз вообще `else` не делали для этого, чтобы в `count` ничего лишнего не залетело.