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

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

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

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

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

Чтобы было удобнее решать задачи, скачайте PDF-версию ER-диаграммы с описанием таблиц. Или откройте диаграмму в соседней вкладке.

Задача 3 / 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.

Составьте сводную таблицу. Посчитайте заказы, оформленные за каждый месяц в течение нескольких лет: с 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 от меньшего к большему.

Подсказка

Создайте два общих табличных выражения для расчёта выручки за 2012 и 2013 годы. Объедините их в одну итоговую.

xxxxxxxxxx
 
WITH
year2012 AS (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS m,
            SUM(total) AS total
             FROM invoice
            WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2012
            GROUP BY EXTRACT(MONTH FROM CAST(invoice_date AS date))),
year2013 AS (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS m,
            SUM(total) AS total
            FROM invoice
            WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2013
            GROUP BY EXTRACT(MONTH FROM CAST(invoice_date AS date)))            
SELECT year2012.m,
SUM(year2012.total) AS sum_total_2012,
SUM(year2013.total) AS sum_total_2013,
ROUND((SUM(year2013.total) - SUM(year2012.total)) / SUM(year2012.total) * 100) AS perc
FROM year2012 JOIN year2013 ON year2012.m = year2013.m
GROUP BY year2012.m
ORDER BY year2012.m ASC

m	sum_total_2012	sum_total_2013	perc
1	37.62	37.62	0
2	37.62	27.72	-26
3	37.62	37.62	0
4	37.62	33.66	-11
5	37.62	37.62	0
6	37.62	37.62	0
7	39.62	37.62	-5
8	47.62	37.62	-21
9	46.71	37.62	-19
10	42.62	37.62	-12
11	37.62	49.62	32
12	37.62	38.62	3