clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36260 in the last week).

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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)