Разбор тестового задания в Тиньков [SQL] Итак, нам дана база | IT Resume

Разбор тестового задания в Тиньков [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

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

1. Почему мы написали не COUNT(*), а COUNT(CASE WHEN dozv_flg IN (1, 0) THEN 1 END)? Мы просто перестраховались — вдруг там еще какие-то значения могут быть.

2. Зачем мы делаем преобразование с помощью ::date? А потому что оператор between потеряет все записи за сегодня, если не преобразовать эти поля в дату. Опять же мы просто перестраховались.


Задача 3.

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

- 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 можно считать и по-другому. Например, сразу брать цифры на примере одного месяца; находить медиану или как-то еще. Мы просто показали один из вариантов.

- - - - -

Пройдите Симулятор по SQL, чтобы прокачаться на бизнесовых задачах https://vk.cc/cfpuft