with requirement(product, lmt) as (
    values
    (1, 100),
    (2, 45),
    (3, 40)
), offer(provider, product, qty) as (
    values 
    (1, 1, 80),
    (1, 2, 50),
    (2, 1, 50),
    (2, 2, 200)
), t1 as (
    select * from offer natural join requirement
), t2 as ( 
    select *, sum(qty) over(partition by product order by provider) as sum_qty from t1
), t3 as (
    select *, coalesce(lag(sum_qty) over(partition by product order by provider), 0) as lag_sum_qty from t2
), t4 as (
    select * from t3 where lag_sum_qty < lmt
)
select product, provider, case when sum_qty < lmt then qty else lmt - lag_sum_qty end as qty from t4;