Введение
Недавно нам прилетело большое тестовое задание от Тиньков-Банка на должность аналитика данных. Там очень много задач, но сегодня мы разберем несколько — остановимся на мелочах и обратим внимание на тонкие моменты.
Материал создан командой Симулятора «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 для анализа данных».
Комментарии (57)
mentin
27.07.2022 20:07Если уж страховаться от левых значений в dozv_flg, то стоит застраховаться от отсутствия там значений вообще, и избежать деления на ноль
Xander_d
27.07.2022 21:05Вот да. И по хорошему, сначала посмотреть бы, а какие там данные вообще могут быть. А то вдруг там и 2, и 3...
И окажется, что мы неправильно понимаем значение этого флага и это, скажем, количество, а не флаг, и надо будет просто ">0" в кейс записывать.
starik-2005
27.07.2022 20:17Даже интересно стало, сколько они такому джуну предлагают бабла...
ITResume Автор
28.07.2022 10:17Ну в целом ЗП там неплохие, насколько я знаю. Но там тестовое заданий на 50, так что это не все))
wadeg
27.07.2022 20:21+7А потому что оператор
betweenпотеряет все записи за сегодня, если не преобразовать эти поля в дату (это особенность сравнения даты-времени в PostgreSQL). Опять же - мы просто перестраховались.Спасибо, мы вам перезвоним. Вы потеряли индекс по дате, условие стало non-sargable.
вдруг там еще какие-то значения могут быть
Даже комментировать не буду.
FanatPHP
27.07.2022 22:05Я правильно понимаю, что к дате надо было приводить сами граничные значения?
ITResume Автор
28.07.2022 10:18-2Мы ждали подобных комментариев) В них рождается истина))
А про "комментировать не буду" - если можно, все-таки прокомментируйте)wadeg
28.07.2022 20:35+2Ничего там зародиться не может, это просто детская безграмотность. Вы взялись за тему, в которой не понимаете абсолютно ничего, даже как работает индексный поиск.
Комментировать различие между БД и помойкой ("вдруг там еще какие-то значения могут быть") тем более не планировал.
ITResume Автор
29.07.2022 12:00-1Вы очень счастливый человек, если работаете только в тех компаниях, где БД не помойка, а круто спроектированная штука. Жаль, что в 99% случаев это не так.
Хотя, тогда странно, что вы такой не дружелюбный, раз на работе все так чудесно. Мы вроде до оскорблений не скатывались :)wadeg
29.07.2022 12:11+2Вы очень счастливый человек, если работаете только в тех компаниях, где БД не помойка, а круто спроектированная штука. Жаль, что в 99% случаев это не так.
Где неизвестнодаже содержимое полей — 99%? Вы и тут чепуху пишете и упираетесь.Мы вроде до оскорблений не скатывались
Тут нет вообще никаких оскорблений. А есть толстый намек, что писать в сообщество разработчиков, не имея даже начальных представлений о предмете — очень, очень нелепо и просто неразумно.ITResume Автор
29.07.2022 19:09-1Это не наша база. Дана картинка с табличками - решите тестовое. Причем тут вообще мы :)
Если ваши представления настолько глубоки, что вы сходу можете сказать незнакомым людям, что "у них нет даже начальных представлений о предмете" - так может быть вы поделитесь свои опытом и напишете какую-нибудь статейку на тему? :) А то у вас статей 0, а такие "дилетанты" как мы вынуждены заполнять инфополе своим контентом)
wadeg
29.07.2022 19:37Вынуждаете повторяться. Если текст показывает, что незнакомые люди даже не понимают, что такое индексный поиск, то эти незнакомые люди вообще не представляют самых основ предмета, о которым эти незнакомые люди еще и зачем-то пишут на ресурсе, где предмет знаком многим. И да, это можно сказать сходу.
А то у вас статей 0, а такие «дилетанты» как мы вынуждены заполнять инфополе своим контентом)
Да, это действительно очень плохо. Но да, я статей лучше, чем, например, Брент Озар, Пол Рэндал или, если местами сразу по-русски, то Дмитрия Пилюгина — не напишу. И нет, это совсем не означает, что инфополе нужно гхм… «заполнять» абсолютно безграмотными статейками, лишь бы накрутить свою упоминаемость.
Xander_d
27.07.2022 21:12А потому что оператор
betweenпотеряет все записи за сегодня, если не преобразовать эти поля в дату (это особенность сравнения даты-времени в PostgreSQL). Опять же - мы просто перестраховались.Если уж перестраховываться, то лучше привести Now к концу суток (или вместо Now тупо задать 9999 год, как удобнее. Но тут возникает вопрос, не может ли оказаться во входящих данных будущее время).
А если развить мысль, то, при условии нормальных данных, зачем там вообще верхняя граница?
Отдельный вопрос, насколько правильно считать данные за неполный месяц. Вдруг там внутри месяца есть своя динамика, и основная активность приходится на конец месяца? Тогда ваш MAU будет занижен...
grobitto
27.07.2022 21:52Одна дата у кастомера тоже вызывает вопросы - если ставится дата первого обращения, то мау не посчитать, если дата последнего - имеет смысл только мау за последний некалендарный месяц (если кастомер может обращаться в компанию несколько раз)
Xander_d
27.07.2022 21:58Почему "одна"? Я так понял, что это таблица всех звонков (call_id) с таймстэмпом начала и окончания.
grobitto
28.07.2022 08:08Таблицы не было в схеме, ок
Все равно большие вопросы - использование неполного текущего месяца потянет среднее вниз, и баг с тем, что используется только номер месяца, и если данные за несколько лет то все одинаковые месяцы склеятся в один
HappyGroundhog
27.07.2022 22:43+1Кстати насчет «MAU всегда должен быть одним числом» хотелось бы поспорить.
В результате вашего запроса получается та самая «средняя температура по больнице».
В случае столбца Месяц — MAU видна динамика продукта, например,
Январь — 5000
Февраль — 7000
Март — 2000
Апрель — 13000
Май — 15000
По этой динамике можно увидеть дыру в марте, связанную, например, с неудачным продуктовым решением в виде монетизации. А так же виден рост количества активных пользователей.
Если же брать MAU как среднее за месяцы, то будет 8400. При этом абсолютно не видна динамика, может это уже остатки от прежних 5000 или наоборот, была 1000, а стало 8400 и это хорошо! Как абсолютное число смысл имеет, но небольшой.ITResume Автор
28.07.2022 10:20Так мы поэтому и сказали, что это вообще далеко не единственный способ. Медиана, MAU за отдельные месяцы - все это имеет смысл сравнивать. Тогда что-то плюс-минус разумное можно получить.
SergeiMinaev
28.07.2022 00:46Во второй задачке я бы вместо case использовал filter, который как-то логически кажется более уместным. Но могу ошибаться.
uaggster
28.07.2022 09:40+1select concat_ws('—', first_nm, middle_nm, last_nm) as fio, birth_dt from employeesИнтересно, а как в постгресс ведет себя эта функция, если один из операндов - Null или пустая строка? (Я просто не знаю). Для MSSQLSERVER - все будет гораздо вычурнее:
select Stuff(concat('—' + Nullif(first_nm, ''), '-' + Nullif(middle_nm, ''), '-' + Nullif(last_nm,'')), 1, 1, '') as fio, birth_dt from employeesУ человека может не быть отчества, фамилии или даже имени. В любом сочетании.
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Не знаю, как в постгрессе (опять же, не специалист), в MSSQLSERVER так делать категорически нельзя. Я про where start_dttm::date
Это не саргабельно. В случае, если по start_dttm есть индекс, ОН НЕ БУДЕТ ЗАДЕЙСТВОВАН, ну, или будет задействован в режиме полного сканирования. Так что но, найн, нихт, отрывать руки по самый афедрон! Еще один момент: Функция count возвращает результат в int. Поделив int на int (а деление будет ЦЕЛОЧИСЛЕННЫМ, т.к. оба операнда - целые) - вы наверняка получите не то, что нужно.
Выглядеть должно так (в MSSQLSERVER):select Convert(date, start_dttm) as "date", (count(case when dozv_flg=1 then 1 end) * 1.0) / count(case when dozv_flg in (1, 0) then 1 end) as sla from calls where start_dttm >= '20201001' and start_dttm <= Convert(date, Dateadd(day, 1, Current_timestamp)) group by Convert(date, start_dttm)Как то так, остальные разборы разберу потом :-)
ITResume Автор
28.07.2022 10:22Про саргабельность - кажется, это тема заслуживает отдельной статьи. Уверены, что на эту позицию вполне допустимо такого не знать - по уровню заданий это понятно.
Но отличная мысль для следующего материала)
uaggster
28.07.2022 10:27Вот так, конечно же start_dttm < Convert(date, Dateadd(day, 1, Current_timestamp)). Не вычитал, прошу прощения.
lisper
28.07.2022 14:09concat_ws завезли в MS SQL 2017 (https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql).
md_backend_binance
28.07.2022 10:21А можно психотип человека кто идет после всего в теперь гос компанию? )
uaggster
28.07.2022 10:23+1В следующем кейсе - явная ошибка, причем не технического плана, а именно ошибка реализации:
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Дело в том, что в таблице clients могут быть данные ЗА РАЗНЫЕ ГОДЫ.
И тогда ваш запрос вернет хрень (простите мой французский).
Запрос должен выглядеть как:
with a as ( select month(calendar_dt) as mon, count(distinct id) as cnt from clients group by year(calendar_dt), month(calendar_dt) ) select avg(cnt) as mau from aНу, и не забываем, что среднее от int - будет int. А то вдруг вы там десятые ждете? Тогда avg(cnt * 1.0), ну, или явное преобразование к какому либо плавающему или фиксированному типу.
Akina
28.07.2022 14:23+1не забываем, что среднее от int - будет int.
Зависит от СУБД. Скажем, в PostgreSQL это не так. https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8b050e6e76058df1b23f3b8e498f0fd4
EvilangelMD
29.07.2022 11:22можно спросить? а почему вы в вашем примере не вывели год? ведь я в вашем случае буду видеть больше строк с тем же месяцем, без понятия к какому году он относится...
NikolasSumrak
30.07.2022 08:55Потому что результатом выполнения этого запроса будет одно число, месяцы вы там не увидите
Markscheider
28.07.2022 11:37Условие
Дана таблица clinets:
- id клиента
- calendar_at - дата входа в мобильное приложение...
Либо у меня приступ временной слепоты, либо я чего-то не понимаю. В схеме данных НЕТ таблицы clients. По полям похожая - только system...
---
Ну и резануло то, что в тексте задания расхождения по сравнению с кодом (clinets вместо clients, calendar_at вместо calendar_dt).
Или это дополнительный уровень проверки?
Akina
28.07.2022 12:53+2Задача 1 ...
Есть ещё проблема, которую Вы просмотрели. Видите ли, бывают люди, не имеющие отчества. В таблице в этом поле может оказаться NULL. И если использовать CONCAT() или оператор конкатенации, то некоторые СУБД вернут итогом NULL (что очевидно неверно), а некоторые отнесутся к нему как к пустой строке, и в конце итогового значения получится ни к чему не пристёгнутый дефис. В отличие от CONCAT_WS(). А если в поле может в описанном случае оказаться пустая строка - то потребуется ещё и NULLIF(). Ну или CASE - для универсальности.
Кстати, PostgreSQL в этом случае вообще оригинал - функция конкатенации CONCAT() и оператор конкатенации || ведут себя по-разному. Для многих это оказывается сюрпризом - иногда неприятным.
Задача 2
Мы просто перестраховались — вдруг там еще какие-то значения могут быть. Например, 2.
По-моему, условие чётко устанавливает, что для любого поступившего звонка значение поля может быть или 0, или 1 (или звонок принят, или нет). Никаких 2, никаких NULL и иных значений там быть не может, если считать условие задания верным. А поскольку непоступившие звонки в таблице присутствовать не могут, то насчёт перестраховки - это Вы совершенно безосновательно придумали.
И, если принять, что условие не содержит ошибок, CASE вообще становится не нужен, а процент считается простейшим SUM(dozv_flg)/COUNT(dozv_flg). Либо SUM(dozv_flg)/COUNT(*). Что выбрать - опять зависит от конкретной СУБД, в некоторых эти выражения могут дать разную итоговую производительность запроса.
where start_dttm::date between '2020-10-01' and now()::date
А это "прощай производительность". Правильно - не приводить поле к дате, чтобы можно было использовать красивый BETWEEN, а where start_dttm >= '2020-10-01' and start_dttm < CURRENT_DATE + INTERVAL '1 day'.
lokks
28.07.2022 19:57" — " в условии первого задания окружено пробелами (Имя - Фамилия - Отчество), в результате же будет без них: Имя-Фамилия-Отчество.
Ну и да, от нулов не перестраховались.
FoxisII
29.07.2022 16:14-1По поводу названий
иногда возникает ситуация что в витрине не совсем понятно что именно содержится ....
иногда .... ну конечно же ..... иногда ....
а в некоторых случаях плюнул и назвал колонки по Русски - что бы аналитики не приставали с вопросами - А где мне взять то-то ? А что здесь ?
:) вот такой лайф хак :)
s_f1
Что у Тинькова с названиями полей? middle_nm? dozv_flg??? Они серьёзно? Или у них 'a' на клавиатуре отсутствует?
DarkSavant
Не работал в Тиньке, но работал много где еще. Такие именования видел, что аж поначалу страшно становилось от этих аббревиатур на 20+ символов.
Но, разумеется, что мешает именовать нормально и без дурацких сокращений, чтоб было понятно по названию колонки, а не часа ковыряний доки?
ITResume Автор
Хотя кстати иногда лучше даже длинное название дать, но читаемое. Во всяком случае, лучше чем:
* длинное и нечитаемое
* короткое нечитаемое
:D
crckhd
Автор книги "Чистый код" - Дядя Боб плачет. Вторая глава о наименовании тут совсем не работает)
ITResume Автор
Ну это не самый плохой вариант, серьезно) Видели и похуже)
aleksandy
Тяжёлое оракловая травма в начале карьеры. До 10 версии оракул точно не разрешал имена длиннее 30 символов. А если ещё и по каким-нибудь правилам у таблиц должны быть определённые префиксы, то вообще караул.
gluck59
...а там где я работаю, нет доки совсем.
И комментариев в таблицах и полях тоже нет.
И связей между таблицами.
И сокращения в названиях — ууу... Ладно 20 символов, тут хоть иногда можно догадаться о чем речь. А вот 2-3-4 символа это да.
Nialpe
Это вы с обфускацией наименований полей, таблиц, процедур и т.п. не сталкивались. Когда столкнулся (в финтехе тоже) был в шоке долгое время. Совсем как в фильме "Операция Ы". Помните причину почему Ы?
doctorw
чтобы никто не догадался?
ITResume Автор
Мы недавно столкнулись с названием полей одной крупной интернет-компании (не будем тыкать пальцем), вот там вообще треш)) Просто
tbl_clt_fr_crm_ld_ydx_t_cpn- что-то в таком духе)AlexWinner
****ydx***:DITResume Автор
Не, это типа рекламные кампании были, Яндекс тут не причем)
doctorw
*_crm_* приходилось видеть в бд битрикса :(
Aleks_ja
А то, что микс русской и английской транскрипции вас не смущает?
first_nm + middle_nm + last_nm = fio ????
Почему б не full_nm. И раз уж без гласных, то fll_nm.
ITResume Автор
fio оно свое, родное)