LatornLatorn
Войти

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

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

Сводка платежей с NULL-категориями и прочими статусами

Что уже дано

Уже заполнены projects(project_id INTEGER PRIMARY KEY) и payments(payment_id INTEGER PRIMARY KEY,project_id INTEGER NOT NULL,status TEXT NOT NULL,amount INTEGER NOT NULL). Все ID положительные INTEGER; первичные ключи уникальны. Платёж может ссылаться на отсутствующий проект, тогда в отчёт справочника он не входит. status строго confirmed, refunded, pending либо disputed. amount — неотрицательные целые копейки до 2 000 000 000, без NULL. Суммы возвратов хранятся положительными; здесь нужен валовой оборот, а не выручка после вычитания возвратов. До 1000 строк в каждой таблице; суммы помещаются в 64-битный INTEGER.

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

Для каждого проекта из projects получите confirmed_amount и refunded_amount — суммы соответствующих статусов, оставляя NULL при отсутствии статуса. total_amount — сумма всех платежей независимо от статуса, 0 при отсутствии платежей. unallocated_amount — сумма pending и disputed, 0 при отсутствии этих статусов. Нулевой платёж выбранного статуса даёт 0 в его ячейке, а не NULL.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно 5 столбцов в указанном порядке и с этими именами: project_id, confirmed_amount, refunded_amount, total_amount, unallocated_amount. Все значения INTEGER; NULL допустим только в confirmed_amount и refunded_amount. Сортировка: project_id ASC. Одна строка на каждый проект; пустая projects даёт пустой результат. В тестовых результатах разделитель |, NULL — пустое поле. Допускается любой эквивалентный запрос.

Пример

Подготовка данных
INSERT INTO projects VALUES (1),(2),(3),(4);
INSERT INTO payments VALUES (1,1,'confirmed',100),(2,1,'refunded',50),(3,1,'pending',30),(4,1,'disputed',20),(5,2,'pending',7),(6,4,'refunded',0);
Вывод
1|100|50|200|50
2|||7|7
3|||0|0
4||0|0|0
РешениеSQL · SQLite

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

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

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