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

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

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

Задача 3 / 4

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

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

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

Подсказка

Попробуйте выполнить задание самостоятельно.

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

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

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

Подсказка

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

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

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

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

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

Подсказка

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

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

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

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

Подсказка

xxxxxxxxxx
 
SELECT c.name AS name_category,
    COUNT(DISTINCT(m.film_id)) AS total_films
FROM movie AS m
LEFT OUTER JOIN film_actor AS fa ON fa.film_id = m.film_id
LEFT OUTER JOIN film_category AS fc ON fc.film_id = m.film_id
LEFT OUTER JOIN category AS c ON c.category_id = fc.category_id
WHERE actor_id IN (SELECT fa.actor_id
                  FROM movie AS m
                  LEFT OUTER JOIN film_actor AS fa ON m.film_id = fa.film_id
                  WHERE m.release_year > 2013
                  GROUP BY fa.actor_id
                  HAVING COUNT(m.film_id) > 7)
GROUP BY c.name
ORDER BY total_films DESC, name_category

name_category	total_films
Family	61
Foreign	60
Action	59
Documentary	58
Sports	58
Children	55
Drama	55
New	54
Games	52
Animation	51
Horror	50
Sci-Fi	50
Travel	46
Music	45
Classics	44
Comedy	44