Практика: общие табличные выражения

Напомним синтаксис оператора WITH:

SQL
WITH
-- назначение псевдонимов и формирование подзапросов
псевдоним_1 AS (подзапрос_1),
псевдоним_2 AS (подзапрос_2),
псевдоним_3 AS (подзапрос_3),
....
псевдоним_n AS (подзапрос_n)

SELECT -- основной запрос
-- внутри основного запроса работают с псевдонимами, которые назначили в WITH
FROM псевдоним_1 INNER JOIN псевдоним_2 ... 
...
... 

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

Задача 1 / 3

Перепишите один из своих прошлых запросов с использованием ключевого слова WITH.

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

возрастной рейтинг (поле rating);
минимальное и максимальное значения длительности (поле length), назовите поля min_length и max_length соответственно;
среднее значение длительности (поле length), назовите поле avg_length;
максимум, минимум и среднее для цены просмотра (поле rental_rate), назовите поля min_rental_rate, max_rental_rate, avg_rental_rate соответственно.

Отсортируйте среднюю длительность фильма по возрастанию.

Подсказка

Перепишите запрос, используя конструкцию с WITH.

Перепишите один из своих прошлых запросов, используя ключевое слово WITH.

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

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

Подсказка

Проанализируйте данные из таблицы invoice за 2012 и 2013 годы. В итоговую таблицу должны войти поля:

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

Округлите значение в поле perc до ближайшего целого числа. Отсортируйте таблицу по значению в поле month от меньшего к большему.

Подсказка

xxxxxxxxxx
 
WITH 
m AS (SELECT *
     FROM movie
     WHERE rental_rate > 2
     ORDER BY length DESC
     LIMIT 40)
​
SELECT
MIN(m.rental_rate),
MAX(m.rental_rate),
AVG(m.rental_rate),
MIN(m.length),
MAX(m.length),
AVG(m.length),
m.rating
FROM m
GROUP BY rating
ORDER BY AVG(m.length);

min	max	avg	min	max	avg	rating
2.99	4.99	4.19	179	185	181.8	PG
2.99	4.99	3.99	179	184	181.9	NC-17
2.99	4.99	4.41857	179	185	182.286	R
2.99	4.99	3.79	180	185	182.6	PG-13
2.99	4.99	3.99	179	185	182.625	G