--6.1
SELECT sex,
       count(sex) as couriers_count
FROM   couriers
GROUP BY sex
ORDER BY couriers_count

--6.2
SELECT action,
       count(action) as orders_count
FROM   user_actions
GROUP BY action
ORDER BY count(action)

--6.3
    --Функция DATE_TRUNC используется для усечения дат и времени, 
    --т.е. она работает аналогично округлению ROUND, только для типов данных TIMESTAMP и INTERVAL
SELECT date_trunc('month', creation_time) as month,
       count(order_id) as orders_count
FROM   orders
GROUP BY month
ORDER BY month

--6.4
SELECT date_trunc('month', time) as month,
       action,
       count(action) as orders_count
FROM   user_actions
GROUP BY month, action
ORDER BY month, action

--6.5
--максимальный порядковый номер месяца среди всех порядковых номеров месяцев рождения
--пользователей сервиса. С помощью группировки 
--проведите расчёты отдельно в двух группах — для пользователей мужского и женского пола.
SELECT sex,
       max(date_part('month', birth_date))::integer as max_month
FROM   users
GROUP BY sex

--6.6
--порядковый номер месяца рождения самого молодого пользователя сервиса
SELECT sex,
       date_part('month', max(birth_date))::integer as max_month
FROM   users
GROUP BY sex

--6.7
--максимальный возраст пользователей мужского и женского пола в таблице users.
--Возраст измерьте числом полных лет
SELECT sex,
       max(date_part('year', age(current_date, birth_date))) :: integer as max_age
FROM   users
GROUP BY sex


--Вариант верного решения:
SELECT sex,
       date_part('year', max(age(birth_date)))::integer as max_age
FROM   users
GROUP BY sex
ORDER BY max_age

--6.8
--группы по возрасту, посчитайте количество пользователей каждого возраста
SELECT date_part('year', age(birth_date))::integer as age,
       count(user_id) as users_count
FROM   users
GROUP BY age
ORDER BY age

--6.9
--добавьте в группировку ещё и пол пользователя
--количество пользователей в каждой половозрастной группе
SELECT date_part('year', age(birth_date))::integer as age,
       sex,
       count(user_id) as users_count
FROM   users
WHERE  birth_date is not null
GROUP BY age, sex
ORDER BY age, sex

--6.10
--количество заказов в каждой группе за неделю с 29 августа по 4 сентября 2022 года включительно
SELECT array_length(product_ids, 1) as order_size,
       count(order_id) as orders_count
FROM   orders
WHERE  creation_time :: text between '2022-08-29'
   and '2022-09-05'
GROUP BY order_size
ORDER BY order_size


--Вариант верного решения:
SELECT array_length(product_ids, 1) as order_size,
       count(order_id) as orders_count
FROM   orders
WHERE  creation_time >= '2022-08-29'
   and creation_time < '2022-09-05'
GROUP BY order_size
ORDER BY order_size

--6.11
    --рассчитайте количество заказов в каждой группе. Учитывайте только заказы, оформленные по будням.
    --В результат включите только те размеры заказов, общее число которых превышает 2000.
SELECT array_length(product_ids, 1) as order_size,
       count(order_id) as orders_count
FROM   orders
WHERE  date_part('dow', creation_time) in (1, 2, 3, 4, 5)
GROUP BY 1 having count(order_id) > 2000
ORDER BY 1


--Вариант верного решения:
SELECT array_length(product_ids, 1) as order_size,
       count(order_id) as orders_count
FROM   orders
WHERE  to_char(creation_time, 'Dy') not in ('Sat', 'Sun')
GROUP BY order_size having count(order_id) > 2000
ORDER BY order_size

--6.12
--пять пользователей, сделавших в августе 2022 года наибольшее количество заказов
SELECT user_id as user_id,
       count(action) as created_orders
FROM   user_actions
WHERE  action = 'create_order'
   and date_part('month', time) = 8
GROUP BY user_id
ORDER BY created_orders desc, user_id limit 5


--Вариант верного решения:
SELECT user_id,
       count(distinct order_id) as created_orders
FROM   user_actions
WHERE  action = 'create_order'
   and date_part('month', time) = 8
   and date_part('year', time) = 2022
GROUP BY user_id
ORDER BY created_orders desc, user_id limit 5

--6.13
--определите курьеров, которые в сентябре 2022 года доставили только по одному заказу
SELECT courier_id
FROM   courier_actions
WHERE  action = 'deliver_order'
   and date_part('month', time) = 9
   and date_part('year', time) = 2022
GROUP BY courier_id having count(action) = 1
ORDER BY courier_id

--6.14
--пользователей, у которых последний заказ был создан до 8 сентября 2022 года
SELECT user_id
FROM   user_actions
WHERE  action = 'create_order'
GROUP BY user_id having max(time) < '2022-09-08'
ORDER BY user_id

--6.15
    --3 группы в зависимости от количества товаров
SELECT case when array_length(product_ids, 1) >= 1 and
                 array_length(product_ids, 1) <= 3 then 'Малый'
            when array_length(product_ids, 1) >= 4 and
                 array_length(product_ids, 1) <= 6 then 'Средний'
            else 'Большой' end as order_size,
       count(order_id) as orders_count
FROM   orders
GROUP BY order_size
ORDER BY count(order_id)


--Вариант верного решения:
SELECT case when array_length(product_ids, 1) >= 7 then 'Большой'
            when array_length(product_ids, 1) >= 4 then 'Средний'
            else 'Малый' end as order_size,
       count(order_id) as orders_count
FROM   orders
GROUP BY order_size
ORDER BY orders_count

--6.16
SELECT case when (date_part('year', age(birth_date)) :: integer) between '19' and
                 '24' then '19-24'
            when (date_part('year', age(birth_date)) :: integer) between '25' and
                 '29' then '25-29'
            when (date_part('year', age(birth_date)) :: integer) between '30' and
                 '35' then '30-35'
            when (date_part('year', age(birth_date)) :: integer) between '36' and
                 '41' then '36-41' end as group_age,
       count(user_id) as users_count
FROM   users
WHERE  birth_date is not null
GROUP BY group_age
ORDER BY group_age


--Вариант верного решения:
SELECT case when date_part('year', age(birth_date)) between 19 and
                 24 then '19-24'
            when date_part('year', age(birth_date)) between 25 and
                 29 then '25-29'
            when date_part('year', age(birth_date)) between 30 and
                 35 then '30-35'
            when date_part('year', age(birth_date)) between 36 and
                 41 then '36-41' end as group_age,
       count(user_id) as users_count
FROM   users
WHERE  birth_date is not null
GROUP BY group_age
ORDER BY group_age

--6.17
    --средний размер заказа по выходным и будням
SELECT case when date_part('isodow',
                           creation_time) in ('2', '3', '4', '5', '6') then 'weekdays'
            else 'weekend' end as week_part,
       round(avg(array_length(product_ids, 1)), 2) as avg_order_size
FROM   orders
GROUP BY week_part
ORDER BY avg_order_size


--Вариант верного решения:
SELECT case when to_char(creation_time, 'Dy') in ('Sat', 'Sun') then 'weekend'
            else 'weekdays' end as week_part,
       round(avg(array_length(product_ids, 1)), 2) as avg_order_size
FROM   orders
GROUP BY week_part
ORDER BY avg_order_size

--6.18
    --oбщее количество оформленных заказов и долю отменённых заказов
    --В результат включите только тех пользователей, которые оформили 
    --больше трёх заказов и у которых показатель cancel_rate составляет не менее 0.5
    
SELECT user_id,
       round(count(distinct order_id) filter 
    (WHERE action = 'cancel_order')::decimal / count(distinct order_id), 2) as cancel_rate,
       count(distinct order_id) as orders_count
FROM   user_actions
GROUP BY user_id having round(count(distinct order_id) filter (
WHERE  action = 'cancel_order')::decimal / count(distinct order_id), 2) >= 0.5
   and count(distinct order_id) > 3
ORDER BY user_id

--6.19
    --Общее количество оформленных заказов.
    --Общее количество отменённых заказов.
    --Общее количество неотменённых заказов (т.е. доставленных).
    --Долю неотменённых заказов в общем числе заказов (success rate).
  --Все расчёты проводите за период с 24 августа по 6 сентября 2022 года включительно
SELECT date_part('isodow', time)::int as weekday_number,
       to_char(time, 'Dy') as weekday,
       count(order_id) filter (WHERE action = 'create_order') as created_orders,
       count(order_id) filter (WHERE action = 'cancel_order') as canceled_orders,
       count(order_id) filter (WHERE action = 'create_order') - count(order_id) filter 
    (WHERE action = 'cancel_order') as actual_orders,
       round((count(order_id) filter (WHERE action = 'create_order') - count(order_id) filter 
    (WHERE action = 'cancel_order'))::decimal / count(order_id) filter (WHERE action = 'create_order'),
             3) as success_rate
FROM   user_actions
WHERE  time >= '2022-08-24'
   and time < '2022-09-07'
GROUP BY weekday_number, weekday
ORDER BY weekday_number