Практика: как сочетать объединения и подзапросы

Как мы уже говорили, не существует универсального правила сочетания подзапросов и присоединений. Но всё-таки напомним, как это можно реализовать в коде.

Схема базы данных

Чтобы было удобнее решать задачи, скачайте PDF-версию ER-диаграммы с описанием таблиц. Или откройте диаграмму в соседней вкладке.

Задача 4 / 4

Составьте сводную таблицу. Посчитайте заказы, оформленные за каждый месяц в течение нескольких лет: с 2011 по 2013 год включительно. Итоговая таблица должна включать четыре поля: invoice_month, year_2011, year_2012, year_2013. Поле month должно хранить месяц в виде числа от 1 до 12.

Если в какой-либо месяц заказы не оформляли, номер такого месяца всё равно должен попасть в таблицу.

В этом задании не будет подсказок. Используйте любые методы, которые посчитаете нужными.

Подсказка

Отберите фамилии пользователей, которые:

оформили хотя бы один заказ в январе 2013 года,
а также сделали хотя бы один заказ в любом другом месяце того же года.

Пользователей, которые оформили заказы только в январе, а в остальное время ничего не заказывали, в таблицу включать не нужно.

Подсказка

Сформируйте статистику по категориям фильмов. Отобразите в итоговой таблице два поля:

название категории,
число фильмов из этой категории.

Фильмы для второго поля нужно отобрать по условию. Посчитайте фильмы только с теми актёрами и актрисами, которые больше семи раз снимались в фильмах, вышедших после 2013 года.

Назовите поля name_category и total_films соответственно. Отсортируйте таблицу по количеству фильмов от большего к меньшему, а затем по полю с названием категории в лексикографическом порядке.

Подсказка

Определите, летом какого года общая выручка была максимальной. Выгрузите таблицу, в которую войдут поля:

country — название страны;
total_invoice — число заказов, оформленных в этой стране в тот год, когда выручка за лето была максимальной;
total_customer — число клиентов, зарегистрированных в этой стране.

Отсортируйте таблицу по убыванию значений в поле total_invoice, а затем добавьте сортировку по названию страны в лексикографическом порядке.

Подсказка

Разберите задачу на несколько подзадач. Начните с года с максимальной выручкой за лето. Теперь можно группировать по странам и считать заказы, оформленные в нужный период. Отдельным подзапросом найдите число клиентов, зарегистрированных в каждой стране. Соединить подзапросы можно по общему полю — стране заказа.

xxxxxxxxxx
 
SELECT billing_country AS country,
        summa AS total_invoice,
        clients AS toral_customer
FROM (SELECT billing_country,
             COUNT(invoice_id) AS summa
     FROM invoice AS i
     WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = (SELECT year FROM (SELECT EXTRACT(YEAR FROM CAST(invoice_date AS date)) AS year, SUM(total)
                                                                             FROM invoice
                                                                             WHERE EXTRACT (MONTH FROM CAST(invoice_date AS date)) IN ('06', '07', '08')
                                                                             GROUP BY 1
                                                                             ORDER BY 2 DESC
                                                                             LIMIT 1) AS best_year)
     GROUP BY 1) AS tnew
     LEFT JOIN (SELECT country,
               COUNT(customer_id) AS clients
               FROM client
               GROUP BY 1) AS tnew2 ON tnew.billing_country = tnew2.country
               ORDER BY 2 DESC, 1;

country	total_invoice	toral_customer
USA	19	13
Canada	11	8
France	8	5
Germany	8	4
Brazil	4	5
Czech Republic	4	2
United Kingdom	4	3
Belgium	3	1
Finland	3	1
India	3	2
Ireland	3	1
Portugal	3	2
Chile	2	1
Netherlands	2	1
Norway	2	1
Spain	2	1
Argentina	1	1
Australia	1	1