--1
--Примените оконные функции к таблице products и с помощью ранжирующих функций упорядочьте все товары по цене — от самых дорогих к самым дешёвым. Добавьте в таблицу следующие колонки:
--Колонку product_number с порядковым номером товара (функция ROW_NUMBER).
--Колонку product_rank с рангом товара с пропусками рангов (функция RANK).
--Колонку product_dense_rank с рангом товара без пропусков рангов (функция DENSE_RANK).
--Не забывайте указывать в окне сортировку записей — без неё ранжирующие функции могут давать некорректный результат, если таблица заранее не отсортирована. Деление на партиции внутри окна сейчас не требуется.
--Сортировать записи в результирующей таблице тоже не нужно.
SELECT product_id,
name,
price,
row_number () OVER (ORDER BY price desc) as product_number,
rank () OVER (ORDER BY price desc) as product_rank,
dense_rank () OVER (ORDER BY price desc) as product_dense_rank
FROM products;
--2
--Примените оконную функцию к таблице products и с помощью агрегирующей функции в отдельной колонке для каждой записи проставьте цену самого дорогого товара. Колонку с этим значением назовите max_price.
--Затем для каждого товара посчитайте долю его цены в стоимости самого дорогого товара — просто поделите одну колонку на другую. Полученные доли округлите до двух знаков после запятой. Колонку с долями назовите share_of_max.
--Выведите всю информацию о товарах, включая значения в новых колонках.
--Результат отсортируйте сначала по убыванию цены товара, затем по возрастанию id товара.
SELECT product_id,
name,
price,
max (price) OVER () as max_price,
round (price / max (price) OVER (), 2) as share_of_max
FROM products
ORDER BY price desc, product_id;
--3
--Примените две оконные функции к таблице products.
--Одну с агрегирующей функцией MAX, а другую с агрегирующей функцией MIN — для вычисления максимальной и минимальной цены.
--Для двух окон задайте инструкцию ORDER BY по убыванию цены. Поместите результат вычислений в две колонки max_price и min_price.
--Выведите всю информацию о товарах, включая значения в новых колонках.
--Результат отсортируйте сначала по убыванию цены товара, затем по возрастанию id товара.
SELECT product_id,
name,
price,
max (price) OVER (ORDER BY price desc) as max_price,
min (price) OVER (ORDER BY price desc) as min_price
FROM products
ORDER BY price desc, product_id;
--4
--Сначала на основе таблицы orders сформируйте новую таблицу с общим числом заказов по дням. При подсчёте числа заказов не учитывайте отменённые заказы (их можно определить по таблице user_actions). Колонку с днями назовите date, а колонку с числом заказов — orders_count.
--Затем поместите полученную таблицу в подзапрос и примените к ней оконную функцию в паре с агрегирующей функцией SUM для расчёта накопительной суммы числа заказов. Не забудьте для окна задать инструкцию ORDER BY по дате.
--Колонку с накопительной суммой назовите orders_cum_count. В результате такой операции значение накопительной суммы для последнего дня должно получиться равным общему числу заказов за весь период.
--Сортировку результирующей таблицы делать не нужно.
SELECT date,
orders_count,
sum (orders_count) OVER (ORDER BY date)::int as orders_cum_count
FROM (SELECT creation_time::date as date,
count (order_id) OVER (PARTITION BY creation_time::date) as orders_count
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order'))t
GROUP BY date, orders_count;
--5
--Для каждого пользователя в таблице user_actions посчитайте порядковый номер каждого заказа.
--Для этого примените оконную функцию ROW_NUMBER к колонке с временем заказа. Не забудьте указать деление на партиции по пользователям и сортировку внутри партиций. Отменённые заказы не учитывайте.
--Новую колонку с порядковым номером заказа назовите order_number. Результат отсортируйте сначала по возрастанию id пользователя, затем по возрастанию порядкового номера заказа.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT user_id,
order_id,
time,
row_number () OVER (PARTITION BY user_id
ORDER BY time) as order_number
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
ORDER BY user_id, order_number limit 1000;
--6
--Дополните запрос из предыдущего задания и с помощью оконной функции для каждого заказа каждого пользователя рассчитайте, сколько времени прошло с момента предыдущего заказа.
--Для этого сначала в отдельном столбце с помощью LAG сделайте смещение по столбцу time на одно значение назад. Столбец со смещёнными значениями назовите time_lag. Затем отнимите от каждого значения в колонке time новое значение со смещением (либо можете использовать уже знакомую функцию AGE). Колонку с полученным интервалом назовите time_diff. Менять формат отображения значений не нужно, они должны иметь примерно следующий вид:
--3 days, 12:18:22
--По-прежнему не учитывайте отменённые заказы. Также оставьте в запросе порядковый номер каждого заказа, рассчитанный на прошлом шаге. Результат отсортируйте сначала по возрастанию id пользователя, затем по возрастанию порядкового номера заказа.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT user_id,
order_id,
time,
row_number () OVER (PARTITION BY user_id
ORDER BY time) as order_number,
lag(time, 1) OVER (PARTITION BY user_id
ORDER BY time) as time_lag,
age (time, lag(time, 1) OVER (PARTITION BY user_id
ORDER BY time)) as time_diff
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
ORDER BY user_id, order_number limit 1000;
--7
--На основе запроса из предыдущего задания для каждого пользователя рассчитайте, сколько в среднем времени проходит между его заказами. Посчитайте этот показатель только для тех пользователей, которые за всё время оформили более одного неотмененного заказа.
--Среднее время между заказами выразите в часах, округлив значения до целого числа. Колонку со средним значением времени назовите hours_between_orders. Результат отсортируйте по возрастанию id пользователя.
--Добавьте в запрос оператор LIMIT и включите в результат только первые 1000 записей.
SELECT user_id,
extract (epoch
FROM avg(time_diff)/3600)::int as hours_between_orders
FROM (SELECT user_id,
order_id,
time,
count (order_id) OVER (PARTITION BY user_id) as orders_count,
lag(time, 1) OVER (PARTITION BY user_id
ORDER BY time) as time_lag,
age (time, lag(time, 1) OVER (PARTITION BY user_id
ORDER BY time)) as time_diff
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order'))t
WHERE orders_count > 1
GROUP BY user_id
ORDER BY user_id limit 1000;
--Вариант верного решения:
SELECT user_id,
avg(time_diff)::integer as hours_between_orders
FROM (SELECT user_id,
order_id,
time,
extract(epoch
FROM (time - lag(time, 1)
OVER (
PARTITION BY user_id
ORDER BY time)))/3600 as time_diff
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
WHERE time_diff is not null
GROUP BY user_id
ORDER BY user_id limit 1000;
--8
--Сначала на основе таблицы orders сформируйте новую таблицу с общим числом заказов по дням. Вы уже делали это в одной из предыдущих задач. При подсчёте числа заказов не учитывайте отменённые заказы (их можно определить по таблице user_actions). Колонку с числом заказов назовите orders_count.
--Затем поместите полученную таблицу в подзапрос и примените к ней оконную функцию в паре с агрегирующей функцией AVG для расчёта скользящего среднего числа заказов. Скользящее среднее для каждой записи считайте по трём предыдущим дням. Подумайте, как правильно задать границы рамки, чтобы получить корректные расчёты.
--Полученные значения скользящего среднего округлите до двух знаков после запятой.
--Колонку с рассчитанным показателем назовите moving_avg. Сортировку результирующей таблицы делать не нужно.
SELECT date,
orders_count,
round (avg(orders_count) OVER (ORDER BY date rows between 3 preceding and 1 preceding),2) as moving_avg
FROM(SELECT creation_time::date as date, count (distinct order_id) as orders_count
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
GROUP BY date)t;
--9
--Отметьте в отдельной таблице тех курьеров, которые доставили в сентябре 2022 года заказов больше, чем в среднем все курьеры.
--Сначала для каждого курьера в таблице courier_actions рассчитайте общее количество доставленных в сентябре заказов.
--Затем в отдельном столбце с помощью оконной функции укажите, сколько в среднем заказов доставили в этом месяце все курьеры.
--После этого сравните число заказов, доставленных каждым курьером, со средним значением в новом столбце.
--Если курьер доставил больше заказов, чем в среднем все курьеры, то в отдельном столбце с помощью CASE укажите число 1, в противном случае укажите 0.
--Колонку с результатом сравнения назовите is_above_avg, колонку с числом доставленных заказов каждым курьером — delivered_orders, а колонку со средним значением — avg_delivered_orders.
--При расчёте среднего значения округлите его до двух знаков после запятой. Результат отсортируйте по возрастанию id курьера.
SELECT courier_id,
delivered_orders,
avg_delivered_orders,
case when delivered_orders > avg_delivered_orders then 1
else 0 end as is_above_avg
FROM (SELECT courier_id,
count (order_id) as delivered_orders,
round (avg (count (order_id)) OVER (), 2) as avg_delivered_orders
FROM courier_actions
WHERE date_part('month', time) = 9
and date_part('year', time) = 2022
and action = 'deliver_order'
GROUP BY courier_id) t
ORDER BY courier_id;
--10
--По данным таблицы user_actions посчитайте число первых и повторных заказов на каждую дату.
--Для этого сначала с помощью оконных функций и оператора CASE сформируйте таблицу, в которой напротив каждого заказа будет стоять отметка «Первый» или «Повторный» (без кавычек). Для каждого пользователя первым заказом будет тот, который был сделан раньше всего. Все остальные заказы должны попасть, соответственно, в категорию «Повторный». Затем на каждую дату посчитайте число заказов каждой категории.
--Колонку с типом заказа назовите order_type, колонку с датой — date, колонку с числом заказов — orders_count.
--В расчётах учитывайте только неотменённые заказы.
--Результат отсортируйте сначала по возрастанию даты, затем по возрастанию значений в колонке с типом заказа.
SELECT date,
order_type,
count (order_type) as orders_count FROM(SELECT time::date as date,
case when time = min (time) OVER (PARTITION BY user_id) then 'Первый'
else 'Повторный' end as order_type
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
GROUP BY date, order_type
ORDER BY date, order_type;
--Вариант верного решения:
SELECT time::date as date,
order_type,
count(order_id) as orders_count
FROM (SELECT user_id,
order_id,
time,
case when time = min(time) OVER (PARTITION BY user_id) then 'Первый'
else 'Повторный' end as order_type
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
GROUP BY date, order_type
ORDER BY date, order_type;
--11
--К запросу, полученному на предыдущем шаге, примените оконную функцию и для каждого дня посчитайте долю первых и повторных заказов. Сохраните структуру полученной ранее таблицы и добавьте только одну новую колонку с посчитанными значениями.
--Колонку с долей заказов каждой категории назовите orders_share. Значения в полученном столбце округлите до двух знаков после запятой. В результат также включите количество заказов в группах, посчитанное на предыдущем шаге.
--В расчётах по-прежнему учитывайте только неотменённые заказы.
--Результат отсортируйте сначала по возрастанию даты, затем по возрастанию значений в колонке с типом заказа.
SELECT date,
order_type,
count (order_id) as orders_count,
round(count (order_id)/ sum (count (order_id)) OVER (PARTITION BY date),
2) as orders_share FROM(SELECT time::date as date,
order_id,
case when time = min (time) OVER (PARTITION BY user_id) then 'Первый'
else 'Повторный' end as order_type
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
GROUP BY date, order_type
ORDER BY date, order_type;
--Вариант верного решения:
SELECT date,
order_type,
orders_count,
round(orders_count / sum(orders_count) OVER (PARTITION BY date),
2) as orders_share
FROM (SELECT time::date as date,
order_type,
count(order_id) as orders_count
FROM (SELECT user_id,
order_id,
time,
case when time = min(time) OVER (PARTITION BY user_id) then 'Первый'
else 'Повторный' end as order_type
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t
GROUP BY date, order_type) t
ORDER BY date, order_type;
--12
--Примените оконную функцию к таблице products и с помощью агрегирующей функции в отдельной колонке для каждой записи проставьте среднюю цену всех товаров. Колонку с этим значением назовите avg_price.
--Затем с помощью оконной функции и оператора FILTER в отдельной колонке рассчитайте среднюю цену товаров без учёта самого дорогого. Колонку с этим средним значением назовите avg_price_filtered. Полученные средние значения в колонках avg_price и avg_price_filtered округлите до двух знаков после запятой.
--Выведите всю информацию о товарах, включая значения в новых колонках.
--Результат отсортируйте сначала по убыванию цены товара, затем по возрастанию id товара.
SELECT product_id,
name,
price,
round (avg (price) OVER (), 2) as avg_price,
round (avg (price) filter (WHERE price < (SELECT max(price)
FROM products))
OVER (), 2) as avg_price_filtered
FROM products
ORDER BY price desc, product_id;
--13
--Для каждой записи в таблице user_actions с помощью оконных функций и предложения FILTER посчитайте, сколько заказов сделал и сколько отменил каждый пользователь на момент совершения нового действия.
--Иными словами, для каждого пользователя в каждый момент времени посчитайте две накопительные суммы — числа оформленных и числа отменённых заказов. Если пользователь оформляет заказ, то число оформленных им заказов увеличивайте на 1, если отменяет — увеличивайте на 1 количество отмен.
--Колонки с накопительными суммами числа оформленных и отменённых заказов назовите соответственно created_orders и canceled_orders. На основе этих двух колонок для каждой записи пользователя посчитайте показатель cancel_rate, т.е. долю отменённых заказов в общем количестве оформленных заказов. Значения показателя округлите до двух знаков после запятой. Колонку с ним назовите cancel_rate.
--В результате у вас должны получиться три новые колонки с динамическими показателями, которые изменяются во времени с каждым новым действием пользователя.
--В результирующей таблице отразите все колонки из исходной таблицы вместе с новыми колонками. Отсортируйте результат по колонкам user_id, order_id, time — по возрастанию значений в каждой.
--Добавьте в запрос оператор LIMIT и выведите только первые 1000 строк результирующей таблицы.
SELECT user_id,
order_id,
action,
time,
count (order_id) filter (WHERE action = 'create_order') OVER w as created_orders,
count (order_id) filter (WHERE action = 'cancel_order') OVER w as canceled_orders,
round (count (order_id) filter (WHERE action = 'cancel_order') OVER w / count (order_id) filter (WHERE action = 'create_order') OVER w::decimal, 2) as cancel_rate
FROM user_actions window w as (
PARTITION BY user_id
ORDER BY time)
ORDER BY user_id, order_id, time
limit 1000;
--Вариант верного решения:
SELECT user_id,
order_id,
action,
time,
created_orders,
canceled_orders,
round(canceled_orders::decimal / created_orders, 2) as cancel_rate
FROM (SELECT user_id,
order_id,
action,
time,
count(order_id) filter (WHERE action != 'cancel_order') OVER (PARTITION BY user_id
ORDER BY time) as created_orders,
count(order_id) filter (WHERE action = 'cancel_order') OVER (PARTITION BY user_id
ORDER BY time) as canceled_orders
FROM user_actions) t
ORDER BY user_id, order_id, time limit 1000;
--14
--Из таблицы courier_actions отберите топ 10% курьеров по количеству доставленных за всё время заказов.
--Выведите id курьеров, количество доставленных заказов и порядковый номер курьера в соответствии с числом доставленных заказов.
--У курьера, доставившего наибольшее число заказов, порядковый номер должен быть равен 1, а у курьера с наименьшим числом заказов — числу, равному десяти процентам от общего количества курьеров в таблице courier_actions.
--При расчёте номера последнего курьера округляйте значение до целого числа.
--Колонки с количеством доставленных заказов и порядковым номером назовите соответственно orders_count и courier_rank.
--Результат отсортируйте по возрастанию порядкового номера курьера.
SELECT courier_id,
orders_count,
dense_rank () OVER (ORDER BY orders_count desc, courier_id) as courier_rank
FROM(SELECT DISTINCT courier_id,
count(order_id) OVER (PARTITION BY courier_id) as orders_count
FROM courier_actions
WHERE action = 'deliver_order') t
ORDER BY courier_rank limit round((SELECT count(distinct courier_id)
FROM courier_actions)*0.1);
--Вариант верного решения:
with courier_count as (SELECT count(distinct courier_id)
FROM courier_actions)
SELECT courier_id,
orders_count,
courier_rank
FROM (SELECT courier_id,
count(distinct order_id) as orders_count,
row_number() OVER (ORDER BY count(distinct order_id) desc, courier_id) as courier_rank
FROM courier_actions
WHERE action = 'deliver_order'
GROUP BY courier_id) as t1
WHERE courier_rank <= round((SELECT *
FROM courier_count)*0.1);
--15
--С помощью оконной функции отберите из таблицы courier_actions всех курьеров, которые работают в нашей компании 10 и более дней. Также рассчитайте, сколько заказов они уже успели доставить за всё время работы.
--Будем считать, что наш сервис предлагает самые выгодные условия труда и поэтому за весь анализируемый период ни один курьер не уволился из компании. Возможные перерывы между сменами не учитывайте — для нас важна только разница во времени между первым действием курьера и текущей отметкой времени.
--Текущей отметкой времени, относительно которой необходимо рассчитывать продолжительность работы курьера, считайте время последнего действия в таблице courier_actions. Учитывайте только целые дни, прошедшие с момента первого выхода курьера на работу (часы и минуты не учитывайте).
--В результат включите три колонки: id курьера, продолжительность работы в днях и число доставленных заказов.
--Две новые колонки назовите соответственно days_employed и delivered_orders.
--Результат отсортируйте сначала по убыванию количества отработанных дней, затем по возрастанию id курьера.
SELECT courier_id,
date_part ('day', last_date - first_date)::int as days_employed,
delivered_orders FROM(SELECT courier_id,
count (order_id) filter (WHERE action = 'deliver_order') as delivered_orders,
min (time) as first_date,
(SELECT max(time)
FROM courier_actions) as last_date
FROM courier_actions
GROUP BY courier_id) t
WHERE date_part ('day', last_date - first_date) >= 10
ORDER BY days_employed desc, courier_id;
--Вариант верного решения:
SELECT courier_id,
days_employed,
delivered_orders
FROM (SELECT courier_id,
delivered_orders,
date_part('days', max(max_time) OVER() - min_time)::integer as days_employed
FROM (SELECT courier_id,
count(distinct order_id) filter (WHERE action = 'deliver_order') as delivered_orders,
min(time) as min_time,
max(time) as max_time
FROM courier_actions
GROUP BY courier_id) t1) t2
WHERE days_employed >= 10
ORDER BY days_employed desc, courier_id
--16
--На основе информации в таблицах orders и products рассчитайте стоимость каждого заказа, ежедневную выручку сервиса и долю стоимости каждого заказа в ежедневной выручке, выраженную в процентах.
--В результат включите следующие колонки: id заказа, время создания заказа, стоимость заказа, выручку за день, в который был совершён заказ, а также долю стоимости заказа в выручке за день, выраженную в процентах.
--При расчёте долей округляйте их до трёх знаков после запятой.
--Результат отсортируйте сначала по убыванию даты совершения заказа (именно даты, а не времени), потом по убыванию доли заказа в выручке за день, затем по возрастанию id заказа.
--При проведении расчётов отменённые заказы не учитывайте.
SELECT order_id,
creation_time,
order_price,
sum (order_price) OVER (PARTITION BY date_part ('days', creation_time)) as daily_revenue,
round (order_price / sum (order_price) OVER (PARTITION BY date_part ('days', creation_time))*100, 3) as percentage_of_daily_revenue
FROM (SELECT order_id,
creation_time,
sum (price) as order_price FROM(SELECT order_id,
creation_time,
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)
GROUP BY order_id, creation_time) t1
ORDER BY creation_time::date desc, percentage_of_daily_revenue desc, order_id;
--Вариант верного решения:
SELECT order_id,
creation_time,
order_price,
sum(order_price) OVER(PARTITION BY date(creation_time)) as daily_revenue,
round(100 * order_price::decimal / sum(order_price) OVER(PARTITION BY date(creation_time)),
3) as percentage_of_daily_revenue
FROM (SELECT order_id,
creation_time,
sum(price) as order_price
FROM (SELECT order_id,
creation_time,
product_ids,
unnest(product_ids) as product_id
FROM orders
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t3
LEFT JOIN products using(product_id)
GROUP BY order_id, creation_time) t
ORDER BY date(creation_time) desc, percentage_of_daily_revenue desc, order_id;
--17
--На основе информации в таблицах orders и products рассчитайте ежедневную выручку сервиса и отразите её в колонке daily_revenue.
--Затем с помощью оконных функций и функций смещения посчитайте ежедневный прирост выручки.
--Прирост выручки отразите как в абсолютных значениях, так и в % относительно предыдущего дня.
--Колонку с абсолютным приростом назовите revenue_growth_abs, а колонку с относительным — revenue_growth_percentage.
--Для самого первого дня укажите прирост равным 0 в обеих колонках. При проведении расчётов отменённые заказы не учитывайте.
--Результат отсортируйте по колонке с датами по возрастанию.
--Метрики daily_revenue, revenue_growth_abs, revenue_growth_percentage округлите до одного знака при помощи ROUND().
SELECT date,
sum (price) as daily_revenue,
coalesce (round (sum (price) - lag (sum (price), 1) OVER (ORDER BY date), 1), 0) as revenue_growth_abs,
coalesce (round (100*sum (price)::decimal / lag (sum (price), 1) OVER (ORDER BY date) - 100, 1), 0) as revenue_growth_percentage
FROM (SELECT order_id,
creation_time::date as date,
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 products using(product_id)
GROUP BY date
ORDER BY date;
--Вариант верного решения:
SELECT date,
round(daily_revenue, 1) as daily_revenue,
round(coalesce(daily_revenue - lag(daily_revenue, 1) OVER (ORDER BY date), 0),
1) as revenue_growth_abs,
round(coalesce(round((daily_revenue - lag(daily_revenue, 1) OVER (ORDER BY date))::decimal / lag(daily_revenue, 1) OVER (ORDER BY date) * 100, 2), 0),
1) as revenue_growth_percentage
FROM (SELECT date(creation_time) as date,
sum(price) as daily_revenue
FROM (SELECT order_id,
creation_time,
product_ids,
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 products using(product_id)
GROUP BY date) t2
ORDER BY date;
--18
--С помощью оконной функции рассчитайте медианную стоимость всех заказов из таблицы orders, оформленных в нашем сервисе.
--В качестве результата выведите одно число.
--Колонку с ним назовите median_price. Отменённые заказы не учитывайте.
--Запрос должен учитывать два возможных сценария: для чётного и нечётного числа заказов. Встроенные функции для расчёта квантилей применять нельзя.
WITH
main_table AS (SELECT order_id, SUM (price) AS order_price, ROW_NUMBER () OVER (ORDER BY SUM (price)) AS row_number
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')) t1
LEFT JOIN products using (product_id)
GROUP BY order_id
),
first_50 AS (SELECT order_price, row_number
FROM main_table
LIMIT (SELECT MAX(row_number)/2
FROM main_table)
),
last_50 AS (SELECT order_price, row_number
FROM main_table
OFFSET (SELECT MAX(row_number)/2
FROM main_table)
)
SELECT CASE
WHEN MOD(MAX (row_number), 2) = 0 THEN ((SELECT MAX(order_price) FROM first_50) + (SELECT MIN(order_price) FROM last_50))/2
ELSE (SELECT MAX(order_price) FROM main_table WHERE row_number = (SELECT MAX (row_number)/2+1 FROM main_table))
END AS median_price
FROM main_table;
--Вариант верного решения:
with
main_table AS (SELECT order_price,
ROW_NUMBER() OVER (ORDER BY order_price) AS row_number,
COUNT(*) OVER() AS total_rows
FROM (SELECT SUM(price) AS order_price
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')
) t3
LEFT JOIN products USING(product_id)
GROUP BY
order_id
) t1
)
SELECT AVG(order_price) AS median_price
FROM main_table
WHERE row_number BETWEEN total_rows / 2.0
AND total_rows / 2.0 + 1
To embed this project on your website, copy the following code and paste it into your website's HTML: