Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table sales (id integer primary key, qty integer) ;
insert into sales (id,qty) values
(1,	1),
(2,	1),
(3,	1),
(4,	1),
(5,	3),
(6,	9) ;
create table slots (name text primary key, size integer) ;
insert into slots values
('a',	3),
('b',	5),
('c',	9),
('d',	null);
6 rows affected

4 rows affected

SELECT id
     , int4range
          ( (1 + sum(qty) OVER w - qty) :: integer
          , sum(qty) OVER w :: integer
          , '[]'
          ) AS sale_range
  FROM sales
WINDOW w AS (ORDER by id)
 ORDER BY id
 


id
sale_range


1
[1,2)

2
[2,3)

3
[3,4)

4
[4,5)

5
[5,8)

6
[8,17)


SELECT name
     , int4range
          ( (1 + sum(size) OVER w - COALESCE(size, 0)) :: integer
          , CASE WHEN size IS NULL THEN NULL :: integer ELSE sum(size) OVER w :: integer END
          , '[]'
          ) AS slot_range
  FROM slots
WINDOW w AS (ORDER BY size)
 ORDER BY size


name
slot_range


a
[1,4)

b
[4,9)

c
[9,18)

d
[18,)


WITH sales_ranges AS
(
SELECT id, qty
     , int4range
          ( (1 + sum(qty) OVER w - qty) :: integer
          , sum(qty) OVER w :: integer
          , '[]'
          ) AS sale_range
  FROM sales
WINDOW w AS (ORDER by id)
 ORDER BY id
), slots_ranges AS
(
SELECT name, size
     , int4range
          ( (1 + sum(size) OVER w - COALESCE(size, 0)) :: integer
          , CASE WHEN size IS NULL THEN NULL :: integer ELSE sum(size) OVER w :: integer END
          , '[]'
          ) AS slot_range
  FROM slots
WINDOW w AS (ORDER BY size)
 ORDER BY size
)
SELECT sa.id, sa.qty
     , upper(rg.range) - lower(rg.range) AS used
     , rg.range AS total
     , so.name AS slot
  FROM sales_ranges AS sa
 INNER JOIN slots_ranges AS so
    ON sa.sale_range && so.slot_range -- both ranges overlap
 CROSS JOIN LATERAL (SELECT sa.sale_range * so.slot_range) AS rg(range) -- compute the intersection of both ranges


id
qty
used
total
slot


1
1
1
[1,2)
a

2
1
1
[2,3)
a

3
1
1
[3,4)
a

4
1
1
[4,5)
b

5
3
3
[5,8)
b

6
9
1
[8,9)
b

6
9
8
[9,17)
c
id	qty	used	total	slot
1	1	1	[1,2)	a
2	1	1	[2,3)	a
3	1	1	[3,4)	a
4	1	1	[4,5)	b
5	3	3	[5,8)	b
6	9	1	[8,9)	b
6	9	8	[9,17)	c