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

Введение

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

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

Описание базы данных

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

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

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

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

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

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

Задача 1

Условие

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

Вывести: новое поле, назовем его 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

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

Задача 2

Условие

Вывести %% дозвона для каждого дня. Период с 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). Опять же - мы просто перестраховались.

Задача 3

Условие

Дана таблица 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 от ребят из Simulative - там много крутых штук 👍

Еще больше интересных материалов - в нашем телеграм канале.

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