LatornLatorn
Войти

SQL: CASE, COALESCE и NULLIF — Доля конверсий по кампаниям

Практическая задача по SQL на тему «CASE, COALESCE и NULLIF». В условии приведены схема таблиц, ограничения данных, имена столбцов результата и порядок сортировки. Напишите один SELECT или WITH-запрос для SQLite. Перед проверкой продумайте пустой результат, повторяющиеся строки и границы диапазонов, если они встречаются в условии. Проверяется результат запроса, поэтому допустима эквивалентная реализация. Сначала решите задачу самостоятельно; продолжение практики доступно в полном курсе продвинутого SQL.

Доля конверсий по кампаниям

Что уже дано

В SQLite созданы и заполнены campaigns(campaign_id INTEGER PRIMARY KEY, name TEXT) и clicks(click_id INTEGER PRIMARY KEY, campaign_id INTEGER, converted INTEGER NOT NULL). Первичные ключи — положительные INTEGER. name — TEXT или NULL; имена могут повторяться. converted равен строго 0 (нет конверсии) или 1 (конверсия). campaign_id в clicks может быть NULL или ссылаться на отсутствующую кампанию; такие клики не относятся ни к одной кампании списка. В каждой таблице не более 1000 строк.

Что нужно сделать

Для каждой кампании из campaigns верните total_clicks — число её найденных кликов, conversions — число этих кликов с converted=1, conv_rate — conversions / total_clicks с вещественным делением и округлением до четырёх знаков после запятой по правилам SQLite ROUND. Если кликов нет, оба счётчика равны 0, а conv_rate равен NULL. Нулевая доля при существующих кликах должна оставаться 0.0.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно пять столбцов в таком порядке и с такими именами: campaign_id (INTEGER), name (TEXT или NULL, без подмены), total_clicks (INTEGER), conversions (INTEGER), conv_rate (REAL или NULL). Сортировка по campaign_id по возрастанию. Если кампаний нет, результат пуст. В тестах NULL — пустое поле, столбцы разделены |. Допускается любое эквивалентное решение. NULLIF выражает политику нулевого знаменателя, а *1.0 обеспечивает вещественное деление; эти конкретные конструкции не обязательны, если результат эквивалентен.

Пример

Подготовка данных
INSERT INTO campaigns VALUES
(1,'Лето'),
(2,'Зима'),
(3,'Весна');
INSERT INTO clicks VALUES
(1,1,1),
(2,1,0),
(3,1,1),
(4,2,0),
(5,2,0),
(6,3,1);
Вывод
1|Лето|3|2|0.6667
2|Зима|2|0|0.0
3|Весна|1|1|1.0
РешениеSQL · SQLite

Без регистрации. Гостевая проверка не сохраняет прогресс. Для сохранения перейдите к уроку.

Отправьте решение, чтобы увидеть результаты тестов.

Связанные задачи