-- 1
--Для каждого дня в таблице orders рассчитайте следующие показатели:
--Выручку, полученную в этот день.
--Суммарную выручку на текущий день.
--Прирост выручки, полученной в этот день, относительно значения выручки за предыдущий день.
--Колонки с показателями назовите соответственно revenue, total_revenue, revenue_change. Колонку с датами назовите date.
--Прирост выручки рассчитайте в процентах и округлите значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты.
--Будем считать, что оплата за заказ поступает сразу же после его оформления, т.е. случаи, когда заказ был оформлен в один день, а оплата получена на следующий, возникнуть не могут.
--Суммарная выручка на текущий день — это результат сложения выручки, полученной в текущий день, со значениями аналогичного показателя всех предыдущих дней.
--При расчёте выручки помните, что не все заказы были оплачены — некоторые были отменены пользователями.
--Не забывайте при делении заранее приводить значения к нужному типу данных.
--Пропущенные значения прироста для самой первой даты не заполняйте — просто оставьте поля в этой строке пустыми.
SELECT date, revenue,
SUM (revenue) OVER (ORDER BY date) AS total_revenue,
ROUND (100*revenue::DECIMAL/LAG (revenue,1) OVER () - 100,2) AS revenue_change
FROM(
SELECT date, SUM (price) AS revenue
FROM (
SELECT DATE(creation_time) AS date, 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')) AS t1
LEFT JOIN products USING (product_id)
GROUP BY date
) AS t
ORDER BY date;
-- 2
--Для каждого дня в таблицах orders и user_actions рассчитайте следующие показатели:
--Выручку на пользователя (ARPU) за текущий день.
--Выручку на платящего пользователя (ARPPU) за текущий день.
--Выручку с заказа, или средний чек (AOV) за текущий день.
--Колонки с показателями назовите соответственно arpu, arppu, aov. Колонку с датами назовите date.
--При расчёте всех показателей округляйте значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты.
with
revenue AS (
SELECT date, SUM(price) AS revenue,
COUNT (DISTINCT order_id) AS orders
FROM (
SELECT DATE (creation_time) AS date, 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)
GROUP BY date
),
users AS (
SELECT DATE(time) as date,
COUNT(DISTINCT user_id) FILTER (WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')) AS paying_users,
COUNT(DISTINCT user_id) AS users
FROM user_actions
GROUP BY date
)
SELECT date,
ROUND (revenue::DECIMAL/users, 2) AS arpu,
ROUND (revenue::DECIMAL/paying_users, 2) AS arppu,
ROUND (revenue::DECIMAL/orders, 2) AS aov
FROM revenue LEFT JOIN users USING (date)
ORDER BY date;
-- 3
--По таблицам orders и user_actions для каждого дня рассчитайте следующие показатели:
--Накопленную выручку на пользователя (Running ARPU).
--Накопленную выручку на платящего пользователя (Running ARPPU).
--Накопленную выручку с заказа, или средний чек (Running AOV).
--Колонки с показателями назовите соответственно running_arpu, running_arppu, running_aov. Колонку с датами назовите date.
--При расчёте всех показателей округляйте значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты.
--При расчёте числа пользователей и платящих пользователей на текущую дату учитывайте соответствующих пользователей за все предыдущие дни, включая текущий.
--При расчёте выручки помните, что не все заказы были оплачены — некоторые были отменены пользователями.
with
revenue AS (
SELECT date, SUM(price) AS revenue,
COUNT (DISTINCT order_id) AS orders
FROM (
SELECT DATE (creation_time) AS date, 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)
GROUP BY date
),
users AS (
SELECT date, SUM (new_users) OVER (ORDER BY date) AS running_users,
SUM (new_paying_users) OVER (ORDER BY date) AS running_paying_users
FROM (SELECT date, COUNT (DISTINCT user_id) AS new_paying_users
FROM (SELECT user_id, MIN(time)::DATE as date
FROM user_actions
WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
GROUP BY user_id) t1
GROUP BY date) t2
LEFT JOIN
(SELECT date, COUNT (user_id) as new_users
FROM (SELECT user_id, MIN(time)::DATE as date
FROM user_actions
GROUP BY user_id) t3
GROUP BY date) t4
USING (date)
)
SELECT date,
ROUND (SUM (revenue) OVER (ORDER BY date)::DECIMAL/running_users, 2) AS running_arpu,
ROUND (SUM (revenue) OVER (ORDER BY date)::DECIMAL/running_paying_users, 2) AS running_arppu,
ROUND (SUM (revenue) OVER (ORDER BY date)::DECIMAL/SUM (orders) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING), 2) AS running_aov
FROM revenue LEFT JOIN users USING (date)
ORDER BY date;
Вариант верного решения:
SELECT date,
round(sum(revenue) OVER (ORDER BY date)::decimal / sum(new_users) OVER (ORDER BY date),
2) as running_arpu,
round(sum(revenue) OVER (ORDER BY date)::decimal / sum(new_paying_users) OVER (ORDER BY date),
2) as running_arppu,
round(sum(revenue) OVER (ORDER BY date)::decimal / sum(orders) OVER (ORDER BY date),
2) as running_aov
FROM (SELECT creation_time::date as date,
count(distinct order_id) as orders,
sum(price) as revenue
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')) t1
LEFT JOIN products using(product_id)
GROUP BY date) t2
LEFT JOIN (SELECT time::date as date,
count(distinct user_id) as users
FROM user_actions
GROUP BY date) t3 using (date)
LEFT JOIN (SELECT time::date as date,
count(distinct user_id) as paying_users
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
GROUP BY date) t4 using (date)
LEFT JOIN (SELECT date,
count(user_id) as new_users
FROM (SELECT user_id,
min(time::date) as date
FROM user_actions
GROUP BY user_id) t5
GROUP BY date) t6 using (date)
LEFT JOIN (SELECT date,
count(user_id) as new_paying_users
FROM (SELECT user_id,
min(time::date) as date
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
GROUP BY user_id) t7
GROUP BY date) t8 using (date);
-- 4
--Для каждого дня недели в таблицах orders и user_actions рассчитайте следующие показатели:
--Выручку на пользователя (ARPU).
--Выручку на платящего пользователя (ARPPU).
--Выручку на заказ (AOV).
--При расчётах учитывайте данные только за период с 26 августа 2022 года по 8 сентября 2022 года включительно — так, чтобы в анализ попало
--одинаковое количество всех дней недели (ровно по два дня).
--В результирующую таблицу включите как наименования дней недели (например, Monday), так и порядковый номер дня недели
--(от 1 до 7, где 1 — это Monday, 7 — это Sunday).
--Колонки с показателями назовите соответственно arpu, arppu, aov.
--Колонку с наименованием дня недели назовите weekday, а колонку с порядковым номером дня недели weekday_number.
--При расчёте всех показателей округляйте значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию порядкового номера дня недели.
--В этой задаче порядковый номер дня недели необходим для того, чтобы дни недели были расположены на графике слева направо в правильном порядке — не по возрастанию наименования, а по возрастанию порядкового номера.
--Для получения корректной визуализации в настройках оси X необходимо отключить сортировку, установленную по умолчанию.
with
revenue AS(
SELECT weekday_number, weekday, SUM (price) AS revenue, COUNT(DISTINCT order_id) AS orders
FROM(
SELECT DATE_PART ('isodow', creation_time) AS weekday_number, TO_CHAR (creation_time, 'Day') AS weekday, 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')
AND creation_time BETWEEN '2022-08-26' AND '2022-09-08 23:59:59') as t1
LEFT JOIN products USING (product_id)
GROUP BY weekday_number, weekday
),
users AS(
SELECT DATE_PART ('isodow', time) AS weekday_number,
COUNT(DISTINCT user_id) FILTER (WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')) AS paying_users,
COUNT(DISTINCT user_id) AS users
FROM user_actions
WHERE time BETWEEN '2022-08-26' AND '2022-09-08 23:59:59'
GROUP BY weekday_number
)
SELECT weekday_number, weekday,
ROUND (revenue::DECIMAL/users, 2) AS arpu,
ROUND (revenue::DECIMAL/paying_users, 2) AS arppu,
ROUND (revenue::DECIMAL/orders, 2) AS aov
FROM revenue LEFT JOIN users USING (weekday_number)
ORDER BY weekday_number;
--Вариант верного решения:
SELECT weekday,
t1.weekday_number as weekday_number,
round(revenue::decimal / users, 2) as arpu,
round(revenue::decimal / paying_users, 2) as arppu,
round(revenue::decimal / orders, 2) as aov
FROM (SELECT to_char(creation_time, 'Day') as weekday,
max(date_part('isodow', creation_time)) as weekday_number,
count(distinct order_id) as orders,
sum(price) as revenue
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')
and creation_time >= '2022-08-26'
and creation_time < '2022-09-09') t4
LEFT JOIN products using(product_id)
GROUP BY weekday) t1
LEFT JOIN (SELECT to_char(time, 'Day') as weekday,
max(date_part('isodow', time)) as weekday_number,
count(distinct user_id) as users
FROM user_actions
WHERE time >= '2022-08-26'
and time < '2022-09-09'
GROUP BY weekday) t2 using (weekday)
LEFT JOIN (SELECT to_char(time, 'Day') as weekday,
max(date_part('isodow', time)) as weekday_number,
count(distinct user_id) as paying_users
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')
and time >= '2022-08-26'
and time < '2022-09-09'
GROUP BY weekday) t3 using (weekday)
ORDER BY weekday_number;
-- 5
--Для каждого дня в таблицах orders и user_actions рассчитайте следующие показатели:
--Выручку, полученную в этот день.
--Выручку с заказов новых пользователей, полученную в этот день.
--Долю выручки с заказов новых пользователей в общей выручке, полученной за этот день.
--Долю выручки с заказов остальных пользователей в общей выручке, полученной за этот день.
--Колонки с показателями назовите соответственно revenue, new_users_revenue, new_users_revenue_share, old_users_revenue_share.
--Колонку с датами назовите date.
--Все показатели долей необходимо выразить в процентах. При их расчёте округляйте значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты.
WITH
main_t AS(
SELECT date, user_id, order_id, order_price
FROM (SELECT DATE(time) as date, user_id, order_id
FROM user_actions
WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action ='cancel_order')) AS t1
LEFT JOIN
(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) AS t2
USING (order_id)
),
new_users_revenue AS(
SELECT t3.date as date, SUM (order_price) as new_users_revenue
FROM main_t
RIGHT JOIN
(SELECT DATE(MIN(time)) as date, user_id
FROM user_actions
GROUP BY user_id
ORDER BY user_id) t3
ON main_t.date = t3.date AND main_t.user_id = t3.user_id
GROUP BY t3.date
ORDER BY date
)
SELECT date, revenue, new_users_revenue,
ROUND (100*new_users_revenue/revenue::DECIMAL,2) AS new_users_revenue_share,
100 - ROUND (100*new_users_revenue/revenue::DECIMAL,2) AS old_users_revenue_share
FROM (SELECT date, SUM (order_price) AS revenue
FROM main_t
GROUP BY date) AS t4
LEFT JOIN new_users_revenue USING (date)
ORDER BY date;
--Вариант верного решения
SELECT date,
revenue,
new_users_revenue,
round(new_users_revenue / revenue * 100, 2) as new_users_revenue_share,
100 - round(new_users_revenue / revenue * 100, 2) as old_users_revenue_share
FROM (SELECT creation_time::date as date,
sum(price) as revenue
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')) t3
LEFT JOIN products using (product_id)
GROUP BY date) t1
LEFT JOIN (SELECT start_date as date,
sum(revenue) as new_users_revenue
FROM (SELECT t5.user_id,
t5.start_date,
coalesce(t6.revenue, 0) as revenue
FROM (SELECT user_id,
min(time::date) as start_date
FROM user_actions
GROUP BY user_id) t5
LEFT JOIN (SELECT user_id,
date,
sum(order_price) as revenue
FROM (SELECT user_id,
time::date as date,
order_id
FROM user_actions
WHERE order_id not in (SELECT order_id
FROM user_actions
WHERE action = 'cancel_order')) t7
LEFT JOIN (SELECT order_id,
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')) t9
LEFT JOIN products using (product_id)
GROUP BY order_id) t8 using (order_id)
GROUP BY user_id, date) t6
ON t5.user_id = t6.user_id and
t5.start_date = t6.date) t4
GROUP BY start_date) t2 using (date);
-- 6
--Для каждого товара, представленного в таблице products, за весь период времени в таблице orders рассчитайте следующие показатели:
--Суммарную выручку, полученную от продажи этого товара за весь период.
--Долю выручки от продажи этого товара в общей выручке, полученной за весь период.
--Колонки с показателями назовите соответственно revenue и share_in_revenue. Колонку с наименованиями товаров назовите product_name.
--Долю выручки с каждого товара необходимо выразить в процентах. При её расчёте округляйте значения до двух знаков после запятой.
--Товары, округлённая доля которых в выручке составляет менее 0.5%, объедините в общую группу с названием «ДРУГОЕ» (без кавычек),
--просуммировав округлённые доли этих товаров.
--Результат должен быть отсортирован по убыванию выручки от продажи товара.
--Товары с небольшой долей в выручке необходимо объединить в одну группу, чтобы не выводить на графике абсолютно все товары из таблицы products.
SELECT product_name,
SUM (revenue) as revenue,
SUM (share_in_revenue) AS share_in_revenue
FROM (
SELECT case
WHEN ROUND (100*revenue::DECIMAL/SUM (revenue) OVER (), 2) <0.5 THEN 'ДРУГОЕ'
ELSE name
END AS product_name,
revenue,
ROUND (100*revenue::DECIMAL/SUM (revenue) OVER (), 2) AS share_in_revenue
FROM (SELECT name, SUM(price) AS revenue
FROM (SELECT date, order_id, product_id, name, price
FROM (SELECT DATE(creation_time) as date, 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')) AS t1
LEFT JOIN products USING (product_id)) AS t2
GROUP BY name) AS t3
) AS t4
GROUP BY product_name
ORDER BY revenue desc;
-- 7
--Для каждого дня в таблицах orders и courier_actions рассчитайте следующие показатели:
--Выручку, полученную в этот день.
--Затраты, образовавшиеся в этот день.
--Сумму НДС с продажи товаров в этот день.
--Валовую прибыль в этот день (выручка за вычетом затрат и НДС).
--Суммарную выручку на текущий день.
--Суммарные затраты на текущий день.
--Суммарный НДС на текущий день.
--Суммарную валовую прибыль на текущий день.
--Долю валовой прибыли в выручке за этот день (долю п.4 в п.1).
--Долю суммарной валовой прибыли в суммарной выручке на текущий день (долю п.8 в п.5).
--Колонки с показателями назовите соответственно revenue, costs, tax, gross_profit, total_revenue, total_costs, total_tax, total_gross_profit, gross_profit_ratio, total_gross_profit_ratio
--Колонку с датами назовите date.
--Долю валовой прибыли в выручке необходимо выразить в процентах, округлив значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты.
--При расчёте переменных затрат учитывайте следующие условия:
--1. Затраты на сборку учитываются в том же дне, когда был оформлен заказ. Сборка отменённых заказов не производится.
--2. Выплата курьерам за доставленный заказ начисляется сразу же после его доставки, поэтому если курьер доставит заказ на следующий день, то и выплата будет учтена в следующем дне.
--3. Для получения бонусной выплаты курьерам необходимо доставить не менее 5 заказов в течение одного дня, поэтому если курьер примет 5 заказов в течение дня, но последний из них доставит после полуночи, бонусную выплату он не получит.
--При расчёте НДС учитывайте, что для некоторых товаров налог составляет 10%, а не 20%. Список товаров со сниженным НДС:
--Также при расчёте величины НДС по каждому товару округляйте значения до двух знаков после запятой.
with
costs AS (
SELECT date,
CASE
WHEN DATE_PART ('month', date)=8 AND DATE_PART ('year', date)=2022 THEN 120000 + SUM (order_assembly_costs) + SUM (delivery_order_costs) + SUM (delivery_orders_bonus)
ELSE 150000 + SUM (order_assembly_costs) + SUM (delivery_order_costs) + SUM (delivery_orders_bonus)
END AS costs
FROM(
SELECT date, courier_id, delivery_orders, accepted_orders,
CASE
WHEN DATE_PART ('month', date)=8 AND DATE_PART ('year', date)=2022 THEN accepted_orders*140
ELSE accepted_orders*115
END AS order_assembly_costs,
delivery_orders*150 AS delivery_order_costs,
CASE
WHEN DATE_PART ('month', date)=8 AND DATE_PART ('year', date)=2022 AND delivery_orders>=5 THEN 400
WHEN DATE_PART ('month', date)=9 AND DATE_PART ('year', date)=2022 AND delivery_orders>=5 THEN 500
ELSE 0
END AS delivery_orders_bonus
FROM(SELECT DATE(time) as date, courier_id,
COUNT (order_id) FILTER (WHERE action = 'deliver_order') as delivery_orders,
COUNT (order_id) FILTER (WHERE action = 'accept_order') as accepted_orders
FROM courier_actions
WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
GROUP BY date, courier_id) t
) t1
GROUP BY date
),
revenue AS (
SELECT date, SUM (price) AS revenue, SUM(product_tax) as tax
FROM (SELECT date, order_id, name, price,
CASE
WHEN name IN ('сахар', 'сухарики', 'сушки', 'семечки', 'масло льняное', 'виноград', 'масло оливковое', 'арбуз', 'батон', 'йогурт', 'сливки', 'гречка', 'овсянка', 'макароны', 'баранина', 'апельсины', 'бублики', 'хлеб', 'горох', 'сметана', 'рыба копченая', 'мука', 'шпроты', 'сосиски', 'свинина', 'рис', 'масло кунжутное', 'сгущенка', 'ананас', 'говядина', 'соль', 'рыба вяленая', 'масло подсолнечное', 'яблоки', 'груши', 'лепешка', 'молоко', 'курица', 'лаваш', 'вафли', 'мандарины')
THEN ROUND (price*10/110, 2)
ELSE ROUND (price*20/120, 2)
END as product_tax
FROM (SELECT DATE(creation_time) as date, 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)
) t
GROUP BY date
)
SELECT date, revenue, costs, tax,
revenue - costs - tax AS gross_profit,
SUM (revenue) OVER (ORDER BY date) AS total_revenue,
SUM (costs) OVER (ORDER BY date) AS total_costs,
SUM (tax) OVER (ORDER BY date) AS total_tax,
SUM (revenue - costs - tax) OVER (ORDER BY date) AS total_gross_profit,
ROUND (100*(revenue - costs - tax)/revenue::DECIMAL, 2) AS gross_profit_ratio,
ROUND (100*SUM (revenue - costs - tax) OVER (ORDER BY date)/SUM (revenue) OVER (ORDER BY date)::DECIMAL, 2) AS total_gross_profit_ratio
FROM revenue JOIN costs USING (date)
ORDER BY date;
To embed this program on your website, copy the following code and paste it into your website's HTML: