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

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

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

Задача 2 / 4

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

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

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

Подсказка

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

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

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

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

Подсказка

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

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

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

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

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

Подсказка

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

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

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

Подсказка

xxxxxxxxxx
SELECT month.last_name
FROM
        (SELECT c.last_name
        FROM client AS c
        INNER JOIN invoice AS i ON c.customer_id = i.customer_id
        GROUP BY c.customer_id, i.invoice_date
        HAVING COUNT(invoice_id) IS NOT NULL
        AND EXTRACT(YEAR FROM CAST(i.invoice_date AS date)) = '2013'
        AND EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '01') AS month
     --   AND (EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '02'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '03'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '04'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '05'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '06'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '07'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '08'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '09'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '10'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '11'
       --     OR EXTRACT(MONTH FROM CAST(i.invoice_date AS date)) = '12')) AS id

last_name
Bernard
Gutiérrez
Murray
Pareek
Tremblay