Sprint SQL

--THEME 2/7 Lession 5-10

SELECT total,
       customer_id

FROM invoice

WHERE (billing_city = 'Dublin'
    OR billing_city = 'London'
    OR billing_city = 'Paris')
    AND total >= 5
    AND (customer_id = 40
    OR customer_id = 46);

SELECT billing_city,
       billing_address
FROM invoice
WHERE CAST (invoice_date AS DATE) <= '2009-09-30'
    AND CAST (invoice_date AS DATE) >= '2009-09-01'

    AND billing_country != 'Brazil'
    AND billing_country != 'USA'
    AND total > 2;

SELECT name
FROM playlist
WHERE name LIKE '%Classic%';


SELECT billing_address,
       billing_country
FROM invoice
WHERE billing_country IN ('USA',
                          'India',
                          'Canada',
                          'Argentina',
                          'France')
AND billing_city != 'Redmond'
AND billing_city != 'Lyon'
AND billing_city != 'Delhi';-- сюда добавьте условие;

--5.
--Выгрузите из таблицы movie название фильмов, описание которых заканчивается подстрокой Mexico. Аренда фильмов из итоговой таблицы должна составлять меньше двух долларов либо их рейтинг не должен быть PG-13.

SELECT title
FROM movie
WHERE description LIKE '%Mexico%'
    AND (rental_rate < 2
    OR rating != 'PG-13');


--6.
--Из таблицы с треками отберите названия композиций, которые соответствуют условиям. В итоговую таблицу должны войти:
треки длиннее 300000 миллисекунд, в создании которых принимал участие Bono, с идентификаторами жанра 7, 8, 9, 10;
либо треки, размер которых превышает 1000000000 байт.

SELECT name
FROM track
WHERE composer LIKE '%Bono%'
    AND milliseconds > '300000'
    AND genre_id >= 7
    AND genre_id <= 10
    OR bytes > 1000000000;


--THEME 2/7 Lession 11
--1.
--Добавьте условие в код задания: выгрузите из таблицы invoice несколько полей, в которых указаны идентификаторы покупателей от 20 до 50 включительно.

SELECT customer_id,
       invoice_date,
       total
FROM invoice
WHERE customer_id BETWEEN 20 AND 50;-- сюда запишите условие;

--2.
--Добавьте к выгруженным полям информацию о месяце и неделе заказа. Информацию о дате хранит поле invoice_date. Месяц заказа должен быть представлен первым числом месяца в формате '2009-01-01 00:00:00', а неделя заказа — номером недели.
--Не забудьте изменить тип данных поля invoice_date, чтобы применить функции для работы с датой.  Поменяйте тип данных поля на timestamp. Тогда данные не исказятся от автоматической поправки на часовой пояс.

SELECT customer_id,
       invoice_date,
       total,
DATE_TRUNC ('month',
CAST (invoice_date AS TIMESTAMP)),
    EXTRACT (WEEK FROM CAST (invoice_date AS TIMESTAMP))
FROM invoice
WHERE customer_id BETWEEN 20 AND 50;

--3.
--Отфильтруйте получившуюся таблицу по номерам недели. Оставьте в таблице данные за 5, 7, 10, 33 и 48 недели. Добавьте второе условие после оператора WHERE: укажите EXTRACT(WEEK FROM CAST(invoice_date AS timestamp)), чтобы выделить номер недели, и выберите нужные номера с помощью оператора IN.

SELECT customer_id,
       invoice_date,
       total,
       DATE_TRUNC('month', CAST(invoice_date AS TIMESTAMP)),
       EXTRACT(WEEK FROM CAST(invoice_date AS TIMESTAMP))
FROM invoice
WHERE customer_id BETWEEN 20 AND 50
AND EXTRACT(WEEK FROM CAST(invoice_date AS TIMESTAMP)) IN (5,
                                                       7,
                                                       10,
                                                       33,
                                                       48);

--4.
--Выгрузите из таблицы invoice всю информацию о заказах, оформленных первого числа каждого месяца. Не забудьте привести дату к типу timestamp.
--Подсказка. Получить из даты первое число месяца можно двумя способами. Первый — можно усечь дату до нужного значения функцией DATE_TRUNC. Второй вариант — выделить номер дня функцией EXTRACT.

SELECT *
FROM invoice
WHERE EXTRACT(DAY FROM CAST(invoice_date AS TIMESTAMP)) = 1

--5.
--Выгрузите адреса электронной почты сотрудников из города Калгари (англ. Calgary), которых наняли на работу в 2002 году. Попробуйте выполнить это задание без подсказок.

SELECT email
FROM staff
WHERE city = 'Calgary'
AND EXTRACT(YEAR FROM CAST(hire_date AS TIMESTAMP)) = 2002;

----THEME 2/7 Lession 11

SELECT total,
       CASE
           WHEN total < 5 THEN 'маленький'
           WHEN total >= 5 AND total < 10 THEN 'средний'
           WHEN total >= 10 THEN 'крупный'
       END
FROM invoice
LIMIT 10;

SELECT total,
       CASE
           WHEN total >= 5 AND total < 10 THEN 'средний'
           WHEN total >= 10 THEN 'крупный'
           ELSE 'маленький'
       END
FROM invoice
LIMIT 10;

--1.
--Выделите категории в таблице staff, которая хранит информацию о сотрудниках. Выведите на экран поля last_name, first_name и title. Категории нужно выделить на основе значений в поле title — оно содержит информацию о должности сотрудника:
--Если в title встречается слово 'IT', в новом поле будет отображена категория 'разработка'.
--Если в title встречается слово 'Manager' и нет слова 'IT', в новом поле отобразится категория 'отдел продаж'.
--Если в title встречается слово 'Support', в новом поле появится категория 'поддержка'.

SELECT last_name,
       first_name,
       title,-- здесь укажите нужные поля
      CASE
          WHEN title LIKE '%IT%' THEN 'разработка'
          WHEN title LIKE '%Manager%' THEN 'отдел продаж'
          WHEN title LIKE '%Support%' THEN 'поддержка'
      END -- сюда запишите условия
FROM staff;

--2.
--Теперь попробуйте разделить на категории фильмы из таблицы movie. Выведите на экран поля title и rental_rate. Выделить категории нужно по цене аренды фильма:
--Если значение rental_rate меньше 1, в новом поле появится категория 'категория 1'.
--Если значение rental_rate больше либо равно 1, но меньше 3, в новом поле отобразится категория 'категория 2'.
--Если значение rental_rate больше либо равно 3, в новом поле появится категория 'категория 3'.

SELECT title,
       rental_rate,-- сюда добавьте поля
       CASE
           WHEN rental_rate < 1 THEN 'категория 1'
           WHEN rental_rate >= 1 AND rental_rate < 3 THEN 'категория 2'
           WHEN rental_rate > 3 THEN 'категория 3'
           END-- здесь запишите условия
FROM movie;

--THEME 2/7 Lession 13

--1.
--Из таблицы invoice выгрузите поле billing_city с городами оформления счёта. Выгрузите только те записи, в которых на месте индекса стоит пропуск. Данные с индексами хранит поле billing_postal_code.

SELECT billing_city
FROM invoice
WHERE billing_postal_code IS NULL;-- сюда запишите условие;

--2.
--Дополните предыдущий запрос. Исключите из выдачи записи с пропусками в поле billing_state. Выберите записи, в которых сумма заказа в поле total не ниже 15 долларов.

SELECT billing_city
FROM invoice
WHERE billing_postal_code IS NULL
  AND billing_state IS NOT NULL
  AND total > 15;-- дополните условие;

--3.
--Найдите треки длиннее 250000 миллисекунд, в названии которых есть слово Moon, но автор трека не указан. Выгрузите номера альбомов, в которых находятся выбранные треки.

SELECT album_id
FROM track
WHERE name LIKE '%Moon%'
AND (composer IS NULL
AND milliseconds > 250000);

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

SELECT first_name,
       last_name,
       country
FROM client
WHERE company IS NULL
AND state IS NULL
AND phone IS NULL
AND fax IS NULL;

-- THEME 3/7

--Функция    Описание    Пример    Результат
--ABS   Возвращает модуль числа    ABS(-14)    14
--CEILING   Возвращает число, округлённое до целого в большую сторону  CEILING(42.8)   43
--FLOOR Возвращает число, округлённое до целого в меньшую сторону  FLOOR(42.8) 42
--ROUND Округляет значение до ближайшего числа, округляет число до определённого количества знаков после запятой ROUND(42.4)
--ROUND(42.4382, 2) 42 42.44
--TRUNC Усекает значение до ближайшего числа, усекает число до указанного количества знаков после запятой, но число при этом не округляет  TRUNC(42.4) TRUNC(42.4382, 2)   42 42.43
-- POWER    Возвращает число, возведённое в степень, — нужную степень указывают вторым аргументом POWER(9, 3) 729
--SQRT  Извлекает квадратный корень из числа    SQRT(9) 3


--THEME 3/7 Lession 3

--1.
--Перед тем как применить агрегирующие функции, нужно получить срез данных. Выгрузите все поля из таблицы invoice, выберите записи о заказах, оформленных в сентябре. Информацию о дате заказа содержит поле invoice_date. Тип данных поля — varchar. Не забудьте про функции для работы с датой.

SELECT *
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS TIMESTAMP)) = 9;

--2.
--Дополните запрос. Оставьте в таблице поля с идентификатором покупателя (поле customer_id), датой заказа без времени (поле invoice_date) и суммой заказа (поле total). Выберите покупателей с идентификаторами 11, 13, 44, 36, 48, 52, 54, 56.

SELECT customer_id,
       CAST (invoice_date AS DATE),
       total
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS TIMESTAMP)) = 9
AND customer_id IN (11, 13, 44, 36, 48, 52, 54, 56);

--3.
--Дополните запрос. Найдите минимальное и максимальное значения поля total. Условия для среза остаются прежними.

SELECT MIN(total),
       MAX(total)-- добавьте расчёты с помощью агрегирующих функций
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS TIMESTAMP)) = 9
  AND customer_id IN (11, 13, 44, 36, 48, 52, 54, 56);

--4.
--Добавьте к минимуму и максимуму несколько новых полей:
--среднее значение поля total, округлённое до ближайшего числа;
--количество уникальных идентификаторов покупателей из поля customer_id;
--суммарная выручка.
--Не меняйте условия для среза данных.

SELECT MIN(total),
       MAX(total),
       ROUND(AVG(total)),
       COUNT(DISTINCT customer_id),
       SUM(total)-- добавьте нужные поля
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS TIMESTAMP)) = 9
  AND customer_id IN (11, 13, 44, 36, 48, 52, 54, 56);

--5.
--Посчитайте, сколько пропусков содержит поле fax из таблицы client.

SELECT COUNT(*)-COUNT(fax)
FROM client;


--6.
--Посчитайте среднюю стоимость заказов, оформленных в понедельник.

SELECT AVG(total)
FROM invoice
WHERE CAST(invoice_date AS TIMESTAMP) = DATE_TRUNC('week', CAST(invoice_date AS TIMESTAMP));

--THEME 3/7 Lession 4

-- нужно указать поля и необходимые вычисления
SELECT billing_city,
       ROUND(AVG(total))
FROM invoice -- не забыть таблицу
GROUP BY billing_city -- здесь указывают поле, по которому группируют данные
LIMIT 5;

--1.
--Группировка помогает сравнивать данные. Но сначала нужно получить срез. Напишите запрос, который выгрузит общую выручку (поле total) в США (англ. USA). Информацию о стране хранит поле billing_country.

SELECT SUM(total)-- добавьте агрегирующую функцию
FROM invoice
WHERE billing_country = 'USA'-- сюда напишите условие;

--2.
--Теперь можно проверить, как отличаются данные по городам. Посчитайте общую выручку, количество заказов, среднюю выручку для каждого города США. Нужное поле — billing_city.

SELECT billing_city,
       SUM(total),
       COUNT(invoice_id),
       AVG(total)
       -- добавьте агрегирующие функции
FROM invoice
WHERE billing_country = 'USA'
GROUP BY billing_city-- впишите категорию для группировки;


--3.
--Проверьте, какую выручку в среднем приносит каждый покупатель. Выгрузите общую сумму выручки, число уникальных покупателей (поле customer_id) и среднюю выручку на  уникального пользователя для страны США.

SELECT SUM(total),
       SUM(total)/COUNT(DISTINCT customer_id),
       COUNT(DISTINCT customer_id)-- добавьте агрегирующую функцию
FROM invoice
WHERE billing_country = 'USA';

--4.
--Дополните предыдущий запрос. Сгруппируйте данные по неделе заказа. Для этого нужно усечь дату из поля invoice_date, округлив её до первого дня недели. Обратите внимание, поле invoice_date хранит данные типа varchar.

SELECT DATE_TRUNC('week', CAST(invoice_date AS TIMESTAMP)),-- впишите поле с категориями
       SUM(total),
       COUNT(DISTINCT customer_id),
       SUM(total)/COUNT(DISTINCT customer_id)
FROM invoice
WHERE billing_country = 'USA'
GROUP BY DATE_TRUNC('week',CAST(invoice_date AS TIMESTAMP))-- сгруппируйте данные;

--5.
--Посчитайте, сколько пользователей с почтовыми доменами yahoo и gmail обслужил каждый сотрудник. В итоговой таблице должны быть два поля:
--идентификатор сотрудника,
--количество пользователей.

SELECT support_rep_id,
       COUNT(customer_id)

FROM client
WHERE email LIKE '%yahoo%'
    OR email LIKE '%gmail%'

GROUP BY support_rep_id


--6.
--Создайте новое поле с категориями:
--заказы на сумму меньше одного доллара получат категорию 'low cost';
--заказы на сумму от одного доллара и выше получат категорию 'high cost'.
--Для каждой категории посчитайте сумму значений в поле total, но только для тех заказов, при оформлении которых указан почтовый код. В итоговую таблицу должны войти только два поля.

SELECT CASE
       WHEN total < 1 THEN ('low cost')
       WHEN total >=1 THEN ('high cost')
       END,
SUM (total)
FROM invoice
WHERE billing_postal_code IS NOT NULL
GROUP BY
       CASE
       WHEN total < 1 THEN ('low cost')
       WHEN total >=1 THEN ('high cost')
       END

--THEME 3/7 Lession 6
--1.
--Отберите пять самых крупных заказов из таблицы invoice.

SELECT *
FROM invoice
ORDER BY total DESC-- впишите условие для сортировки
LIMIT 5 -- добавьте ограничение;

--2.
--Отберите пятерых самых активных клиентов в США с 25 мая 2011 по 25 сентября 2011. Дату хранит поле invoice_date, тип данных поля — varchar. Выведите два поля: идентификатор клиента и количество заказов. Расположите записи по убыванию количества заказов. Идентификаторы с одинаковым числом заказов должны быть отсортированы по возрастанию.

SELECT customer_id,
       COUNT(customer_id)
FROM invoice
WHERE billing_country = 'USA'
AND CAST(invoice_date AS DATE) BETWEEN '2011.05.25' AND '2011.09.25'-- сюда впишите условия
GROUP BY customer_id
ORDER BY COUNT(customer_id) DESC, customer_id
-- добавьте данные для сортировки
LIMIT 5;

--3.
--Нужно посмотреть продажи по годам. Выгрузите таблицу, в которую войдут:
--год покупки;
--минимальная сумма заказа;
--максимальная сумма заказа;
--общая сумма выручки;
--количество заказов;
--средняя выручка на уникального покупателя, округлённая до ближайшего целого числа.
--Отсортируйте таблицу по году от большего к меньшему. Отберите только те записи, в которых в поле billing_country указаны страны: США (англ. USA), Великобритания (англ. United Kingdom) и Германия (англ. Germany).

SELECT EXTRACT(YEAR FROM CAST(invoice_date AS TIMESTAMP)),
       MIN(total),
       MAX(total),
       SUM(total),
       COUNT(total),
       ROUND(SUM(total)/COUNT(DISTINCT customer_id))

FROM invoice
WHERE billing_country IN ('USA',
                         'United Kingdom',
                         'Germany')
GROUP BY EXTRACT(YEAR FROM CAST(invoice_date AS TIMESTAMP))
ORDER BY EXTRACT(YEAR FROM CAST(invoice_date AS TIMESTAMP)) DESC-- Попробуйте выполнить запрос самостоятельно, без подсказок

--THEME 3/7 Lession 9

SELECT rating,
       AVG(rental_rate)-- укажите нужные поля
FROM movie
GROUP BY rating-- сгруппируйте данные
HAVING AVG(rental_rate) > 3;-- пропишите условие;

--2.
--Изучите заказы, которые оформили в сентябре 2011 года. Сравните общую сумму выручки (поле total) за каждый день этого месяца: выведите день в формате '2011-09-01'  и сумму. Информацию о дате заказа хранит поле invoice_date. Не забудьте изменить тип данных в этом поле, чтобы использовать операторы для работы с датой. Оставьте в таблице только те значения суммы, которые больше 1 и меньше 10.

SELECT CAST(invoice_date AS DATE),
       SUM(total)-- укажите нужные поля
FROM invoice
WHERE CAST(invoice_date AS DATE) BETWEEN '2011-09-01' AND '2011-09-30'-- пропишите условие
GROUP BY CAST(invoice_date AS DATE)-- сгруппируйте данные
HAVING SUM(total) > 1 AND SUM(total) < 10-- добавьте условие;

--3.
--Посчитайте пропуски в поле с почтовым индексом billing_postal_code для каждой страны (поле billing_country). Получите срез: в таблицу должны войти только те записи, в которых поле billing_address не содержит слов Street, Way, Road или Drive. Отобразите в таблице страну и число пропусков, если их больше 10.

SELECT billing_country,
       COUNT(*)
FROM invoice
WHERE billing_postal_code IS NULL
  AND NOT (billing_address LIKE '%Street%'
   OR billing_address LIKE '%Way%'
   OR billing_address LIKE '%Road%'
   OR billing_address LIKE '%Drive%')
GROUP BY billing_country
HAVING COUNT(*) > 10;

----THEME 3/7 Lession 9

SELECT EXTRACT(YEAR FROM CAST(i.invoice_date AS DATE)) AS year_of_purchase,
       MIN(i.total) AS min_cost,
       MAX(i.total) AS max_cost,
       SUM(i.total) AS total_revenue,
       COUNT(i.total) AS total_purchases,
       ROUND(SUM(i.total)/COUNT(DISTINCT(i.customer_id))) AS average_receipt
FROM invoice AS i
WHERE billing_country IN ('USA', 'United Kingdom', 'Germany')
GROUP BY year_of_purchase
ORDER BY year_of_purchase DESC;

--2.
--Отберите фильмы, в описании которых встречается слово Epic. Выгрузите таблицу из трёх полей:
--rating_of_epic с рейтингом фильма;
--year_of_epic с годом выхода фильма;
--average_rental со средним сроком аренды.
--Сгруппируйте данные по рейтингу и году выхода фильма.

SELECT rating AS rating_of_epic,
       release_year AS year_of_epic,
       AVG(rental_duration) AS average_rental
FROM movie
WHERE description LIKE '%Epic%'
GROUP BY rating_of_epic, year_of_epic

--THEME 4/7 Lession 5

SELECT c.first_name,
       c.last_name,
       i.total
FROM invoice AS i
INNER JOIN client AS c ON i.customer_id = c.customer_id
LIMIT 10;


SELECT c.first_name,
       c.last_name,
       MIN(i.total) AS min_cost,
       MAX(i.total) AS max_cost,
       ROUND(AVG(i.total), 2) AS average_cost,
       COUNT(i.total) AS total_purchases
FROM invoice AS i
INNER JOIN client AS c ON i.customer_id = c.customer_id
WHERE i.billing_country = 'USA'
GROUP BY first_name, last_name
ORDER BY average_cost DESC
LIMIT 10;

--1.
--Нужно объединить данные двух таблиц: track и invoice_line. Таблица track хранит информацию о музыкальных треках в магазине, названия треков указаны в поле name. Таблица invoice_line содержит данные о купленных треках, их стоимость указана в поле unit_price. В обеих таблицах есть поле track_id — в нём содержатся идентификаторы музыкальных треков.
--Выгрузите таблицу с названием трека и числом, которое соответствует тому, сколько раз трек покупали. Учитывайте, что в одном заказе один и тот же трек может встречаться несколько раз. Если какой-либо из треков не покупали или о купленном треке нет информации — такие записи не должны войти в таблицу. Оставьте в итоговой таблице первые 20 записей.

SELECT t.name,
       COUNT(i.quantity)

FROM track AS t
INNER JOIN invoice_line AS i ON t.track_id = i.track_id

GROUP BY name
LIMIT 20

--2.
--Нужно дополнить запрос: добавьте поле с идентификатором плейлиста playlist_id. Такое поле можно получить из таблицы playlist_track. В этой таблице собраны идентификаторы плейлистов и треков (поле track_id). Условие остаётся прежним: если идентификаторы треков не совпадают во всех трёх таблицах, такие треки не должны войти в итоговую таблицу. Выведите первые 20 записей.

SELECT t.name,
       SUM(i.quantity),
       p.playlist_id-- укажите необходимое поле
FROM track AS t
INNER JOIN invoice_line AS i ON t.track_id=i.track_id
INNER JOIN playlist_track AS p ON t.track_id=p.track_id
GROUP BY t.name, p.playlist_id -- добавьте новое поле для группировки
LIMIT 20-- добавьте ещё одно присоединение;

--3.
--Идентификатор плейлиста теперь указан в итоговой таблице. Но что это за плейлисты — непонятно. Эту информацию можно взять в четвёртой таблице — playlist. Таблица содержит поле playlist_id с идентификатором плейлиста и поле name — с его названием. Добавьте в итоговую таблицу поле name. Условия те же: данные без совпадения не должны попасть в таблицу. Ограничьте вывод первыми 20 записями.

SELECT t.name,
       SUM(i.quantity),
       pt.playlist_id,
       pl.name-- укажите необходимое поле
FROM track AS t
INNER JOIN invoice_line AS i ON t.track_id=i.track_id
INNER JOIN playlist_track AS pt ON t.track_id=pt.track_id
INNER JOIN playlist AS pl ON pt.playlist_id=pl.playlist_id
GROUP BY t.name, pt.playlist_id, pl.name -- добавьте новое поле для группировки
LIMIT 20-- добавьте ещё одно присоединение

----THEME 4/7 Lession 6

--1.
--Выведите названия всех треков, добавив информацию о датах, в которые эти треки покупали. Ни один трек не должен потеряться, даже если его не покупали вообще. Чтобы получить нужный результат, надо соединить три таблицы сразу, ведь таблица invoice, которая хранит данные о дате заказа, не содержит информации о купленных треках.
--Сначала соедините таблицы track и invoice_line по ключу track_id, а затем присоедините таблицу invoice по ключу invoice_id. В итоговую таблицу поместите два поля: name из таблицы track и invoice_date из таблицы invoice. Приведите дату в нужный формат.

SELECT name,
       CAST(invoice_date AS DATE)-- поместите поле с датой
FROM track AS tr-- определите левую таблицу
LEFT OUTER JOIN invoice_line AS il ON il.track_id=tr.track_id
LEFT OUTER JOIN invoice AS i ON i.invoice_id=il.invoice_id-- определите вариант присоединения первой таблицы
-- определите вариант присоединения второй таблицы;


--2.
--Посчитайте для каждого года число уникальных названий купленных треков.

SELECT EXTRACT(YEAR FROM CAST(i.invoice_date AS DATE)) AS year_of_invoice,
       COUNT(DISTINCT t.name)-- посчитайте уникальное число треков
FROM track AS t
LEFT JOIN invoice_line AS il ON t.track_id = il.track_id
LEFT JOIN invoice AS i ON il.invoice_id = i.invoice_id
GROUP BY year_of_invoice-- добавьте данные для группировки;

--3.
--Выгрузите таблицу из двух полей: первое поле с фамилией сотрудника, второе — с количеством пользователей, чьи запросы этот сотрудник обработал. Назовите поля employee_last_name и all_customers соответственно. Сгруппируйте записи по идентификатору сотрудника. Отсортируйте количество пользователей по убыванию.

SELECT s.last_name AS employee_last_name,
       COUNT(support_rep_id) AS all_customers
FROM staff AS s
LEFT OUTER JOIN client AS c ON s. employee_id=c.support_rep_id
GROUP BY employee_id
ORDER BY all_customers DESC

--4.
--У некоторых сотрудников есть менеджеры — их идентификаторы указаны в поле reports_to. Посмотрите внимательно на схему базы: таблица staff отсылает сама к себе. Это нормально, можно не создавать новую таблицу с менеджерами.
--Теперь можно разобраться в иерархии команды. Отобразите таблицу с двумя полями: в первое поле войдут фамилии всех сотрудников, а во второе — фамилии их менеджеров. Назовите поля employee_last_name и manager_last_name.

SELECT s_1.last_name AS employee_last_name,
       s_2.last_name AS manager_last_name
FROM staff AS s_1
LEFT JOIN staff AS s_2 ON s_1.reports_to=s_2.employee_id
GROUP BY employee_last_name,manager_last_name

--5.
--Отобразите названия фильмов, в которых снимались актёры и актрисы, не указанные в базе.

SELECT m.title
FROM movie AS m
LEFT OUTER JOIN film_actor AS fa ON m.film_id=fa.film_id
LEFT OUTER JOIN actor AS a ON fa.actor_id=a.actor_id
WHERE a.actor_id IS NULL
GROUP BY m.title

--6.
--Отобразите на экране имена исполнителей, для которых в базе данных не нашлось ни одного музыкального альбома.

SELECT a.name
FROM artist AS a
LEFT OUTER JOIN album AS al ON a.artist_id=al.artist_id
WHERE al.album_id IS NULL
GROUP BY a.name

----THEME 5/7 Lession 1
--1.
--Найдите топ-40 самых длинных фильмов, аренда которых составляет больше 2 долларов. Выведите на экран название фильма (поле title), цену аренды (поле rental_rate), длительность фильма (поле length) и возрастной рейтинг (поле rating).

SELECT m.title,
       m.rental_rate,
       m.LENGTH,
       m.rating
FROM movie AS m
WHERE rental_rate > 2
ORDER BY LENGTH DESC
LIMIT 40

--2.
--Проанализируйте данные о возрастных рейтингах отобранных фильмов. Выгрузите в итоговую таблицу следующие поля:
возрастной рейтинг (поле rating);
--минимальное и максимальное значения длительности (поле length); назовите поля min_length и max_length соответственно;
--среднее значение длительности (поле length); назовите поле avg_length;
--минимум, максимум и среднее для цены просмотра (поле rental_rate); назовите поля min_rental_rate, max_rental_rate, avg_rental_rate соответственно.
--Отсортируйте среднюю длительность фильма по возрастанию.

SELECT top.rating,
       MIN(top.LENGTH) AS min_length,
       MAX(top.LENGTH) AS max_length,
       AVG(top.LENGTH) AS avg_length,
       MIN(top.rental_rate) AS min_rental_rate,
       MAX(top.rental_rate) AS max_rental_rate,
       AVG(top.rental_rate) AS avg_rental_rate
FROM
  (SELECT title,
          rental_rate,
          LENGTH,
          rating
   FROM movie
   WHERE rental_rate > 2
   ORDER BY LENGTH DESC
   LIMIT 40) AS top
GROUP BY top.rating
ORDER BY avg_length;

--3.
--Найдите средние значения полей, в которых указаны минимальная и максимальная длительность отобранных фильмов. Отобразите только два этих поля. Назовите их avg_min_length и avg_max_length соответственно.

SELECT
       AVG(top_top.min_length) AS avg_min_length,
       AVG(top_top.max_length) AS avg_max_length
FROM
(SELECT top.rating,
       MIN(top.LENGTH) AS min_length,
       MAX(top.LENGTH) AS max_length,
       AVG(top.LENGTH) AS avg_length,
       MIN(top.rental_rate) AS min_rental_rate,
       MAX(top.rental_rate) AS max_rental_rate,
       AVG(top.rental_rate) AS avg_rental_rate
FROM
  (SELECT title,
          rental_rate,
          LENGTH,
          rating
   FROM movie
   WHERE rental_rate > 2
   ORDER BY LENGTH DESC
   LIMIT 40) AS top
GROUP BY top.rating
ORDER BY avg_length) AS top_top
ORDER BY avg_min_length,avg_max_length;

--4.
--Отберите альбомы, названия которых содержат слово 'Rock' и его производные. В этих альбомах должно быть восемь или более треков. Выведите на экран одно число — среднее количество композиций в отобранных альбомах.

SELECT AVG(rock_top.cn)
FROM
(SELECT a.title,
       COUNT(t.name) AS cn
FROM album AS a
JOIN track AS t ON a.album_id=t.album_id
WHERE a.title LIKE '%Rock%'
GROUP BY a.title
HAVING COUNT(t.name) >=8) AS rock_top;

--5.
--Для каждой страны посчитайте среднюю стоимость заказов в 2009 году по месяцам. Отберите данные за 2, 5, 7 и 10 месяцы и сложите средние значения стоимости заказов. Выведите названия стран, у которых это число превышает 10 долларов.

SELECT best_table.country
FROM
(SELECT billing_country AS country,
        EXTRACT (MONTH FROM invoice_date::TIMESTAMP) AS month_cost,
        AVG(total) AS average_total
FROM invoice
WHERE EXTRACT (YEAR FROM invoice_date::TIMESTAMP) = '2009'
GROUP BY billing_country,
         month_cost
) AS best_table
WHERE best_table.month_cost IN (2, 5, 7, 10)
GROUP BY country
HAVING SUM(average_total)>10;

----THEME 5/7 Lession 2

--1.
--Вы уже сравнивали выручку в разных странах, но теперь можно усовершенствовать запросы.
--Напишите код для первого подзапроса. Таблица invoice_line хранит информацию о купленных треках. Выгрузите из неё только те заказы (поле invoice_id), которые включают больше пяти треков.

SELECT invoice_id
FROM invoice_line
GROUP BY invoice_id
HAVING COUNT(track_id) > 5;

--2.
--Теперь напишите код для второго подзапроса. С помощью той же таблицы найдите среднее значение цены одного трека (поле unit_price).

SELECT AVG(unit_price)
FROM invoice_line

--3.
--Для каждой страны (поле billing_country) посчитайте минимальное, максимальное и среднее значение выручки из поля total. Назовите поля так: min_total, max_total и avg_total. Нужные поля для выгрузки хранит таблица invoice.
--При подсчёте учитывайте только те заказы, которые включают более пяти треков. Стоимость заказа должна превышать среднюю цену одного трека. Используйте код, написанный в предыдущих заданиях.
--Отсортируйте итоговую таблицу по значению в поле avg_total от большего к меньшему.

SELECT billing_country,
       MIN(total) AS min_total,
       MAX(total) AS max_total,
       AVG(total) AS avg_total
FROM invoice
WHERE invoice_id IN (SELECT invoice_id
FROM invoice_line
GROUP BY invoice_id
HAVING COUNT(track_id) > 5)
AND total > (SELECT AVG(unit_price)
FROM invoice_line)
GROUP BY billing_country
ORDER BY avg_total DESC;

--4.
--Отберите десять самых коротких по продолжительности треков и выгрузите названия их жанров.

SELECT name
FROM genre
WHERE genre_id IN
          (SELECT genre_id
          FROM track
          GROUP BY genre_id
          ORDER BY MIN(milliseconds)
          LIMIT 5)
GROUP BY name

--5.
--Выгрузите уникальные названия городов, в которых стоимость заказов превышает среднее значение за 2009 год.

SELECT DISTINCT billing_city
FROM invoice
WHERE total >
(SELECT AVG(total)
FROM invoice
WHERE EXTRACT (YEAR FROM invoice_date::TIMESTAMP) = '2009');

--6.
--Найдите возрастной рейтинг с самыми дорогими для аренды фильмами. Для этого посчитайте среднюю стоимость аренды фильма каждого рейтинга. Выведите на экран названия категорий фильмов с этим рейтингом. Добавьте второе поле со средним значением продолжительности фильмов.

SELECT c.name,
       AVG(m.LENGTH)
FROM movie m
LEFT JOIN film_category fc ON m.film_id = fc.film_id
LEFT JOIN category c ON fc.category_id = c.category_id
WHERE m.rating IN (
            SELECT rating
            FROM movie
            GROUP BY rating
            ORDER BY AVG(rental_rate) DESC
            LIMIT 1)
GROUP BY c.name

----THEME 5/7 Lession 3
--1.
--Составьте сводную таблицу. Посчитайте заказы, оформленные за каждый месяц в течение нескольких лет: с 2011 по 2013 год. Итоговая таблица должна включать четыре поля: invoice_month, year_2011, year_2012, year_2013. Поле invoice_month должно хранить месяц в виде числа от 1 до 12.
--Если в какой-либо месяц заказы не оформляли, номер такого месяца всё равно должен попасть в таблицу.
--В этом задании не будет подсказок. Используйте любые методы, которые посчитаете нужными.

SELECT invoice_month,
       year_2011,
       year_2012,
       year_2013
FROM (SELECT invoice_month,
       year_2011,
       year_2012
FROM (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month,
       COUNT(*) AS year_2011
FROM invoice
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = '2011'
GROUP BY invoice_month) AS y11
JOIN (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month_2,
       COUNT(*) AS year_2012
FROM invoice
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = '2012'
GROUP BY invoice_month_2) AS y12 ON y11.invoice_month=y12.invoice_month_2) AS y11_12
JOIN (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month_3,
       COUNT(*) AS year_2013
FROM invoice
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = '2013'
GROUP BY invoice_month_3) AS y13 ON y11_12.invoice_month=y13.invoice_month_3

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


SELECT DISTINCT c.last_name
FROM invoice AS i
LEFT JOIN client AS c ON i.customer_id = c.customer_id
WHERE i.customer_id IN
    (SELECT customer_id
     FROM invoice
     WHERE CAST(invoice_date AS DATE) BETWEEN '2013-01-01' AND '2013-01-31' ) -- тут можно фильтровать по строкам вида '2013-01-01'
     AND CAST(invoice_date AS DATE) BETWEEN '2013-02-01' AND '2013-12-31';

--3.
--Сформируйте статистику по категориям фильмов. Отобразите в итоговой таблице два поля:
--название категории;
--число фильмов из этой категории.
--Фильмы для второго поля нужно отобрать по условию. Посчитайте фильмы только с теми актёрами и актрисами, которые больше семи раз снимались в фильмах, вышедших после 2013 года.
--Назовите поля name_category и total_films соответственно. Отсортируйте таблицу по количеству фильмов от большего к меньшему, а затем по полю с названием категории в лексикографическом порядке.

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

--4.
--Определите, летом какого года общая выручка в магазине была максимальной. Затем проанализируйте данные за этот год по странам. --Выгрузите таблицу с полями:
--country — название страны;
--total_invoice — число заказов, оформленных в этой стране в тот год, когда общая выручка за лето была максимальной;
--total_customer — число клиентов, зарегистрированных в этой стране.
--Отсортируйте таблицу по убыванию значений в поле total_invoice, а затем добавьте сортировку по названию страны в лексикографическом порядке.

SELECT i.billing_country AS country,
       COUNT(i.total) AS total_invoice,
       one.total_customer
FROM invoice AS i
LEFT JOIN (SELECT country,
           COUNT(customer_id) AS total_customer
           FROM client
           GROUP BY country) AS one ON i.billing_country=one.country
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) =
(SELECT EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) AS yy
FROM invoice AS i
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) > 5
  AND EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) < 9
GROUP BY yy
ORDER BY SUM(total) DESC
LIMIT 1)

GROUP BY i.billing_country, one.total_customer
ORDER BY total_invoice DESC,
         i.billing_country

--1.
--Перепишите один из своих прошлых запросов с использованием оператора WITH.
--Выведите топ-40 самых длинных фильмов, аренда которых составляет больше 2 долларов. Проанализируйте данные о возрастных рейтингах отобранных фильмов. Выгрузите в итоговую таблицу следующие поля:
--возрастной рейтинг (поле rating);
--минимальное и максимальное значения длительности (поле length), назовите поля min_length и max_length соответственно;
--среднее значение длительности (поле length), назовите поле avg_length;
--минимум, максимум и среднее для цены просмотра (поле rental_rate), назовите поля min_rental_rate, max_rental_rate, avg_rental_rate соответственно.
--Отсортируйте среднюю длительность фильма по возрастанию.

----THEME 5/7 Lession 4

WITH
i AS (
SELECT
    rating,
    LENGTH,
    rental_rate
FROM movie AS m
WHERE rental_rate > 2
ORDER BY LENGTH DESC
limit 40
),
c AS (
SELECT rating,
MIN(LENGTH) AS min_length ,
MAX(LENGTH) AS max_length ,
AVG(LENGTH) AS avg_length ,
MIN(rental_rate) AS min_rental_rate ,
MAX(rental_rate) AS max_rental_rate ,
AVG(rental_rate) AS avg_rental_rate
FROM i
GROUP BY rating
)
SELECT c.rating, c.min_length, c.max_length, c.avg_length, c.min_rental_rate, c.max_rental_rate, c.avg_rental_rate
FROM c
ORDER BY c.avg_length;


--3.
--Проанализируйте данные из таблицы invoice за 2012 и 2013 годы. В итоговую таблицу должны войти поля:
--month — номер месяца;
--sum_total_2012 — выручка за этот месяц в 2012 году;
--sum_total_2013 — выручка за этот месяц в 2013 году;
--perc — процент, который отображает, насколько изменилась месячная выручка в 2013 году по сравнению с 2012 годом.
--Округлите значение в поле perc до ближайшего целого числа. Отсортируйте таблицу по значению в поле month от меньшего к большему.

WITH
t_2012 AS (SELECT EXTRACT (MONTH FROM invoice_date::TIMESTAMP) AS MONTH,
                      SUM(total) AS sum_t_2012
               FROM invoice
               WHERE EXTRACT (YEAR FROM invoice_date::TIMESTAMP) = '2012'
               GROUP BY MONTH),

t_2013 AS (SELECT EXTRACT (MONTH FROM invoice_date::TIMESTAMP) AS MONTH,
                      SUM(total) AS sum_t_2013
               FROM invoice
               WHERE EXTRACT (YEAR FROM invoice_date::TIMESTAMP) = '2013'
               GROUP BY MONTH)

SELECT t_2012.MONTH,
       t_2012.sum_t_2012,
       t_2013.sum_t_2013,
       ROUND((t_2013.sum_t_2013 - t_2012.sum_t_2012)/t_2012.sum_t_2012*100) AS perc
FROM t_2012
JOIN t_2013 ON t_2012.MONTH=t_2013.MONTH
ORDER BY t_2012.MONTH;