2022-07-28 19:25:54
Разбор тестового задания в Тиньков [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
2.4K views16:25
I
