--1
--Используя данные из таблицы user_actions, рассчитайте среднее число заказов всех пользователей нашего сервиса.
--Для этого сначала в подзапросе посчитайте, сколько заказов сделал каждый пользователь, а затем обратитесь к результату подзапроса в блоке FROM и уже в основном запросе усредните количество заказов по всем пользователям.
--Полученное среднее число заказов всех пользователей округлите до двух знаков после запятой.
--Колонку с этим значением назовите orders_avg.
SELECT round (avg (orders_count), 2) as orders_avg
FROM (SELECT user_id,
count (order_id) filter (WHERE action = 'create_order') as orders_count
FROM user_actions
GROUP BY user_id) as user_orders_count;
--2
--Повторите запрос из предыдущего задания, но теперь вместо подзапроса используйте оператор WITH и табличное выражение.
--Условия задачи те же: используя данные из таблицы user_actions, рассчитайте среднее число заказов всех пользователей.
--Полученное среднее число заказов округлите до двух знаков после запятой. Колонку с этим значением назовите orders_avg.
with t1 as (SELECT user_id,
count (order_id) filter (WHERE action = 'create_order') as orders_count
FROM user_actions
GROUP BY user_id)
SELECT round (avg (orders_count), 2) as orders_avg
FROM t1;
--3
--Выведите из таблицы products информацию о всех товарах кроме самого дешёвого.
--Результат отсортируйте по убыванию id товара.
SELECT product_id,
name,
price
FROM products
WHERE price != (SELECT min(price)
FROM products)
ORDER BY product_id desc;
--4
--Выведите информацию о товарах в таблице products, цена на которые превышает среднюю цену всех товаров на 20 рублей и более.
--Результат отсортируйте по убыванию id товара.
with price_avg as(SELECT round (avg(price), 2)
FROM products)
SELECT product_id,
name,
price
FROM products
WHERE price >= (SELECT *
FROM price_avg)+20
ORDER BY product_id desc;
--5
--Посчитайте количество уникальных клиентов в таблице user_actions, сделавших за последнюю неделю хотя бы один заказ.
--Полученную колонку с числом клиентов назовите users_count.
--В качестве текущей даты, от которой откладывать неделю, используйте последнюю дату в той же таблице user_actions.
with last_time as(SELECT max (time) - interval '1 week'
FROM user_actions)
SELECT count (distinct user_id) as users_count
FROM user_actions
WHERE time >= (SELECT *
FROM last_time) and action = 'create_order';
--Вариант верного решения:
SELECT count(distinct user_id) as users_count
FROM user_actions
WHERE action = 'create_order'
and time >= (SELECT max(time)
FROM user_actions) - interval '1 week'
--6
--С помощью функции AGE и агрегирующей функции снова определите возраст самого молодого курьера мужского пола в таблице couriers, но в этот раз при расчётах в качестве первой даты используйте последнюю дату из таблицы courier_actions.
--Чтобы получить именно дату, перед применением функции AGE переведите последнюю дату из таблицы courier_actions в формат DATE, как мы делали в этом задании.
--Возраст курьера измерьте количеством лет, месяцев и дней и переведите его в тип VARCHAR.
--Полученную колонку со значением возраста назовите min_age.
with last_date as (SELECT max (time)::date
FROM courier_actions)
SELECT min(age ((SELECT *
FROM last_date), birth_date)::varchar) as min_age
FROM couriers
WHERE sex = 'male'
--Вариант верного решения:
SELECT min(age((SELECT max(time)::date
FROM courier_actions), birth_date))::varchar as min_age
FROM couriers
WHERE sex = 'male'
--7
--Из таблицы user_actions с помощью подзапроса или табличного выражения отберите все заказы, которые не были отменены пользователями.
--Выведите колонку с id этих заказов. Результат запроса отсортируйте по возрастанию id заказа.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
with cancel_orders as (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
SELECT order_id
FROM user_actions
WHERE order_id not in (SELECT *
FROM cancel_orders)
ORDER BY order_id limit 1000;
--Вариант верного решения:
SELECT order_id
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
ORDER BY order_id limit 1000
--8
--Используя данные из таблицы user_actions, рассчитайте, сколько заказов сделал каждый пользователь и отразите это в столбце orders_count.
--В отдельном столбце orders_avg напротив каждого пользователя укажите среднее число заказов всех пользователей, округлив его до двух знаков после запятой.
--Также для каждого пользователя посчитайте отклонение числа заказов от среднего значения.
--Отклонение считайте так: число заказов «минус» округлённое среднее значение. Колонку с отклонением назовите orders_diff.
--Результат отсортируйте по возрастанию id пользователя.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
with t1 as (SELECT user_id,
count (distinct order_id) as orders_count
FROM user_actions
WHERE action = 'create_order'
GROUP BY user_id)
-- рассчитали, сколько заказов сделал каждый пользователь
SELECT user_id,
orders_count,
round((SELECT avg(orders_count)
FROM t1), 2) as orders_avg, orders_count - round((SELECT avg(orders_count)
FROM t1), 2) as orders_diff
FROM t1
ORDER BY user_id limit 1000;
--9
--Назначьте скидку 15% на товары, цена которых превышает среднюю цену на все товары на 50 и более рублей, а также скидку 10% на товары, цена которых ниже средней на 50 и более рублей. Цену остальных товаров внутри диапазона (среднее - 50; среднее + 50) оставьте без изменений. При расчёте средней цены, округлите её до двух знаков после запятой.
--Выведите информацию о всех товарах с указанием старой и новой цены. Колонку с новой ценой назовите new_price.
--Результат отсортируйте сначала по убыванию прежней цены в колонке price, затем по возрастанию id товара.
with t1 as (SELECT avg(price)
FROM products)
-- рассчитали среднюю цену всех товаров
SELECT product_id,
name,
price,
case when price >= (SELECT *
FROM t1)+50 then price*0.85 when price <= (SELECT *
FROM t1)-50 then price*0.9 else price end as new_price
FROM products
ORDER BY price desc, product_id;
--10
--Выясните, есть ли в таблице courier_actions такие заказы, которые были приняты курьерами, но не были созданы пользователями.
--Посчитайте количество таких заказов.
--Колонку с числом заказов назовите orders_count.
with t1 as (SELECT order_id
FROM user_actions
WHERE action = 'create_order')
SELECT count (order_id) as orders_count
FROM courier_actions
WHERE order_id not in (SELECT *
FROM t1);
--11
--Выясните, есть ли в таблице courier_actions такие заказы, которые были приняты курьерами, но не были доставлены пользователям. Посчитайте количество таких заказов.
--Колонку с числом заказов назовите orders_count.
SELECT count (order_id) as orders_count
FROM courier_actions
WHERE order_id not in (SELECT order_id
FROM courier_actions
WHERE action = 'deliver_order');
--12
--Определите количество отменённых заказов в таблице courier_actions и выясните, есть ли в этой таблице такие заказы, которые были отменены пользователями, но при этом всё равно были доставлены. Посчитайте количество таких заказов.
--Колонку с отменёнными заказами назовите orders_canceled.
--Колонку с отменёнными, но доставленными заказами назовите orders_canceled_and_delivered.
with t1 as (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
-- все id заказов, отмененных пользователями
SELECT count (distinct order_id) as orders_canceled,
count (order_id) filter (WHERE action = 'deliver_order') as orders_canceled_and_delivered
FROM courier_actions
WHERE order_id in (SELECT *
FROM t1);
--13
--По таблицам courier_actions и user_actions снова определите число недоставленных заказов и среди них посчитайте количество отменённых заказов и количество заказов, которые не были отменены (и соответственно, пока ещё не были доставлены).
--Колонку с недоставленными заказами назовите orders_undelivered, колонку с отменёнными заказами назовите orders_canceled, колонку с заказами «в пути» назовите orders_in_process.
SELECT count(distinct order_id) as orders_undelivered,
count(order_id) filter (WHERE action = 'cancel_order') as orders_canceled,
count(distinct order_id) - count(order_id) filter (WHERE action = 'cancel_order') as orders_in_process
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM courier_actions
WHERE action = 'deliver_order');
--14
--Отберите из таблицы users пользователей мужского пола, которые старше всех пользователей женского пола.
--Выведите две колонки: id пользователя и дату рождения. Результат отсортируйте по возрастанию id пользователя.
with last_date as (SELECT max(time)::date
FROM user_actions),
female_max_age as(SELECT max(age((SELECT *
FROM last_date), birth_date))
FROM users
WHERE sex = 'female')
SELECT user_id,
birth_date
FROM users
GROUP BY user_id having age((SELECT *
FROM last_date), birth_date) > (SELECT *
FROM female_max_age)
ORDER BY user_id;
--Вариант верного решения:
SELECT user_id,
birth_date
FROM users
WHERE sex = 'male'
and birth_date < (SELECT min(birth_date)
FROM users
WHERE sex = 'female')
ORDER BY user_id
--15
--Выведите id и содержимое 100 последних доставленных заказов из таблицы orders.
--Содержимым заказов считаются списки с id входящих в заказ товаров. Результат отсортируйте по возрастанию id заказа.
with t1 as (SELECT order_id
FROM courier_actions
WHERE action = 'deliver_order'
ORDER BY time desc limit 100)
SELECT order_id,
product_ids
FROM orders
WHERE order_id in (SELECT order_id
FROM t1)
ORDER BY order_id;
--16
--Из таблицы couriers выведите всю информацию о курьерах, которые в сентябре 2022 года доставили 30 и более заказов.
--Результат отсортируйте по возрастанию id курьера.
SELECT courier_id,
sex,
birth_date
FROM couriers
WHERE courier_id in (SELECT courier_id
FROM courier_actions
WHERE action = 'deliver_order'
and date_part ('year', time) = 2022
and date_part ('month', time) = 9
GROUP BY courier_id having count (order_id) >= 30)
ORDER BY courier_id;
--17
--Рассчитайте средний размер заказов, отменённых пользователями мужского пола.
--Средний размер заказа округлите до трёх знаков после запятой. Колонку со значением назовите avg_order_size.
SELECT round (avg(array_length(product_ids, 1)), 3) as avg_order_size
FROM orders
WHERE order_id in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order'
and user_id in (SELECT user_id
FROM users
WHERE sex = 'male'));
--18
--Посчитайте возраст каждого пользователя в таблице users.
--Возраст измерьте числом полных лет, как мы делали в прошлых уроках. Возраст считайте относительно последней даты в таблице user_actions.
--Для тех пользователей, у которых в таблице users не указана дата рождения, укажите среднее значение возраста всех остальных пользователей, округлённое до целого числа.
--Колонку с возрастом назовите age. В результат включите колонки с id пользователя и возрастом. Отсортируйте полученный результат по возрастанию id пользователя.
with last_date as(SELECT max(time)::date
FROM user_actions),
avg_age as(SELECT round (avg (date_part ('year', age((SELECT *
FROM last_date), birth_date)))::integer, 0) as avg_age
FROM users
WHERE birth_date is not null)
SELECT user_id,
coalesce(date_part ('year', age ((SELECT *
FROM last_date), birth_date)), (SELECT avg_age
FROM avg_age))::integer as age
FROM users
ORDER BY user_id;
--Вариант верного решения:
with users_age as (SELECT user_id,
date_part('year', age((SELECT max(time)
FROM user_actions), birth_date)) as age
FROM users)
SELECT user_id,
coalesce(age, (SELECT round(avg(age))
FROM users_age))::integer as age
FROM users_age
ORDER BY user_id;
--19
--Для каждого заказа, в котором больше 5 товаров, рассчитайте время, затраченное на его доставку.
--В результат включите id заказа, время принятия заказа курьером, время доставки заказа и время, затраченное на доставку. Новые колонки назовите соответственно time_accepted, time_delivered и delivery_time.
--В расчётах учитывайте только неотменённые заказы. Время, затраченное на доставку, выразите в минутах, округлив значения до целого числа. Результат отсортируйте по возрастанию id заказа.
with t1 as(SELECT order_id
FROM orders
WHERE array_length (product_ids, 1) > 5
and order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order'))
--неотмененные заказы, в которых больше 5 товаров
SELECT order_id,
min (time) as time_accepted,
max (time) as time_delivered,
extract(epoch
FROM (max(time) - min(time))/60)::integer as delivery_time
FROM courier_actions
WHERE order_id in (SELECT order_id
FROM t1)
GROUP BY order_id
ORDER BY order_id;
--20
--Для каждой даты в таблице user_actions посчитайте количество первых заказов, совершённых пользователями.
--Первыми заказами будем считать заказы, которые пользователи сделали в нашем сервисе впервые. В расчётах учитывайте только неотменённые заказы.
--В результат включите две колонки: дату и количество первых заказов в эту дату. Колонку с датами назовите date, а колонку с первыми заказами — first_orders.
--Результат отсортируйте по возрастанию даты.
with first_orders_by_users as (SELECT date(min(time)) as date,
user_id
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
GROUP BY user_id)
SELECT date,
count(user_id) as first_orders
FROM first_orders_by_users
GROUP BY date
ORDER BY date;
--21
--Выберите все колонки из таблицы orders и дополнительно в качестве последней колонки укажите функцию unnest, применённую к колонке product_ids. Эту последнюю колонку назовите product_id. Больше ничего с данными делать не нужно.
--Добавьте в запрос оператор LIMIT и выведите только первые 100 записей результирующей таблицы.
SELECT creation_time,
order_id,
product_ids,
unnest (product_ids) as product_id
FROM orders limit 100;
--22
--Используя функцию unnest, определите 10 самых популярных товаров в таблице orders.
--Самыми популярными товарами будем считать те, которые встречались в заказах чаще всего. Если товар встречается в одном заказе несколько раз (когда было куплено несколько единиц товара), это тоже учитывается при подсчёте. Учитывайте только неотменённые заказы.
--Выведите id товаров и то, сколько раз они встречались в заказах (то есть сколько раз были куплены). Новую колонку с количеством покупок товаров назовите times_purchased.
--Результат отсортируйте по возрастанию id товара.
with t1 as (SELECT unnest (product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')), t2 as (SELECT product_id,
count (product_id) as times_purchased
FROM t1
GROUP BY product_id
ORDER BY times_purchased desc limit 10)
SELECT product_id,
times_purchased
FROM t2
ORDER BY product_id;
--23
--Из таблицы orders выведите id и содержимое заказов, которые включают хотя бы один из пяти самых дорогих товаров, доступных в нашем сервисе.
--Результат отсортируйте по возрастанию id заказа.
with t1 as (SELECT product_id
FROM products
ORDER BY price desc limit 5),
--определили 5 самых дорогих товаровt2 as (SELECT order_id,
product_ids,
unnest (product_ids) as product_id
FROM orders)
-- развернули массив с id товарами
SELECT DISTINCT order_id,
product_ids
FROM t2
WHERE product_id in (SELECT product_id
FROM t1)
ORDER BY order_id;
--Вариант верного решения:
with top_products as (SELECT product_id
FROM products
ORDER BY price desc limit 5), unnest as (SELECT order_id,
product_ids,
unnest(product_ids) as product_id
FROM orders)
SELECT DISTINCT order_id,
product_ids
FROM unnest
WHERE product_id in (SELECT product_id
FROM top_products)
ORDER BY order_id
To embed this project on your website, copy the following code and paste it into your website's HTML: