Разбор тестового задания в Тиньков [SQL]

Недавно нам прилетело большое тестовое задание от Тиньков-Банка на должность аналитика данных. Там очень много задач, но сегодня мы разберем несколько — остановимся на мелочах и обратим внимание на тонкие моменты.

Материал создан командой Симулятора «SQL для анализа данных».

Итак, нам дана такая структура таблиц:

В какой СУБД мы будем работать — не сказано. По косвенным признакам мы предполагаем, что это PostgreSQL.

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

Кстати, интересный вопрос: представьте, что вы можете попросить интервьюера дать вам пример любого запроса.

Какой запрос вы попросите написать, чтобы понять, с какой СУБД вы имеете дело?

Ответ на этот вопрос предлагаем обсудить в комментариях, а мы переходим к задачам! 🙃

Необходимо получить список сотрудников в формате: «Иванова — Наталья – Юрьевна». ФИО должно быть прописано в одном столбике, разделение —.

Вывести: новое поле, назовем его fio, birth_dt.

Эта задача достаточно простая — здесь даже нет необходимости джойнить другие таблицы, достаточно поработать с таблицей Employees.

Основная проблема — вывести ФИО через заданный разделитель. Многие решают эту задачу с помощью простой конкатенации:

select first_nm || '—' || middle_nm || '—' || last_nm as fio, birth_dt from employees

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

select concat_ws('—', first_nm, middle_nm, last_nm) as fio, birth_dt from employees

Выглядит посимпатичней. Заодно и перед интервьюером блеснули знаниями 😅

Вывести %% дозвона для каждого дня. Период с 01.10.2020 по текущий день.

%% дозвона – это доля принятых звонков (dozv_flg=1) от всех поступивших звонков (dozv_flg = 1 or dozv_flg = 0).

Вывести: date, sla (%% дозвона)

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

А на самом деле, все просто — достаточно просто знать, что условный оператор CASE можно использовать внутри агрегатных функций — например, COUNT.

Итак, чтобы посчитать SLA, нам нужно:

посчитать кол-во звонков с dozv_flg = 1
посчитать общее количество звонков
разделить одно на другое

Давайте сделаем это в одном запросе, без подзапросов и CTE.

select start_dttm::date as "date", count(case when dozv_flg=1 then 1 end) / count(case when dozv_flg in (1, 0) then 1 end) as sla from calls where start_dttm::date between '2020-10-01' and now()::date group by start_dttm::date

Вот, собственно, и все. Но проговорим несколько важных моментов:

Почему мы написали не count(*), а count(case when dozv_flg in (1, 0) then 1 end)?
Любопытный читатель

Мы просто перестраховались — вдруг там еще какие-то значения могут быть. Например, 2. Лишним не будет, в любом случае.

Зачем мы делаем преобразование с помощью ::date?
Другой любопытный читатель

А потому что оператор between потеряет все записи за сегодня, если не преобразовать эти поля в дату (это особенность сравнения даты-времени в PostgreSQL). Опять же — мы просто перестраховались.

Дана таблица clinets:

id клиента
calendar_at - дата входа в мобильное приложение

Нужно написать запрос для расчета MAU.

Если что, MAU - monthly active users: количество уникальных клиентов, проявляющих активность в приложении в течение месяца.

Многие по ошибке выводят MAU в виде таблицы со столбцами Месяц — Кол-во активных клиентов. Это неправильно - MAU всегда должно быть одним числом.

Соответственно, решение задачи сводится к следующим пунктам:

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

Для решения задачи мы будем использовать CTE и оператор DISTINCT внутри COUNT:

with a as ( select to_char(calendar_dt, 'MM') as mon, count(distinct id) as cnt from clients group by mon ) select avg(cnt) as mau from a

Сразу отметим - MAU можно считать и по-другому. Например:

сразу брать цифры на примере одного месяца
находить медиану
как-то еще

Мы просто показали один из вариантов 😇

На сегодня остановимся на этих 3 задачах. У нас в запасе еще много интересного — так что если вам зашло, мы скоро вернемся с новым разбором!

В целом, задачи не очень сложные. Но мы постарались «подсветить» некоторые тонкие моменты, без которых решение этих задач может стать проблемой.
Команда Симулятора по SQL

Хотите глубже разобраться в том, как писать крутые SQL-запросы, делать эдхоки и считать продуктовые метрики? Пройдите обучение в Симуляторе по SQL от ребят из Simulative - там много крутых штук 👍

Симулятор «SQL для анализа данных» - поможет глубоко изучить SQL на бизнесовых кейсах. Вас ждет работа с реальными данными с интересной сюжетной линией.
Хотите оценить свои навыки? Попробуйте решить задачи с собеседования в Альфа-Банк.
Еще больше интересных материалов - в нашем телеграм канале.

14 комментариев

Иван Петров

27.07.2022

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

Ответить

Anton Karmanov

В прошлом году искал работу, созванивался с HR из Тинька, в конце 40-минутного созвона узнал что все последующие этапы собеседования займут 4 (ЧЕТЫРЕ, КАРЛ!!!) часа на помидорскую позицию.

И это при том что «чтобы получать запрашиваемую вами зарплату надо идеально пройти все этапы», хотя я запрашивал в рамках рынка и не накидывал 100k сверх верхней границы.