Домашняя работа
.docx
Часть 1. Stackoverflow
Сколько различных местоположений указано в профилях пользователей Stackoverflow?
-
select count(*) from (
select Location
from Users
group by Location
) as tmp
where Location is not null
222 074
У скольких пользователей в имени содержится подстрока “John” или “Ivan”?
-
select count(*)
from Users
where DisplayName like '%Ivan%' or DisplayName like '%John%'
89 125
Вывести (одним запросом) количество пользователей, задавших менее 10 вопросов, количество пользователей, задавших от 10 до 20 вопросов и количество пользователей, задавших более 20 вопросов.
-
select
count(case when tmp.c < 10 then 1 end),
count(case when tmp.c between 10 and 20 then 1 end),
count(case when tmp.c > 20 then 1 end)
from (
select count(tmp.Id) as c
from Users left join (select Id, OwnerUserId from Posts where PostTypeId = 1) as tmp
on Users.Id = tmp.OwnerUserId
group by Users.Id
) as tmp
12 953 196 | 241 723 | 161 678
Для каждого месяца, когда по сравнению с предыдущим месяцем постов (вопросов и ответов) стало меньше, вывести год, месяц и процент снижения.
-
select tmp.y, tmp.m, tmp.p
from (
select
year(CreationDate) as y,
month(CreationDate) as m,
count(*) * 100.0 / lag(count(*), 1) over (
order by year(CreationDate), month(CreationDate)
) as p
from Posts
where PostTypeId = 1 or PostTypeId = 2
group by year(CreationDate), month(CreationDate)
) as tmp
where tmp.p < 100
Результаты
Вывести количество пользователей, не задавших ни одного вопроса.
-
select count(*)
from Users left join (select OwnerUserId from Posts where PostTypeId = 1) tmp
on Users.Id = tmp.OwnerUserId
where tmp.OwnerUserId is null
9 355 502
Часть 2. Служба доставки пиццерии
База данных:
Таблица pizza:
id
name
price
1
Веганская
350
2
Пепперони
460
3
Гавайская
490
4
Деревенская
500
5
Славянская
490
Таблица client:
id
name
1
Арбузов
2
Березовский
3
Волков
4
Горбачев
Таблица orders:
id_order
dat_order
id_client
1
2013-09-20
1
2
2013-09-26
1
3
2013-09-27
2
4
2013-09-30
3
5
2013-10-01
4
Таблица order_content:
id_order |
id_pizza |
quantity |
1 |
1 |
1 |
1 |
4 |
2 |
2 |
2 |
3 |
3 |
5 |
2 |
3 |
4 |
1 |
3 |
1 |
1 |
4 |
1 |
3 |
Посчитать общую сумму всех заказов за 2014 год.
-
select coalesce(sum(quantity * price), 0) from orders join order_content on orders.id_order = order_content.id_order join pizza on order_content.id_pizza = pizza.id where extract(year from dat_order) = 2014
Вывести фамилии клиентов, купивших более 10 Деревенских пицц и ни одной Веганской.
-
select cname from ( select id, cname, sum(case when pname = 'Деревенская' then s else 0 end) as village, sum(case when pname = 'Веганская' then s else 0 end) as vegan from ( select client.id, client.name as cname, sum(quantity) as s, pizza.name as pname from client left join orders on client.id = orders.id_client left join order_content on orders.id_order = order_content.id_order left join pizza on order_content.id_pizza = pizza.id where pizza.name = 'Деревенская' or pizza.name = 'Веганская' group by pizza.name, client.id, client.name ) as tmp group by cname, id ) as tmp where village > 10 and vegan = 0 group by cname
Найти всех постоянных клиентов. Будем считать постоянными клиентами тех, кто совершил больше одного заказа, между заказами прошло не больше 30 дней и последний заказ был совершён не более 30 дней назад. (Т.е. если хоть раз между двумя любыми последовательными заказами клиента прошло больше 30 дней, то он уже не является постоянным.)
-
select id, name from ( select id, name, dat_order - lag(dat_order) over (partition by name) as dprev, current_date - last_value(dat_order) over (partition by name) as dnow from client join orders on client.id = orders.id_client order by dat_order ) as tmp group by id, name, dnow having max(dprev) <= 30 and dnow <= 30
Вывести среднюю стоимость заказа в выходные.
-
select avg(tmp.s) from ( select sum(quantity * price) as s from orders join order_content on orders.id_order = order_content.id_order join pizza on order_content.id_pizza = pizza.id where extract(dow from dat_order) = 6 or extract(dow from dat_order) = 0 group by orders.id_order ) as tmp