Разбор тестового задания на SQL в EdTech-компании

Как и в любом навыке, регулярная практика — твой лучший друг. Особенно если речь идет про прохождение технических интервью, нужно постоянно держать себя в форме.

Предлагаю потренироваться на задачах с тестового задания на SQL.

Дано две таблицы.

Первая — finished_lessons, в ней лежит информация о пройденных уроках:

Вторая — profession_dop, в ней лежит информация о подписках пользователей на профессии:

Разберем несколько SQL-запросов по этим таблицам.

1. Необходимо посмотреть, какое количество пользователей находятся в потоке 17 по профессии data-analyst

Простой запрос из одной таблицы. Фильтруем profession_dop по потоку 17 и профессии data-analyst, затем считаем количество пользователей:

1SELECT2    COUNT(DISTINCT user_id) AS users3FROM profession_dop4WHERE profession_name = 'data-analyst'5    AND squad = '17';

2. Необходимо посмотреть, какое количество пользователей по профессии data-scientist подписались в 2020 году

Тоже довольно прямолинейный запрос. Используем функцию YEAR() для извлечения года из даты подписки и фильтруем по 2020 году:

1SELECT2    COUNT(DISTINCT user_id) AS users3FROM profession_dop4WHERE profession_name = 'data-scientist'5    AND YEAR(subscription_date) = 2020;

3. Необходимо найти, какое количество уроков прошли студенты по профессии project-manager

Для решения этой задачи подойдет и подзапрос user_id IN (SELECT …), и JOIN двух таблиц.

Предлагаемый ниже запрос обеспечивает лучшую читаемость и производительность за счет CTE:

1WITH project_managers AS (2    SELECT DISTINCT user_id3    FROM profession_dop4    WHERE profession_name = 'project-manager'5)6SELECT7    COUNT(DISTINCT lesson_id) AS lessons8FROM finished_lessons9INNER JOIN project_managers USING(user_id);

4. Необходимо найти первый пройденный урок (lesson_id) для пользователей, подписавшихся на профессию qa в марте 2020 года

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

1. Найти самый первый урок за все время для каждого пользователя из подписавшихся на профессию qa в марте 2020 года.

2. Найти первый урок по qa для каждого пользователя из подписавшихся на профессию qa в марте 2020 года.

Решение первого варианта восприятия:

1WITH qa_users AS (2    SELECT DISTINCT user_id3    FROM profession_dop4    WHERE profession_name = 'qa'5        AND YEAR(subscription_date) = 20206        AND MONTH(subscription_date) = 37),8first_lesson_date AS (9    SELECT 10        fl.user_id,11        MIN(fl.date_created) AS first_lesson_date12    FROM finished_lessons fl13    INNER JOIN qa_users USING(user_id)14    GROUP BY fl.user_id15)16SELECT17    fl.user_id,18    fl.lesson_id19FROM finished_lessons AS fl20INNER JOIN first_lesson_date AS fld21    ON fl.user_id = fld.user_id22    AND fl.date_created = fld.first_lesson_date;

Решение второго варианта можете предложить в комментариях.

5. Необходимо вычислить, на какие из профессий подписано больше всего студентов — вывести топ-5

Используем оконную функцию DENSE_RANK() для ранжирования профессий по количеству студентов, затем выбираем топ-5.

Способ формирования топа лучше уточнять. В моем случае, если какая-то профессия делит с кем-то место — хотим видеть все и считать за одно место:

1WITH profession_counts AS (2    SELECT3        profession_name,4        COUNT(DISTINCT user_id) AS student_count,5        DENSE_RANK() OVER (6            ORDER BY COUNT(DISTINCT user_id) DESC7        ) AS rank8    FROM profession_dop9    GROUP BY profession_name10)11SELECT 12    profession_name, 13    student_count14FROM profession_counts15WHERE rank <= 5;

6. Необходимо вывести уроки и даты их прохождения по пользователю 145768, отсортировав от первого пройденного урока к последнему, по профессии project-manager

Вообще кажется, что в БД должно быть больше полей. Для решения этой задачи таблица profession_dop должна содержать поле с id курса — profession_id. А база с уроками должна содержать id профессии, в которой был выполнен урок. Либо должна быть третья таблица для маппинга профессий и id уроков у них.

Если студент будет одновременно проходить два курса или начнет один, потом забросит его, потом начнет другой, а после этого вернется к первому — невозможно будет разделить уроки двух разных курсов.

Тем не менее, мой вариант решения. Находим пользователя и дату начала его обучения по профессии project-manager. Ищем все уроки этого пользователя, которые начались после его поступления на эту профессию. Сортируем в порядке возрастания даты:

1WITH project_manager_user AS (2    SELECT3        user_id,4        subscription_date5    FROM profession_dop6    WHERE profession_name = 'project-manager'7        AND user_id = 1457688)9SELECT10    fl.lesson_id,11    fl.date_created12FROM finished_lessons fl13INNER JOIN project_manager_user USING(user_id)14WHERE date_created >= subscription_date15ORDER BY fl.date_created ASC;

Главный вывод: на собеседовании важно не только написать SQL-запрос, но и заметить ограничения схемы данных.

Иногда правильный ответ – это не просто код, а уточнение бизнес-логики и объяснение, почему задача в текущем виде решается неоднозначно.