--1
--Для каждого дня, представленного в таблицах user_actions и courier_actions, рассчитайте следующие показатели:
--Число новых пользователей.
--Число новых курьеров.
--Общее число пользователей на текущий день.
--Общее число курьеров на текущий день.
--Колонки с показателями назовите соответственно new_users, new_couriers, total_users, total_couriers. Колонку с датами назовите date.
--Проследите за тем, чтобы показатели были выражены целыми числами. Результат должен быть отсортирован по возрастанию даты.
--Поля в результирующей таблице: date, new_users, new_couriers, total_users, total_couriers
--Новыми будем считать тех пользователей и курьеров, которые в данный день совершили своё первое действие в нашем сервисе.
--Общее число пользователей/курьеров на текущий день — это результат сложения числа новых пользователей/курьеров
--в текущий день со значениями аналогичного показателя всех предыдущих дней.
--Чтобы посчитать количество новых пользователей/курьеров на каждую дату, сначала необходимо для каждого пользователя/курьера
--определить минимальную дату, то есть дату первого действия в нашем сервисе.
--Затем нужно произвести группировку и для каждой полученной даты посчитать число пользователей/курьеров с этой датой.
--Далее к полученной таблице достаточно применить оконную функцию и для каждой даты вычислить накопительную сумму числа пользователей/курьеров.


SELECT date, new_users, new_couriers,
      SUM (new_users) OVER (ORDER BY date)::int AS total_users,
      SUM (new_couriers) OVER (ORDER BY date)::int AS total_couriers
FROM
(SELECT date, COUNT (user_id) as new_users
 FROM(
      SELECT user_id, MIN(time)::DATE as date
      FROM user_actions
      GROUP BY user_id) t
 GROUP BY date
) AS users
FULL JOIN
(SELECT date, COUNT (courier_id) as new_couriers
 FROM(
      SELECT courier_id, MIN(time)::DATE as date
      FROM courier_actions 
      GROUP BY courier_id) t1
 GROUP BY date
) AS couriers
USING (date)
ORDER BY date;

--2
--Дополните запрос из предыдущего задания и теперь для каждого дня, представленного в таблицах user_actions и courier_actions, дополнительно рассчитайте следующие показатели:
--Прирост числа новых пользователей.
--Прирост числа новых курьеров.
--Прирост общего числа пользователей.
--Прирост общего числа курьеров.
--Показатели, рассчитанные на предыдущем шаге, также включите в результирующую таблицу.
--Колонки с новыми показателями назовите соответственно new_users_change, new_couriers_change, total_users_growth, total_couriers_growth.
--Колонку с датами назовите date.
--Все показатели прироста считайте в процентах относительно значений в предыдущий день.
--При расчёте показателей округляйте значения до двух знаков после запятой.
--Результирующая таблица должна быть отсортирована по возрастанию даты.

SELECT date, new_users, new_couriers, total_users, total_couriers,
      ROUND (100*new_users::DECIMAL/ LAG(new_users,1) OVER (ORDER BY date) - 100,2) AS new_users_change,
      ROUND (100*new_couriers::DECIMAL/ LAG(new_couriers,1) OVER (ORDER BY date) - 100,2) AS new_couriers_change,
      ROUND (100*total_users::DECIMAL/ LAG(total_users,1) OVER (ORDER BY date) - 100,2) AS total_users_growth,
      ROUND (100*total_couriers::DECIMAL/ LAG(total_couriers,1) OVER (ORDER BY date) - 100,2) AS total_couriers_growth
FROM(
SELECT date, new_users, new_couriers,
      SUM (new_users) OVER (ORDER BY date)::int AS total_users,
      SUM (new_couriers) OVER (ORDER BY date)::int AS total_couriers
FROM
(SELECT date, COUNT (user_id) as new_users
 FROM(
      SELECT user_id, MIN(time)::DATE as date
      FROM user_actions
      GROUP BY user_id) t
 GROUP BY date
) AS users
FULL JOIN
(SELECT date, COUNT (courier_id) as new_couriers
 FROM(
      SELECT courier_id, MIN(time)::DATE as date
      FROM courier_actions 
      GROUP BY courier_id) t1
 GROUP BY date
) AS couriers
USING (date)
) t2
ORDER BY date;

--3
--Для каждого дня, представленного в таблицах user_actions и courier_actions, рассчитайте следующие показатели:
--Число платящих пользователей.
--Число активных курьеров.
--Долю платящих пользователей в общем числе пользователей на текущий день.
--Долю активных курьеров в общем числе курьеров на текущий день.
--Колонки с показателями назовите соответственно paying_users, active_couriers, paying_users_share, active_couriers_share.
--Колонку с датами назовите date. Проследите за тем, чтобы абсолютные показатели были выражены целыми числами.
--Все показатели долей необходимо выразить в процентах. При их расчёте округляйте значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты. 

--Платящими будем считать тех пользователей, которые в данный день оформили хотя бы один заказ, который в дальнейшем не был отменен.
--Курьеров будем считать активными, если в данный день они приняли хотя бы один заказ, который был доставлен (возможно, уже на следующий день), или доставили любой заказ.
--Общее число пользователей/курьеров на текущий день — это по-прежнему результат сложения числа новых пользователей/курьеров в текущий день со значениями аналогичного показателя всех предыдущих дней.

SELECT date, paying_users, active_couriers,
       ROUND (100*paying_users/total_users::DECIMAL,2) AS paying_users_share,
       ROUND (100*active_couriers/total_couriers::DECIMAL,2) AS active_couriers_share
FROM(
     SELECT time::DATE AS date, COUNT (DISTINCT user_id) FILTER (WHERE action='create_order' AND order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')) AS paying_users
     FROM user_actions
     GROUP BY date) AS t1
FULL JOIN
    (SELECT time::DATE AS date, COUNT (DISTINCT courier_id) FILTER (WHERE action='accept_order' AND order_id IN (SELECT order_id FROM courier_actions WHERE action='deliver_order') OR action ='deliver_order') AS active_couriers
     FROM courier_actions
     GROUP BY date) AS t2
USING (date)
FULL JOIN
     (SELECT date, new_users, new_couriers,
             SUM (new_users) OVER (ORDER BY date)::int AS total_users,
             SUM (new_couriers) OVER (ORDER BY date)::int AS total_couriers
      FROM (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) AS t4
      FULL JOIN
           (SELECT date, COUNT (courier_id) as new_couriers
            FROM(
                 SELECT courier_id, MIN(time)::DATE as date
                 FROM courier_actions 
                 GROUP BY courier_id) t5
            GROUP BY date) AS t6
       USING (date)
      )t7
USING (date)
ORDER BY date;

--4
--Для каждого дня, представленного в таблице user_actions, рассчитайте следующие показатели:
--Долю пользователей, сделавших в этот день всего один заказ, в общем количестве платящих пользователей.
--Долю пользователей, сделавших в этот день несколько заказов, в общем количестве платящих пользователей.
--Колонки с показателями назовите соответственно single_order_users_share, several_orders_users_share. Колонку с датами назовите date.
--Все показатели с долями необходимо выразить в процентах.
--При расчёте долей округляйте значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты.
--Платящими по-прежнему считаем тех пользователей, которые в данный день оформили (и не отменили) хотя бы один заказ.

with 
paying_users AS(
SELECT time::DATE AS date, COUNT (DISTINCT user_id) AS paying_users
FROM user_actions
WHERE action='create_order' 
      AND order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
GROUP BY date
),

count_users AS(
SELECT date,
       COUNT (user_id) FILTER (WHERE count_orders=1) AS single_order_users,
       COUNT (user_id) FILTER (WHERE count_orders>1) AS several_orders_users
FROM(
     SELECT time::DATE AS date, user_id, COUNT (DISTINCT order_id) as count_orders
     FROM user_actions
     WHERE action='create_order' 
           AND order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
     GROUP BY date, user_id
) t
GROUP BY date
)

SELECT date,
       ROUND (100*single_order_users/paying_users::DECIMAL,2) AS single_order_users_share,
       ROUND (100*several_orders_users/paying_users::DECIMAL,2) AS several_orders_users_share,
       AVG(100*several_orders_users/paying_users::DECIMAL) OVER ()
FROM paying_users LEFT JOIN count_users USING (date)
ORDER BY date;

Вариант верного решения:

SELECT date,
       round(100 * single_order_users::decimal / paying_users,
             2) as single_order_users_share,
       100 - round(100 * single_order_users::decimal / paying_users,
                   2) as several_orders_users_share
FROM   (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) t1
    LEFT JOIN (SELECT date,
                      count(user_id) as single_order_users
               FROM   (SELECT time::date as date,
                              user_id,
                              count(distinct order_id) as user_orders
                       FROM   user_actions
                       WHERE  order_id not in (SELECT order_id
                                               FROM   user_actions
                                               WHERE  action = 'cancel_order')
                       GROUP BY date, user_id having count(distinct order_id) = 1) t2
               GROUP BY date) t3 using (date)
ORDER BY date;

--5
--Для каждого дня, представленного в таблице user_actions, рассчитайте следующие показатели:
--Общее число заказов.
--Число первых заказов (заказов, сделанных пользователями впервые).
--Число заказов новых пользователей (заказов, сделанных пользователями в тот же день, когда они впервые воспользовались сервисом).
--Долю первых заказов в общем числе заказов (долю п.2 в п.1).
--Долю заказов новых пользователей в общем числе заказов (долю п.3 в п.1).
--Колонки с показателями назовите соответственно orders, first_orders, new_users_orders, first_orders_share, new_users_orders_share. Колонку с датами назовите date.
--Проследите за тем, чтобы во всех случаях количество заказов было выражено целым числом. Все показатели с долями необходимо выразить в процентах.
--При расчёте долей округляйте значения до двух знаков после запятой.
--Результат должен быть отсортирован по возрастанию даты.
--При расчёте числа первых заказов учитывайте, что у каждого пользователя может быть всего один первый заказ (что вполне логично).
--При расчёте числа заказов новых пользователей учитывайте, что в свой первый день каждый новый пользователь мог как совершить сразу несколько заказов, так и не совершить ни одного.
--Во всех случаях при расчёте числа заказов учитывайте только фактически совершённые заказы, отменённые заказы не учитывайте.

with 
t1 AS (SELECT time::DATE AS date, COUNT (DISTINCT order_id)::int as orders
       FROM user_actions
       WHERE action='create_order'
            AND order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
       GROUP BY date
),
-- рассчитали общее число заказов

t2 AS (SELECT date, COUNT (user_id)::int as first_orders
       FROM(
            SELECT MIN(time)::DATE AS date, user_id
            FROM user_actions
            WHERE action='create_order'
                  AND order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
            GROUP BY user_id) t
       GROUP BY date
),
-- рассчитали число первых заказов (заказов, сделанных пользователями впервые)

t3 AS(
      SELECT t4.date, COALESCE(COUNT(order_id),0)::int AS new_users_orders
      FROM(
           SELECT MIN(time)::DATE as date, user_id
           FROM user_actions
           GROUP BY user_id) t4
           -- для каждого пользователя нашли дату совершения первого действия
      LEFT JOIN (
           SELECT time::DATE AS date, user_id, order_id
           FROM user_actions
           WHERE order_id NOT IN (select order_id from user_actions where action = 'cancel_order')) t5
           --таблица с общим числом заказов на каждую дату для каждого пользователя
      ON t4.date=t5.date AND t4.user_id=t5.user_id
      GROUP BY t4.date
)
-- рассчитали число новых пользователей (заказов, сделанных пользователями в тот же день, когда они впервые воспользовались сервисом)

SELECT date, orders, first_orders, new_users_orders,
       ROUND (100*first_orders/orders::DECIMAL,2) AS first_orders_share,
       ROUND (100*new_users_orders/orders::DECIMAL,2) AS new_users_orders_share
FROM t1
LEFT JOIN t2 USING (date)
LEFT JOIN t3 USING (date)
ORDER BY date;

--6
--На основе данных в таблицах user_actions, courier_actions и orders для каждого дня рассчитайте следующие показатели:
--Число платящих пользователей на одного активного курьера.
--Число заказов на одного активного курьера.
--Колонки с показателями назовите соответственно users_per_courier и orders_per_courier. Колонку с датами назовите date.
--При расчёте показателей округляйте значения до двух знаков после запятой.
--Результирующая таблица должна быть отсортирована по возрастанию даты.
--Платящими по-прежнему считаем тех пользователей, которые в данный день оформили хотя бы один заказ, который в дальнейшем не был отменен.
--Курьеров считаем активными, если в данный день они приняли хотя бы один заказ, который был доставлен (возможно, уже на следующий день), или доставили любой заказ.
--В расчётах учитывайте только неотменённые заказы. 

SELECT date,
       ROUND (paying_users::DECIMAL/active_couriers,2) AS users_per_courier,
       ROUND (orders::DECIMAL/active_couriers,2) AS orders_per_courier
FROM(
     SELECT time::DATE AS date, 
            COUNT (DISTINCT user_id) AS paying_users
     FROM user_actions
     WHERE action='create_order'
           AND order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
     GROUP BY date) AS t1
FULL JOIN
    (SELECT time::DATE AS date, 
            COUNT (DISTINCT courier_id) AS active_couriers
     FROM courier_actions
     WHERE action='accept_order'
           AND order_id IN (SELECT order_id FROM courier_actions WHERE action='deliver_order') 
           OR action ='deliver_order'
     GROUP BY date) AS t2
USING (date)
LEFT JOIN
    (SELECT creation_time::DATE AS date, COUNT (order_id) AS orders
     FROM orders
     WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action='cancel_order')
     GROUP BY date) AS t3
USING (date)
ORDER BY date;

--7
--На основе данных в таблице courier_actions для каждого дня рассчитайте, за сколько минут в среднем курьеры доставляли свои заказы.
--Колонку с показателем назовите  minutes_to_deliver. Колонку с датами назовите date.
--При расчёте среднего времени доставки округляйте количество минут до целых значений.
--Учитывайте только доставленные заказы, отменённые заказы не учитывайте.
--Результирующая таблица должна быть отсортирована по возрастанию даты.
--Некоторые заказы оформляют в один день, а доставляют уже на следующий.
--При расчёте среднего времени доставки в качестве дней, для которых считать среднее, используйте дни фактической доставки заказов.

SELECT date, AVG(minutes_to_deliver_order)::int AS minutes_to_deliver
FROM ( 
      SELECT order_id, MAX(time)::DATE AS date,
             EXTRACT(epoch FROM (MAX(time) - MIN(time))/60) minutes_to_deliver_order
      FROM courier_actions
      WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action = 'cancel_order')
      GROUP BY order_id) AS t  
GROUP BY date
ORDER BY date;

--8
--Оценим почасовую нагрузку на наш сервис, выясним, в какие часы пользователи оформляют больше всего заказов, 
--и заодно проанализируем, как изменяется доля отмен в зависимости от времени оформления заказа.
--На основе данных в таблице orders для каждого часа в сутках рассчитайте следующие показатели:
--Число успешных (доставленных) заказов.
--Число отменённых заказов.
--Долю отменённых заказов в общем числе заказов (cancel rate).
--Колонки с показателями назовите соответственно successful_orders, canceled_orders, cancel_rate. Колонку с часом оформления заказа назовите hour.
--При расчёте доли отменённых заказов округляйте значения до трёх знаков после запятой.
--Результирующая таблица должна быть отсортирована по возрастанию колонки с часом оформления заказа.

SELECT hour, successful_orders, canceled_orders,
       ROUND(canceled_orders::DECIMAL/orders,3) AS cancel_rate
FROM(
SELECT DATE_PART ('hour', creation_time)::int AS hour,
       COUNT (order_id) FILTER (WHERE order_id IN (SELECT order_id FROM courier_actions WHERE action='deliver_order')) AS successful_orders,
       COUNT (order_id) FILTER (WHERE order_id IN (SELECT order_id FROM user_actions WHERE action='cancel_order')) AS canceled_orders,
       COUNT (order_id) AS orders
FROM orders
GROUP BY hour) AS t
ORDER BY hour;