--1
--Объедините таблицы user_actions и users по ключу user_id.
--В результат включите две колонки с user_id из обеих таблиц.
--ти две колонки назовите соответственно user_id_left и user_id_right.
--Также в результат включите колонки order_id, time, action, sex, birth_date.
--Отсортируйте получившуюся таблицу по возрастанию id пользователя (в любой из двух колонок с id).
SELECT user_actions.user_id as user_id_left,
users.user_id as user_id_right,
order_id,
time,
action,
sex,
birth_date
FROM user_actions join users
ON user_actions.user_id = users.user_id
ORDER BY user_id_left;
--2
--А теперь попробуйте немного переписать запрос из прошлого задания и посчитать количество уникальных id в объединённой таблице.
--То есть снова объедините таблицы, но в этот раз просто посчитайте уникальные user_id в одной из колонок с id.
--Выведите это количество в качестве результата.
--Колонку с посчитанным значением назовите users_count.
SELECT count (distinct user_id_left) as users_count
FROM (SELECT user_actions.user_id as user_id_left,
users.user_id as user_id_right,
order_id,
time,
action,
sex,
birth_date
FROM user_actions join users
ON user_actions.user_id = users.user_id) t1;
--Вариант верного решения:
SELECT count(distinct a.user_id) as users_count
FROM user_actions a join users b using (user_id)
--3
--С помощью LEFT JOIN объедините таблицы user_actions и users по ключу user_id.
--Обратите внимание на порядок таблиц — слева users_actions, справа users.
--В результат включите две колонки с user_id из обеих таблиц.
--Эти две колонки назовите соответственно user_id_left и user_id_right.
--Также в результат включите колонки order_id, time, action, sex, birth_date.
--Отсортируйте получившуюся таблицу по возрастанию id пользователя (в колонке из левой таблицы).
SELECT user_actions.user_id as user_id_left,
users.user_id as user_id_right,
order_id,
time,
action,
sex,
birth_date
FROM user_actions
LEFT JOIN users using (user_id)
ORDER BY user_id_left;
--4
--Теперь снова попробуйте немного переписать запрос из прошлого задания и посчитайте количество уникальных id в колонке user_id, пришедшей из левой таблицы user_actions.
--Выведите это количество в качестве результата. Колонку с посчитанным значением назовите users_count.
SELECT count (distinct user_id_left) as users_count
FROM (SELECT user_actions.user_id as user_id_left
FROM user_actions
LEFT JOIN users using (user_id)) t1;
--5
--Возьмите запрос из задания 3, где вы объединяли таблицы user_actions и users с помощью LEFT JOIN, добавьте к запросу оператор WHERE и исключите NULL значения в колонке user_id из правой таблицы.
--Включите в результат все те же колонки и отсортируйте получившуюся таблицу по возрастанию id пользователя в колонке из левой таблицы.
SELECT user_actions.user_id as user_id_left,
users.user_id as user_id_right,
order_id,
time,
action,
sex,
birth_date
FROM user_actions
LEFT JOIN users using (user_id)
WHERE users.user_id is not null
ORDER BY user_id_left;
--6
--С помощью FULL JOIN объедините по ключу birth_date таблицы, полученные в результате вышеуказанных запросов (то есть объедините друг с другом два подзапроса). Не нужно изменять их, просто добавьте нужный JOIN.
--В результат включите две колонки с birth_date из обеих таблиц.
--Эти две колонки назовите соответственно users_birth_date и couriers_birth_date.
--Также включите в результат колонки с числом пользователей и курьеров — users_count и couriers_count.
SELECT t1.birth_date as users_birth_date,
users_count,
t2.birth_date as couriers_birth_date,
couriers_count
FROM (SELECT birth_date,
count(user_id) as users_count
FROM users
WHERE birth_date is not null
GROUP BY birth_date) as t1 full join (SELECT birth_date,
count(courier_id) as couriers_count
FROM couriers
WHERE birth_date is not null
GROUP BY birth_date) as t2 using (birth_date)
ORDER BY users_birth_date, couriers_birth_date;
--7
--Объедините два следующих запроса друг с другом так, чтобы на выходе получился набор уникальных дат из таблиц users и couriers:
SELECT birth_date
FROM users
WHERE birth_date IS NOT NULL
SELECT birth_date
FROM couriers
WHERE birth_date IS NOT NULL
--Поместите в подзапрос полученный после объединения набор дат и посчитайте их количество. Колонку с числом дат назовите dates_count.
--Отсортируйте получившуюся таблицу сначала по колонке users_birth_date по возрастанию, затем по колонке couriers_birth_date — тоже по возрастанию.
SELECT count (birth_date) as dates_count
FROM (SELECT birth_date
FROM users
WHERE birth_date is not null
UNION
SELECT birth_date
FROM couriers
WHERE birth_date is not null) t
--8
--Из таблицы users отберите id первых 100 пользователей (просто выберите первые 100 записей, используя простой LIMIT) и с помощью CROSS JOIN объедините их со всеми наименованиями товаров из таблицы products.
--Выведите две колонки — id пользователя и наименование товара. Результат отсортируйте сначала по возрастанию id пользователя, затем по имени товара — тоже по возрастанию.
SELECT user_id,
name
FROM (SELECT user_id
FROM users limit 100) t1 cross join (SELECT name
FROM products) t2
ORDER BY user_id, name;
--9
--Для начала объедините таблицы user_actions и orders — это вы уже умеете делать. В качестве ключа используйте поле order_id. Выведите id пользователей и заказов, а также список товаров в заказе. Отсортируйте таблицу по id пользователя по возрастанию, затем по id заказа — тоже по возрастанию.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT user_id,
t1.order_id,
product_ids
FROM (SELECT order_id,
product_ids
FROM orders) t1
LEFT JOIN (SELECT user_id,
order_id
FROM user_actions) t2 using (order_id)
ORDER BY user_id, t1.order_id limit 1000;
--Вариант верного решения:
SELECT user_id,
order_id,
product_ids
FROM user_actions
LEFT JOIN orders using(order_id)
ORDER BY user_id, order_id limit 1000
--10
--Снова объедините таблицы user_actions и orders, но теперь оставьте только уникальные неотменённые заказы (мы делали похожий запрос на прошлом уроке). Остальные условия задачи те же: вывести id пользователей и заказов, а также список товаров в заказе. Отсортируйте таблицу по id пользователя по возрастанию, затем по id заказа — тоже по возрастанию.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT user_id,
order_id,
product_ids
FROM (SELECT DISTINCT user_id,
order_id
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t1
LEFT JOIN orders using(order_id)
ORDER BY user_id, order_id limit 1000;
--11
--Используя запрос из предыдущего задания, посчитайте, сколько в среднем товаров заказывает каждый пользователь.
--Выведите id пользователя и среднее количество товаров в заказе. Среднее значение округлите до двух знаков после запятой. Колонку посчитанными значениями назовите avg_order_size. Результат выполнения запроса отсортируйте по возрастанию id пользователя.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT user_id,
round (avg (array_length(product_ids, 1)), 2) as avg_order_size
FROM (SELECT user_id,
order_id
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t1
LEFT JOIN orders using (order_id)
GROUP BY user_id
ORDER BY user_id limit 1000;
--12
--Для начала к таблице с заказами (orders) примените функцию unnest, как мы делали в прошлом уроке. Колонку с id товаров назовите product_id. Затем к образовавшейся расширенной таблице по ключу product_id добавьте информацию о ценах на товары (из таблицы products). Должна получиться таблица с заказами, товарами внутри каждого заказа и ценами на эти товары. Выведите колонки с id заказа, id товара и ценой товара. Результат отсортируйте сначала по возрастанию id заказа, затем по возрастанию id товара.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT order_id,
product_id,
price
FROM (SELECT order_id,
unnest (product_ids) as product_id
FROM orders) t
LEFT JOIN products using (product_id)
ORDER BY order_id, product_id limit 1000;
--13
--Используя запрос из предыдущего задания, рассчитайте суммарную стоимость каждого заказа. Выведите колонки с id заказов и их стоимостью. Колонку со стоимостью заказа назовите order_price. Результат отсортируйте по возрастанию id заказа.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT order_id,
sum (price) as order_price
FROM (SELECT order_id,
unnest (product_ids) as product_id
FROM orders) t
LEFT JOIN products using (product_id)
GROUP BY order_id
ORDER BY order_id limit 1000;
--14
--Объедините запрос из предыдущего задания с частью запроса, который вы составили в задаче 11, то есть объедините запрос со стоимостью заказов с запросом, в котором вы считали размер каждого заказа из таблицы user_actions.
--На основе объединённой таблицы для каждого пользователя рассчитайте следующие показатели:
--общее число заказов — колонку назовите orders_count
--среднее количество товаров в заказе — avg_order_size
--суммарную стоимость всех покупок — sum_order_value
--среднюю стоимость заказа — avg_order_value
--минимальную стоимость заказа — min_order_value
--максимальную стоимость заказа — max_order_value
--Полученный результат отсортируйте по возрастанию id пользователя.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
--Помните, что в расчётах мы по-прежнему учитываем только неотменённые заказы.
--При расчёте средних значений, округляйте их до двух знаков после запятой.
SELECT user_id,
count (order_id) as orders_count,
round (avg (order_size), 2) as avg_order_size,
sum (order_price) as sum_order_value,
round (avg(order_price), 2) as avg_order_value,
min (order_price) as min_order_value,
max (order_price) as max_order_value
FROM (SELECT order_id,
sum (price) as order_price
FROM (SELECT order_id,
unnest (product_ids) as product_id
FROM orders) t1
LEFT JOIN products using (product_id)
GROUP BY order_id) t2
LEFT JOIN (SELECT user_id,
order_id,
array_length(product_ids, 1) as order_size
FROM (SELECT user_id,
order_id
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t3
LEFT JOIN orders using (order_id)) t4 using (order_id)
GROUP BY user_id
ORDER BY user_id limit 1000;
--15
--По данным таблиц orders, products и user_actions посчитайте ежедневную выручку сервиса. Под выручкой будем понимать стоимость всех реализованных товаров, содержащихся в заказах.
--Колонку с датой назовите date, а колонку со значением выручки — revenue.
--В расчётах учитывайте только неотменённые заказы.
--Результат отсортируйте по возрастанию даты.
SELECT date(creation_time) as date,
sum (price) as revenue
FROM (SELECT creation_time,
order_id,
unnest(product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t1
LEFT JOIN (SELECT product_id,
price
FROM products) t2 using (product_id)
GROUP BY date
ORDER BY date;
--16
--По таблицам courier_actions , orders и products определите 10 самых популярных товаров, доставленных в сентябре 2022 года.
--Самыми популярными товарами будем считать те, которые встречались в заказах чаще всего.
--Если товар встречается в одном заказе несколько раз (было куплено несколько единиц товара), то при подсчёте учитываем только одну единицу товара.
--Выведите наименования товаров и сколько раз они встречались в заказах.
--Новую колонку с количеством покупок товара назовите times_purchased.
with t1 as(SELECT order_id,
product_id,
count (distinct product_id) as count
FROM (SELECT order_id,
unnest (product_ids) as product_id
FROM orders
WHERE order_id in (SELECT order_id
FROM courier_actions
WHERE action = 'deliver_order'
and date_part ('year', time) = 2022
and date_part ('month', time) = 9))t
GROUP BY order_id, product_id), t2 as (SELECT product_id,
sum (count) as times_purchased
FROM t1
GROUP BY product_id)
SELECT name,
times_purchased::int
FROM t2
LEFT JOIN products using (product_id)
ORDER BY times_purchased desc limit 10;
--17
--Возьмите запрос, составленный на одном из прошлых уроков, и подтяните в него из таблицы users данные о поле пользователей таким образом, чтобы все пользователи из таблицы user_actions остались в результате.
--Затем посчитайте среднее значение cancel_rate для каждого пола, округлив его до трёх знаков после запятой.
--Колонку с посчитанным средним значением назовите avg_cancel_rate.
--Помните про отсутствие информации о поле некоторых пользователей после join, так как не все пользователи из таблицы user_action есть в таблице users.
--Для этой группы тоже посчитайте cancel_rate и в результирующей таблице для пустого значения в колонке с полом укажите ‘unknown’ (без кавычек).
--Возможно, для этого придётся вспомнить, как работает COALESCE.
--Результат отсортируйте по колонке с полом пользователя по возрастанию.
SELECT coalesce (sex,'unknown') as sex,
round (avg (cancel_rate), 3) as avg_cancel_rate
FROM (SELECT user_id,
count (distinct order_id) filter (WHERE action = 'cancel_order')/count (distinct order_id)::decimal as cancel_rate
FROM user_actions
GROUP BY user_id) t1
LEFT JOIN users using (user_id)
GROUP BY sex
ORDER BY sex;
--18
--По таблицам orders и courier_actions определите id десяти заказов, которые доставляли дольше всего.
SELECT order_id
FROM(SELECT order_id, creation_time, time, time-creation_time as delivery_time
FROM orders
LEFT JOIN courier_actions using (order_id)
WHERE action = 'deliver_order'
ORDER BY delivery_time desc limit 10) t;
--Вариант верного решения:
SELECT order_id
FROM (SELECT order_id,
time as delivery_time
FROM courier_actions
WHERE action = 'deliver_order') as t
LEFT JOIN orders using (order_id)
ORDER BY delivery_time - creation_time desc limit 10;
--19
--Произведите замену списков с id товаров из таблицы orders на списки с наименованиями товаров.
--Наименования возьмите из таблицы products. Колонку с новыми списками наименований назовите product_names.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT order_id,
array_agg (name) as product_names
FROM (SELECT order_id,
unnest (product_ids) as product_id
FROM orders) t1
LEFT JOIN products using (product_id)
GROUP BY order_id limit 1000
--20
--Выясните, кто заказывал и доставлял самые большие заказы. Самыми большими считайте заказы с наибольшим числом товаров.
--Выведите id заказа, id пользователя и id курьера. Также в отдельных колонках укажите возраст пользователя и возраст курьера.
--Возраст измерьте числом полных лет, как мы делали в прошлых уроках. Считайте его относительно последней даты в таблице user_actions — как для пользователей, так и для курьеров. Колонки с возрастом назовите user_age и courier_age.
--Результат отсортируйте по возрастанию id заказа.
SELECT DISTINCT order_id,
user_id,
courier_id,
date_part('year', age((SELECT max(time)
FROM user_actions), users.birth_date))::integer as user_age, date_part('year', age((SELECT max(time)
FROM user_actions), couriers.birth_date))::integer as courier_age
FROM (SELECT order_id
FROM orders
WHERE array_length(product_ids, 1) = (SELECT max(array_length(product_ids, 1))
FROM orders)
ORDER BY order_id) as t
LEFT JOIN user_actions using(order_id)
LEFT JOIN courier_actions using(order_id)
LEFT JOIN users using(user_id)
LEFT JOIN couriers using(courier_id);
--Вариант верного решения:
with order_id_large_size as (SELECT order_id
FROM orders
WHERE array_length(product_ids, 1) = (SELECT max(array_length(product_ids, 1))
FROM orders))
SELECT DISTINCT order_id,
user_id,
date_part('year', age((SELECT max(time)
FROM user_actions), users.birth_date))::integer as user_age, courier_id, date_part('year', age((SELECT max(time)
FROM user_actions), couriers.birth_date))::integer as courier_age
FROM (SELECT order_id,
user_id
FROM user_actions
WHERE order_id in (SELECT *
FROM order_id_large_size)) t1
LEFT JOIN (SELECT order_id,
courier_id
FROM courier_actions
WHERE order_id in (SELECT *
FROM order_id_large_size)) t2 using(order_id)
LEFT JOIN users using(user_id)
LEFT JOIN couriers using(courier_id)
ORDER BY order_id;
--21
--Выясните, какие пары товаров покупают вместе чаще всего.
--Пары товаров сформируйте на основе таблицы с заказами. Отменённые заказы не учитывайте.
--В качестве результата выведите две колонки — колонку с парами наименований товаров и колонку со значениями, показывающими, сколько раз конкретная пара встретилась в заказах пользователей. Колонки назовите соответственно pair и count_pair.
--Пары товаров должны быть представлены в виде списков из двух наименований.
--Пары товаров внутри списков должны быть отсортированы в порядке возрастания наименования.
--Результат отсортируйте сначала по убыванию частоты встречаемости пары товаров в заказах, затем по колонке pair — по возрастанию.
with order_products as (SELECT order_id,
product_id,
name
FROM (SELECT order_id,
unnest (product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t join products using (product_id))
-- развернули массив id товаров в неотмененных заказах
SELECT pair,
count (pair) as count_pair
FROM (SELECT DISTINCT a.order_id,
array_sort (array [a.name, b.name]) as pair
FROM order_products a join order_products b
ON a.order_id = b.order_id and
a.product_id < b.product_id) t
GROUP BY pair
ORDER BY count_pair desc, pair;
--Вариант верного решения:
with main_table as (SELECT DISTINCT order_id,
product_id,
name
FROM (SELECT order_id,
unnest(product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
LEFT JOIN products using(product_id)
ORDER BY order_id, name)
SELECT pair,
count(order_id) as count_pair
FROM (SELECT DISTINCT a.order_id,
case when a.name > b.name then string_to_array(concat(b.name, '+', a.name), '+')
else string_to_array(concat(a.name, '+', b.name), '+') end as pair
FROM main_table a join main_table b
ON a.order_id = b.order_id and
a.name != b.name) t
GROUP BY pair
ORDER BY count_pair desc, pair;
To embed this project on your website, copy the following code and paste it into your website's HTML: