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
;
