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

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

12 Янв 2020
11 мин. чтения

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

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

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

Источник

https://habr.com/ru/company/dcmiran/blog/500360/

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

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).

(Это решение, как и другие блоки кода в этом документе, содержит комментарии об элементах синтаксисе SQL, которые могут отличаться между разными вариантами SQL, и прочие заметки)

Ответ

WITH mau AS
(
  SELECT
   /*
    * Обычно интервьюер позволяет вам написать псевдокод для
    * функций даты, т. е. НЕ будет проверять, как вы их помните.
    * Просто объясните на доске, что делает функция
    *
    * В Postgres доступна DATE_TRUNC(), но аналогичный результат
    * могут дать другие функции даты SQL или их комбинации
    * См. https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
    */
    DATE_TRUNC('month', date) month_timestamp,
    COUNT(DISTINCT user_id) mau
  FROM
    logins
  GROUP BY
    DATE_TRUNC('month', date)
  )

 SELECT
    /*
    * В эту инструкцию SELECT не нужно буквально включать предыдущий месяц.
    *
    * Но как упоминалось в разделе с советами выше, может быть полезно
    * хотя бы набросать самообъединения, чтобы не запутаться, какая
    * таблица представляет прошлый месяц к текущему и т.д.
    */
    a.month_timestamp previous_month,
    a.mau previous_mau,
    b.month_timestamp current_month,
    b.mau current_mau,
    ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change
 FROM
    mau a
 JOIN
    /*
    * Как вариант `ON b.month_timestamp = a.month_timestamp + interval '1 month'`
    */
    mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'

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

Благодарность: это более обобщённое решение предложил Фабиан Хофман 2 мая 2020 года. Спасибо, Фабиан!

Ответы

WITH join_table AS
(
    SELECT
        cur.node,
        cur.parent,
        COUNT(next.node) AS num_children
    FROM
        tree cur
    LEFT JOIN
        tree next ON (next.parent = cur.node)
    GROUP BY
        cur.node,
        cur.parent
)

SELECT
    node,
    CASE
        WHEN parent IS NULL THEN "Root"
        WHEN num_children = 0 THEN "Leaf"
        ELSE "Inner"
    END AS label
FROM
    join_table

Альтернативное решение, без явных соединений:

Благодарность: Уильям Чарджин 2 мая 2020 года обратил внимание на необходимость условия WHERE parent IS NOT NULL, чтобы это решение возвращало Leaf вместо NULL. Спасибо, Уильям!

SELECT
    node,
    CASE
        WHEN parent IS NULL THEN 'Root'
        WHEN node NOT IN
            (SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
        WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
    END AS label
 from
    tree

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

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

Ответы

SELECT
    DATE_TRUNC('month', a.date) month_timestamp,
    COUNT(DISTINCT a.user_id) retained_users
 FROM
    logins a
 JOIN
    logins b ON a.user_id = b.user_id
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
                                             interval '1 month'
 GROUP BY
    date_trunc('month', a.date)

Благодарность: Том Моэртел указал на то, что предварительная дедубликация user_id перед самообъединением делает решение более эффективным, и предложил код ниже. Спасибо, Том!

Альтернативное решение:

WITH DistinctMonthlyUsers AS (
  /*
  * Для каждого месяца определяем *набор* пользователей, которые
  * выполнили авторизацию
  */
    SELECT DISTINCT
      DATE_TRUNC('MONTH', a.date) AS month_timestamp,
      user_id
    FROM logins
  )

SELECT
  CurrentMonth.month_timestamp month_timestamp,
  COUNT(PriorMonth.user_id) AS retained_user_count
FROM
    DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN
    DistinctMonthlyUsers AS PriorMonth
  ON
    CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
    AND
    CurrentMonth.user_id = PriorMonth.user_id

Часть 2

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

Ответ

SELECT
    DATE_TRUNC('month', a.date) month_timestamp,
    COUNT(DISTINCT b.user_id) churned_users
FROM
    logins a
FULL OUTER JOIN
    logins b ON a.user_id = b.user_id
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
                                         interval '1 month'
WHERE
    a.user_id IS NULL
GROUP BY
    DATE_TRUNC('month', a.date)

Обратите внимание, что эту проблему можно решить также с помощью соединений 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.

Ответ

WITH user_login_data AS
(
    SELECT
        DATE_TRUNC('month', a.date) month_timestamp,
        a.user_id,
        /*
        * По крайней мере, в тех вариантах SQL, что я использовал,
        * не нужно включать в инструкцию SELECT колонки из HAVING.
        * Я здесь выписал их для большей ясности.
        */
        MAX(b.month_date) as most_recent_churn,
        MAX(DATE_TRUNC('month', c.date)) as most_recent_active
     FROM
        logins a
     JOIN
        user_churns b
            ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date
     JOIN
        logins c
            ON a.user_id = c.user_id
            AND
            DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
     WHERE
        DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
     GROUP BY
        DATE_TRUNC('month', a.date),
        a.user_id
     HAVING
        most_recent_churn > most_recent_active

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

Ответы

SELECT
    a.date date,
    SUM(b.cash_flow) as cumulative_cf
FROM
    transactions a
JOIN b
    transactions b ON a.date >= b.date
GROUP BY
    a.date
ORDER BY
    date ASC

Альтернативное решение с использованием оконной функции (более эффективное!):

SELECT
    date,
    SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf
FROM
    transactions
ORDER BY
    date ASC

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-дневное скользящее среднее ежедневных регистраций.

Ответ

SELECT
  a.date,
  AVG(b.sign_ups) average_sign_ups
FROM
  signups a
JOIN
  signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY
  a.date

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 и другими адресатами.

Ответ

SELECT
    a.id,
    MIN(b.timestamp) - a.timestamp as time_to_respond
FROM
    emails a
JOIN
    emails b
        ON
            b.subject = a.subject
        AND
            a.to = b.from
        AND
            a.from = b.to
        AND
            a.timestamp < b.timestamp
 WHERE
    a.to = 'zach@g.com'
 GROUP BY
    a.id

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

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 с самой высокой зарплатой. Убедитесь, что ваше решение обрабатывает случаи одинаковых зарплатами!

Ответы

WITH max_salary AS (
    SELECT
        MAX(salary) max_salary
    FROM
        salaries
    )
SELECT
    s.empno
FROM
    salaries s
JOIN
    max_salary ms ON s.salary = ms.max_salary

Альтернативное решение с использованием RANK():

WITH sal_rank AS
  (SELECT
    empno,
    RANK() OVER(ORDER BY salary DESC) rnk
  FROM
    salaries)
SELECT
  empno
FROM
  sal_rank
WHERE
  rnk = 1;

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

Ответ

SELECT
    *,
    /*
    * AVG() is a Postgres command, but other SQL flavors like BigQuery use
    * AVERAGE()
    */
    ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
    salaries

Часть 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

Ответ

SELECT
    *,
    RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
 FROM
    salaries

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

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

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

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

bucket count
20-25 2
450-455 1

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

Ответ

WITH bin_label AS
(SELECT
    session_id,
    FLOOR(length_seconds/5) as bin_label
 FROM
    sessions
 )
 SELECT
    CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket,
    COUNT(DISTINCT session_id) count
 GROUP BY
    bin_label
 ORDER BY
    bin_label ASC

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

Часть 1

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

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

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

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

state_a state_b
NC SC
SC NC

Ответ

SELECT
    a.state as state_a,
    b.state as state_b
 FROM
    state_streams a
 CROSS JOIN
    state_streams b
 WHERE
    ABS(a.total_streams - b.total_streams) < 1000
    AND
    a.state <> b.state

Для информации, перекрёстные соединения также можно писать без явного указания соединения:

SELECT
    a.state as state_a,
    b.state as state_b
 FROM
    state_streams a, state_streams b
 WHERE
    ABS(a.total_streams - b.total_streams) < 1000
    AND
    a.state <> b.state

Часть 2

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

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

Ответ

SELECT
    a.state as state_a,
    b.state as state_b
 FROM
    state_streams a, state_streams b
 WHERE
    ABS(a.total_streams - b.total_streams) < 1000
    AND
    a.state > b.state

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

Ответы

WITH usr_b_sum AS
(
    SELECT
        user,
        SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
    FROM
        table
    GROUP BY
        user
),

usr_class_label AS
(
    SELECT
        user,
        CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class
    FROM
        usr_b_sum
)

SELECT
    class,
    COUNT(DISTINCT user) count
FROM
    usr_class_label
GROUP BY
    class
ORDER BY
    class ASC

Альтернативное решение использует инструкции SELECT в операторах SELECT и UNION:

SELECT
    "a" class,
    COUNT(DISTINCT user_id) -
        (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count
UNION
SELECT
    "b" class,
    (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count