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

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

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

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

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

Задача 2 / 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
year_2011 AS (SELECT
     EXTRACT('MONTH' FROM CAST(invoice_date AS date)) AS invoice_month,
     COUNT(invoice_id) count2011
     FROM invoice
     WHERE EXTRACT('YEAR' FROM CAST(invoice_date AS date)) = '2011'
     GROUP BY 1),
year_2012 AS (SELECT
     EXTRACT('MONTH' FROM CAST(invoice_date AS date)) AS invoice_month1,
     COUNT(invoice_id) count2012
     FROM invoice
     WHERE EXTRACT('YEAR' FROM CAST(invoice_date AS date)) = '2012'
     GROUP BY 1),
year_2013 AS (SELECT
     EXTRACT('MONTH' FROM CAST(invoice_date AS date)) AS invoice_month2,
     COUNT(invoice_id) count2013
     FROM invoice
     WHERE EXTRACT('YEAR' FROM CAST(invoice_date AS date)) = '2013'
     GROUP BY 1)
​

d	count2011	count2012	count2013
1	7	7	7
2	7	7	5
3	7	7	7
4	7	7	5
5	7	7	7
6	7	7	7
7	7	7	7
8	7	7	7
9	7	6	7
10	7	7	7
11	6	7	7
12	7	7	7