sql WITH botusers AS ( SELECT DISTINCT userid FROM visits WHERE LOWER(url) LIKE '%bot%' AND eventdate BETWEEN '2024-12-01' AND '2024-12-31' ), allusers AS ( SELECT DISTINCT userid FROM visits WHERE eventdate BETWEEN '2024-12-01' AND '2024-12-31' ) SELECT ROUND(COUNT(DISTINCT b.userid) 100.0 / COUNT(DISTINCT a.userid), 1) AS share FROM allusers a LEFT JOIN botusers b ON a.userid = b.userid;
Доля трафика от ботов
Ответ и пояснения приведены в подробном разборе ниже.
Короткий ответ
Что ответить на собеседовании
Подробный разбор
Ответ с пояснениями
Условие
Доля трафика от ботов
Команда антифрода разработала механизм определения трафика от ботов на сайт магазина — при входе бота на сайт в параметры его URL добавляется подстрока "bot" (без учета регистра). Если user_id хотя бы один раз был определен как бот в декабре, то он всегда должен определяться как бот.
Изучите датасет с визитами пользователей на сайт и посчитайте долю ботов от общего числа пользователей в декабре 2024 года (округлите ее до одного знака после запятой).
Формат ввода Таблица visits:
- event_date (date) — дата визита
- user_id (int) — уникальный идентификатор пользователя
- url (string) — ссылка, по которой был осуществлен переход
Данные не содержат пропусков или некорректных значений.
Формат вывода Запрос должен вернуть таблицу с полями:
- share (float) — доля ботов от общего числа пользователей в декабре, округленная до одного знака после запятой.
Ответ
sql WITH bot_users AS ( SELECT DISTINCT user_id FROM visits WHERE LOWER(url) LIKE '%bot%' AND event_date BETWEEN '2024-12-01' AND '2024-12-31' ), all_users AS ( SELECT DISTINCT user_id FROM visits WHERE event_date BETWEEN '2024-12-01' AND '2024-12-31' ) SELECT ROUND(COUNT(DISTINCT b.user_id) * 100.0 / COUNT(DISTINCT a.user_id), 1) AS share FROM all_users a LEFT JOIN bot_users b ON a.user_id = b.user_id;
Этот запрос сначала находит всех пользователей, у которых в декабре 2024 был хотя бы один визит с "bot" в URL (без учета регистра), затем всех пользователей в декабре. После этого вычисляет долю ботов от общего числа пользователей и округляет до одного знака после запятой.