LatornLatorn
Войти

SQL: Сводные таблицы через условную агрегацию — Сводка способов доставки VIP-клиентов

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

Сводка способов доставки VIP-клиентов

Что уже дано

Уже заполнены clients(client_id INTEGER PRIMARY KEY,client_name TEXT NOT NULL,tier TEXT NOT NULL) и orders(order_id INTEGER PRIMARY KEY,client_id INTEGER NOT NULL,delivery_type TEXT NOT NULL,amount INTEGER NOT NULL). Первичные ключи положительные и уникальные; дочерний client_id может ссылаться на отсутствующего клиента. tier строго VIP либо regular. delivery_type строго courier либо pickup. amount — целые копейки от 0 до 2 000 000 000; NULL нет. Имена могут повторяться, не содержат | и перевода строки. В каждой таблице от 0 до 1000 строк; суммы помещаются в 64-битный INTEGER.

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

Для каждого VIP-клиента, включая клиентов без заказов, получите courier_amount — сумму courier-заказов, pickup_amount — сумму pickup-заказов и total_amount — сумму всех его заказов. Если нужной категории или всех заказов нет, соответствующая сумма 0. Сохраните исходное имя; курьерская и самовывозная суммы вместе должны составлять общий итог.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно 5 столбцов в указанном порядке и с этими именами: client_id, client_name, courier_amount, pickup_amount, total_amount. client_id и суммы — INTEGER, client_name — TEXT; NULL нет. Сортировка: client_id ASC. Одна строка на VIP-клиента; при отсутствии VIP результат пустой. В тестовых результатах разделитель |, NULL — пустое поле. Допускается любой эквивалентный запрос.

Пример

Подготовка данных
INSERT INTO clients VALUES (1,'A','VIP'),(2,'B','VIP'),(3,'C','VIP'),(4,'D','regular');
INSERT INTO orders VALUES (1,1,'courier',100),(2,1,'courier',100),(3,1,'pickup',50),(4,2,'pickup',70),(5,4,'courier',900);
Вывод
1|A|200|50|250
2|B|0|70|70
3|C|0|0|0
РешениеSQL · SQLite

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

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

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