Введение

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

Да, продуктовые метрики мы тоже будем сегодня считать :)
Да, продуктовые метрики мы тоже будем сегодня считать 🙂

Материал создан командой Симулятора «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:

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

Решение

Если что, MAUmonthly 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 для анализа данных» — поможет глубоко изучить SQL на бизнесовых кейсах. Вас ждет работа с реальными данными с интересной сюжетной линией.

  • Хотите оценить свои навыки? Попробуйте решить задачи с собеседования в Альфа-Банк.

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

Источник: https://habr.com/ru/post/679406/?utm_campaign=679406&utm_source=habrahabr&utm_medium=rss

close

Рассказываем о маркетинге
в онлайн и офлайн

Мы не спамим! Прочтите нашу политику конфиденциальности, чтобы узнать больше.


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

Добавить комментарий

Avatar placeholder

Ваш адрес email не будет опубликован.