SQL_Begin

1
--=============== МОДУЛЬ 5. РАБОТА С POSTGRESQL =======================================
2
--= ПОМНИТЕ, ЧТО НЕОБХОДИМО УСТАНОВИТЬ ВЕРНОЕ СОЕДИНЕНИЕ И ВЫБРАТЬ СХЕМУ PUBLIC===========
3
SET search_path TO public;
4

5
--======== ОСНОВНАЯ ЧАСТЬ ==============
6

7
--ЗАДАНИЕ №1
8
--Сделайте запрос к таблице payment и с помощью оконных функций добавьте вычисляемые 
9
--колонки согласно условиям:
10
--Пронумеруйте все платежи от 1 до N по дате
11
--Пронумеруйте платежи для каждого покупателя, сортировка платежей должна быть по дате
12
--Посчитайте нарастающим итогом сумму всех платежей для каждого покупателя, сортировка должна 
13
--быть сперва по дате платежа, а затем по сумме платежа от наименьшей к большей
14
--Пронумеруйте платежи для каждого покупателя по стоимости платежа от наибольших к меньшим 
15
--так, чтобы платежи с одинаковым значением имели одинаковое значение номера.
16
--Можно составить на каждый пункт отдельный SQL-запрос, а можно объединить все колонки 
17
--в одном запросе.
18

19
select customer_id, payment_id, payment_date, 
20
	ROW_NUMBER() OVER (ORDER BY payment_date) as Column_1,
21
	ROW_NUMBER() OVER (PARTITION BY customer_id
22
		ORDER BY payment_date) as Column_2,		
23
    SUM(amount) OVER (PARTITION BY customer_id 
24
    	ORDER BY payment_date,amount) as Column_3,
25
	dense_rank() over (PARTITION BY customer_id ORDER BY amount desc ) as Column_4
26
from payment p
27
order by customer_id, Column_4
28

29

30
--ЗАДАНИЕ №2
31
--С помощью оконной функции выведите для каждого покупателя стоимость платежа и стоимость 
32
--платежа из предыдущей строки со значением по умолчанию 0.0 с сортировкой по дате.
33

34
-- В ответе Ориентировался на скриншот. 
35
-- Если оствить сортировку только по дате - значения иные будут.
36
select customer_id, payment_id, payment_date, 
37
	amount,
38
	LAG(amount, 1, 0.0) OVER () AS last_amount
39
from payment p
40
order by customer_id, payment_id, payment_date
41

42

43

44
--ЗАДАНИЕ №3
45
--С помощью оконной функции определите, на сколько каждый следующий платеж покупателя больше или меньше текущего.
46

47
select customer_id, payment_id, payment_date, 
48
	amount,
49
	amount - LEAD(amount, 1, 0.0) OVER () AS last_amount
50
from payment p
51
order by customer_id, payment_id, payment_date
52

53

54
--ЗАДАНИЕ №4
55
--С помощью оконной функции для каждого покупателя выведите данные о его последней оплате аренды.
56

57
select customer_id, payment_id, payment_date, amount
58
from payment
59
where payment_date in (SELECT MAX(payment_date) OVER (PARTITION BY customer_id ORDER BY payment_date desc ) FROM payment)
60
order by customer_id  limit 10
61

62

63
--======== ДОПОЛНИТЕЛЬНАЯ ЧАСТЬ ==============
64

65
--ЗАДАНИЕ №1
66
--С помощью оконной функции выведите для каждого сотрудника сумму продаж за август 2005 года 
67
--с нарастающим итогом по каждому сотруднику и по каждой дате продажи (без учёта времени) 
68
--с сортировкой по дате.
69

70
select staff_id, 
71
	payment_date,
72
	sum_amount,
73
	coalesce(SUM(sum_amount) OVER (PARTITION BY staff_id 
74
    	ORDER BY payment_date::DATE),0) as "sum"
75
from (select staff_id , 
76
	to_char(payment_date::DATE,'dd.mm.yyyy') AS "payment_date",
77
	SUM(amount) as sum_amount
78
from payment p 
79
group by staff_id, payment_date::DATE
80
having payment_date::DATE BETWEEN '01-08-2005 00:00:00' AND '31-08-2005 23:59:59' ) as tests
81
order by staff_id, payment_date
82

83

84
--ЗАДАНИЕ №2
85
--20 августа 2005 года в магазинах проходила акция: покупатель каждого сотого платежа получал
86
--дополнительную скидку на следующую аренду. С помощью оконной функции выведите всех покупателей,
87
--которые в день проведения акции получили скидку
88

89
select customer_id, payment_date, payment_number  
90
from (select
91
	customer_id,
92
	payment_date,
93
	ROW_NUMBER() OVER (ORDER BY payment_date) as payment_number
94
	from payment p 
95
	where payment_date::DATE = '2005-08-20'
96
	) as zapros
97
where to_char(payment_number, '000') LIKE '%00'
98
order by payment_number
99

100

101
--ЗАДАНИЕ №3
102
--Для каждой страны определите и выведите одним SQL-запросом покупателей,
103
-- которые попадают под условия:
104
-- 1. покупатель, арендовавший наибольшее количество фильмов
105
-- 2. покупатель, арендовавший фильмов на самую большую сумму
106
-- 3. покупатель, который последним арендовал фильм
107

108

109

110
-- Попытки в решение
111

112
select * from customer c 
113

114
select * from payment p 
115

116
select customer_id, count(payment_id),  sum(amount),   MAX(payment_date)  
117
from payment p
118
GROUP BY customer_id
119
order by customer_id desc limit 5
120

121
select * from country c 
122

123
select concat(first_name,' ',last_name) as FIO, c3.country  
124
from customer c 
125
join address a on c.address_id = a.address_id 
126
join city c2 on a.city_id  = c2.city_id 
127
join country c3 on c2.country_id = c3.country_id 
128
join payment p on c.customer_id = p.customer_id 
129
group by c3.country, concat(first_name,' ',last_name)
130
order by c3.country 
131

132

133
-- Основной работаюющий код
134
select concat(c3.first_name,' ', c3.last_name) as FIO , 
135
	c1.country, 
136
	count(p.customer_id),
137
	sum(p.amount),
138
	max(p.payment_date)
139
from country c1 
140
join city c2 on c1.country_id  = c2.country_id  
141
join address a on c2.city_id  = a.city_id  
142
join customer c3 on a.address_id  = c3.address_id  
143
join payment p on c3.customer_id = p.customer_id 
144
group by c1.country, FIO
145
order by c1.country 
146

147
-- Тесты (на работающий или пробные варианты решения задачи, которые было жалко удалять)
148
select c1.country, 
149
concat(c3.first_name,' ', c3.last_name) as FIO,
150
fio2
151
from (select concat(first_name,' ',last_name) as FIO2  
152
	from customer c 
153
		join address a on c.address_id = a.address_id 
154
		join city c2 on a.city_id  = c2.city_id 
155
		join country c3 on c2.country_id = c3.country_id 
156
		join payment p on c.customer_id = p.customer_id 
157
		group by c3.country, concat(first_name,' ',last_name)
158
		having count(p.customer_id) in (select count(p.customer_id)
159
		from country c1 
160
		join city c2 on c1.country_id  = c2.country_id  
161
		join address a on c2.city_id  = a.city_id  
162
		join customer c3 on a.address_id  = c3.address_id  
163
		join payment p on c3.customer_id = p.customer_id 
164
		group by c1.country)) ted1,
165
		country c1 
166
join city c2 on c1.country_id  = c2.country_id  
167
join address a on c2.city_id  = a.city_id  
168
join customer c3 on a.address_id  = c3.address_id  
169
join payment p on c3.customer_id = p.customer_id 
170
group by c1.country, FIO, ted1.fio2
171

172

173
select concat(c.first_name,' ',c.last_name) as FIO, c3.country  
174
from customer c 
175
join address a on c.address_id = a.address_id 
176
join city c2 on a.city_id  = c2.city_id 
177
join country c3 on c2.country_id = c3.country_id 
178
join payment p on c.customer_id = p.customer_id 
179
group by c3.country, concat(c.first_name,' ',c.last_name)
180
having count(DISTINCT p.customer_id) in (select count(DISTINCT p.customer_id)
181
from country c1 
182
join city c2 on c1.country_id  = c2.country_id  
183
join address a on c2.city_id  = a.city_id  
184
join customer c3 on a.address_id  = c3.address_id  
185
join payment p on c3.customer_id = p.customer_id 
186
group by c1.country)
187
order by c3.country  
188

189
select count(DISTINCT p.customer_id) as FIO, c3.country  
190
from customer c 
191
join address a on c.address_id = a.address_id 
192
join city c2 on a.city_id  = c2.city_id 
193
join country c3 on c2.country_id = c3.country_id 
194
join payment p on c.customer_id = p.customer_id 
195
group by c3.country, concat(c.first_name,' ',c.last_name)
196
having  in (select count(DISTINCT p.customer_id)
197
from country c1 
198
join city c2 on c1.country_id  = c2.country_id  
199
join address a on c2.city_id  = a.city_id  
200
join customer c3 on a.address_id  = c3.address_id  
201
join payment p on c3.customer_id = p.customer_id 
202
group by c1.country)
203
order by c3.country  
204

205
select  concat(c3.first_name,' ', c3.last_name) as FIO,
206
	c1.country, 
207
	count(p.customer_id),
208
	sum(p.amount),
209
	max(p.payment_date)
210
from country c1 
211
join city c2 on c1.country_id  = c2.country_id  
212
join address a on c2.city_id  = a.city_id  
213
join customer c3 on a.address_id  = c3.address_id  
214
join payment p on c3.customer_id = p.customer_id 
215
group by c1.country
216
order by c1.country 
217