Вопросы к аналитику данных по SQL

Здесь приводится 11 вопросов, “средней сложности” по SQL для специалистов, которые занимаются анализом данных. Это авторская подборка вопросов, с которыми пришлось столкнуться автору. И по его словам.

Оно полезно для собеседований, но заодно повысит вашу эффективность на текущем и будущих местах работы. Лично я считаю, что некоторые упомянутые шаблоны SQL полезны и для ETL-систем, на которых работают инструменты отчётности и функции анализа данных для выявления тенденций.

Более подробно с информацией можете ознакомиться по ссылке ниже. Здесь приведены только вопросы.

Источник

Вопросы к аналитику данных по SQL

Задачи на самообъединение

1. Процентное изменение месяц к месяцу

Контекст: часто полезно знать, как изменяется ключевая метрика, например, месячная аудитория активных пользователей, от месяца к месяцу. Допустим у нас есть таблица logins в таком виде:

user_id date
1 2018-07-01
234 2018-07-02
3 2018-07-02
1 2018-07-02
234 2018-10-04

Задача: найти ежемесячное процентное изменение месячной аудитории активных пользователей (MAU).

2. Маркировка древовидной структуры

Контекст: предположим, у вас есть таблица tree с двумя столбцами: в первом указаны узлы, а во втором — родительские узлы.

node   parent
1       2
2       5
3       5
4       3
5       NULL 

Задача: написать SQL таким образом, чтобы мы обозначили каждый узел как внутренний (inner), корневой (root) или конечный узел/лист (leaf), так что для вышеперечисленных значений получится следующее:

node    label  
1       Leaf
2       Inner
3       Inner
4       Leaf
5       Root

3. Удержание пользователей в месяц (3 части)

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Использование самообъединений для расчёта показателей удержания, оттока и реактивации».

Часть 1

Контекст: допустим, у нас есть статистика по авторизации пользователей на сайте в таблице logins:

user_id date
1 2018-07-01
234 2018-07-02
3 2018-07-02
1 2018-07-02
234 2018-10-04

Задача: написать запрос, который получает количество удержанных пользователей в месяц. В нашем случае данный параметр определяется как количество пользователей, которые авторизовались в системе и в этом, и в предыдущем месяце.

Часть 2

Задача: теперь возьмём предыдущую задачу по вычислению количества удержанных пользователей в месяц — и перевернём её с ног на голову. Напишем запрос для подсчёта пользователей, которые не вернулись на сайт вэтом месяце. То есть «потерянных» пользователей.

Обратите внимание, что эту проблему можно решить также с помощью соединений LEFT или RIGHT.

Часть 3

Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить и перейти к следующей задаче.

Контекст: итак, мы хорошо справились с двумя предыдущими проблемами. По условиям новой задачи теперь у нас появилась таблица потерянных пользователей user_churns. Если пользователь была активен в прошлом месяце, но затем не активен в этом, то он вносится в таблицу за этот месяц. Вот как выглядит user_churns:

user_id month_date
1 2018-05-01
234 2018-05-01
3 2018-05-01
12 2018-05-01
234 2018-10-01

Задача: теперь вы хотите провести когортный анализ, то есть анализ совокупности активных пользователей, которые были реактивированы в прошлом. Создайте таблицу с такими пользователями. Для создания когорты можете использовать таблицы user_churns и logins. В Postgres текущая временная метка доступна через current_timestamp.

4. Нарастающий итог

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Моделирование денежных потоков в SQL».

Контекст: допустим, у нас есть таблица transactions в таком виде:

date cash_flow
2018-01-01 -1000
2018-01-02 -100
2018-01-03 50

Где cash_flow — это выручка минус затраты за каждый день.

Задача: написать запрос, чтобы получить нарастающий итог для денежного потока каждый день таким образом, чтобы в конечном итоге получилась таблица в такой форме:

date cumulative_cf
2018-01-01 -1000
2018-01-02 -1100
2018-01-03 -1050

5. Скользящее среднее

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Скользящие средние в MySQL и SQL Server».

Примечание: скользящее среднее можно вычислить разными способами. Здесь мы используем предыдущее среднее значение. Таким образом, метрика для седьмого дня месяца будет средним значением предыдущих шести дней и его самого.

Контекст: допустим, у нас есть таблица signups в таком виде:

date sign_ups
2018-01-01 10
2018-01-02 20
2018-01-03 50
2018-10-01 35

Задача: написать запрос, чтобы получить 7-дневное скользящее среднее ежедневных регистраций.

6. Несколько условий соединения

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Анализ вашей электронной почты с помощью SQL».

Контекст: скажем, наша таблица emails содержит электронные письма, отправленные с адреса zach@g.com и полученные на него:

id subject from to timestamp
1 Yosemite zach@g.com thomas@g.com 2018-01-02 12:45:03
2 Big Sur sarah@g.com thomas@g.com 2018-01-02 16:30:01
3 Yosemite thomas@g.com zach@g.com 2018-01-02 16:35:04
4 Running jill@g.com zach@g.com 2018-01-03 08:12:45
5 Yosemite zach@g.com thomas@g.com 2018-01-03 14:02:01
6 Yosemite thomas@g.com zach@g.com 2018-01-03 15:01:05
.. .. .. .. ..

Задача: написать запрос, чтобы получить время отклика на каждое письмо (id), отправленное на zach@g.com. Не включать письма на другие адреса. Предположим, что у каждого треда уникальная тема. Имейте в виду, что в треде может быть несколько писем туда и обратно между zach@g.com и другими адресатами.

Задачи на оконные функции

7. Найти идентификатор с максимальным значением

Контекст: Допустим, у нас есть таблица salaries с данными об отделах и зарплате сотрудников в следующем формате:

depname empno salary
develop 11 5200
develop 7 4200
develop 9 4500
develop 8 6000
develop 10 5200
personnel 5 3500
personnel 2 3900
sales 3 4800
sales 1 5000
sales 4 4800

Задача: написать запрос, чтобы получить empno с самой высокой зарплатой. Убедитесь, что ваше решение обрабатывает случаи одинаковых зарплатами!

8. Среднее значение и ранжирование с оконной функцией (2 части)

Часть 1

Контекст: допустим, у нас есть таблица salaries в таком формате:

depname empno salary
develop 11 5200
develop 7 4200
develop 9 4500
develop 8 6000
develop 10 5200
personnel 5 3500
personnel 2 3900
sales 3 4800
sales 1 5000
sales 4 4800

Задача: написать запрос, который возвращает ту же таблицу, но с новым столбцом, в котором указана средняя зарплата по департаменту. Мы бы ожидали таблицу в таком виде:

depname empno salary avg_salary
develop 11 5200 5020
develop 7 4200 5020
develop 9 4500 5020
develop 8 6000 5020
develop 10 5200 5020
personnel 5 3500 3700
personnel 2 3900 3700
sales 3 4800 4867
sales 1 5000 4867
sales 4 4800 4867

Часть 2

Задача: напишите запрос, который добавляет столбец с позицией каждого сотрудника в табели на основе его зарплаты в своём отделе, где сотрудник с самой высокой зарплатой получает позицию 1. Мы бы ожидали таблицу в таком виде:

depname empno salary salary_rank
develop 11 5200 2
develop 7 4200 5
develop 9 4500 4
develop 8 6000 1
develop 10 5200 2
personnel 5 3500 2
personnel 2 3900 1
sales 3 4800 2
sales 1 5000 1
sales 4 4800 2

9. Гистограммы

Контекст: Допустим, у нас есть таблица sessions, где каждая строка представляет собой сеанс потоковой передачи видео с длиной в секундах:

session_id length_seconds
1 23
2 453
3 27
.. ..

Задача: написать запрос, чтобы подсчитать количество сеансов, которые попадают промежутки по пять секунд, т. е. для приведённого выше фрагмента результат будет примерно такой:

bucket count
20-25 2
450-455 1

Максимальная оценка засчитывается за надлежащие метки строк (“5-10” и т. д.)

10. Перекрёстное соединение (2 части)

Часть 1

Контекст: допустим, у нас есть таблица state_streams, где в каждой строке указано название штата и общее количество часов потоковой передачи с видеохостинга:

state total_streams
NC 34569
SC 33999
CA 98324
MA 19345
.. ..

(На самом деле в агрегированных таблицах такого типа обычно есть ещё столбец даты, но для этой задачи мы его исключим)

Задача: написать запрос, чтобы получить пары штатов с общим количеством потоков в пределах тысячи друг от друга. Для приведённого выше фрагмента мы хотели бы увидеть что-то вроде:

state_a state_b
NC SC
SC NC

Часть 2

Примечание: этот скорее бонусный вопрос, чем реально важный шаблон SQL. Можете его пропустить!

Задача: как можно изменить SQL из предыдущего решения, чтобы удалить дубликаты? Например, на примере той же таблицы, чтобы пара NC и SC появилась только один раз, а не два.

11. Продвинутые расчёты

Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить её!

Контекст: допустим, у нас есть таблица table такого вида, где одному и тому же пользователю user могут соответствовать разные значения класса class:

user class
1 a
1 b
1 b
2 b
3 a

Задача: предположим, что существует только два возможных значения для класса. Напишите запрос для подсчёта количества пользователей в каждом классе. При этом пользователи с обеими метками a и b должны относиться к классу b.

Для нашего образца получится такой результат:

class count
a 1
b 2