-- 1. Выбрать для каждого клиента количество заказов ценой меньше 1000 и больше 1000. (client_id, count1, count2)
select cl.name,
       (select count(*) from "order" where "order".price < 1000 and cl.id = "order".client_id),
       (select count(*) from "order" where "order".price > 1000 and cl.id = "order".client_id)
from client cl;

select cl.name,
       count(ord.price) filter ( where  ord.price > 1000) over w as num1,
       count(ord.price) filter ( where  ord.price < 1000) over w as num2
from client cl
         join "order" ord on ord.client_id = cl.id
     window w as (PARTITION BY cl.name);

-- 2. Выбрать третий заказ для каждого клиента ( id, client_id, price)
with cte as (select ord.*,
               ROW_NUMBER() over w as row_num
        from client cl
        join "order" ord on ord.client_id = cl.id
         window w as (PARTITION BY cl.name order by ord.id))
select *
FROM cte
where row_num = 3;

-- 3. Выбрать для каждого клиента третий заказ сделанный после заказа стоимостью больше 1000 ( id, client_id, price)
with cte as (select ord.*,
                    lead(ord.id, 3) over (PARTITION BY cl.name order by ord.id) as search_order_id
             from client cl
                      join "order" ord on ord.client_id = cl.id),
     cte2 as (select search_order_id
              FROM cte
              where price > 1000 and search_order_id is not null)
select cte.id, cte.client_id, cte.price
FROM cte, cte2
where cte2.search_order_id = cte.id;