Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


create table orders (
customer text,
order_no int
);

insert into orders values 
('Customer1', 400),
('Customer2', 1200),
('Customer3', 40),
('Customer4', 2000),
('Customer5', 700);

create table pricing_data
(
 high_limit int, 
 price numeric
);

insert into pricing_data values
(250, 50),
(500, 45),
(1000, 40),
(10000, 30);

create view pricing as
  select coalesce(lag(high_limit) over (order by high_limit), 0) as last_limit,
         high_limit, price
    from pricing_data;
5 rows affected

4 rows affected

select o.customer, 
       least(o.order_no - p.last_limit, p.high_limit - p.last_limit) as order_no,
       p.price as charges
  from orders o
  join pricing p on p.last_limit < o.order_no
 order by o.customer, p.price desc
;


customer
order_no
charges


Customer1
250
50

Customer1
150
45

Customer2
250
50

Customer2
250
45

Customer2
500
40

Customer2
200
30

Customer3
40
50

Customer4
250
50

Customer4
250
45

Customer4
500
40

Customer4
1000
30

Customer5
250
50

Customer5
250
45

Customer5
200
40

customer	order_no	charges
Customer1	250	50
Customer1	150	45
Customer2	250	50
Customer2	250	45
Customer2	500	40
Customer2	200	30
Customer3	40	50
Customer4	250	50
Customer4	250	45
Customer4	500	40
Customer4	1000	30
Customer5	250	50
Customer5	250	45
Customer5	200	40