Postgres 16

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

CREATE TABLE room_categories 
(
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(25)
);


CREATE TABLE category_prices 
(
    category_price_id SERIAL PRIMARY KEY,
    room_category_id INTEGER REFERENCES room_categories(category_id),
    valid_period daterange,
    base_price DECIMAL(6, 2)
);

CREATE TABLE price_adjustments 
(
    adjustment_id SERIAL PRIMARY KEY,
    room_category_id INTEGER REFERENCES room_categories(category_id),
    valid_period daterange,
    price_adjustment DECIMAL(6, 2)
);

CREATE TABLE hotel_bookings 
(
    booking_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(35),
    room_category_id INTEGER REFERENCES room_categories(category_id),
    booking_period daterange
);

--the data:

INSERT INTO room_categories (category_name) VALUES
    ('single room'),
    ('double room')
returning *;

INSERT INTO category_prices (room_category_id, valid_period, base_price) VALUES
    (1, '[2023-01-01, 2023-01-31]', 80.00),
    (1, '[2023-02-01, 2023-02-28]', 85.00),
    (1, '[2023-03-01, 2023-03-31]', 88.00),
    (2, '[2023-01-01, 2023-01-31]', 100.00),
    (2, '[2023-02-01, 2023-02-28]', 105.00),
    (2, '[2023-03-01, 2023-03-31]', 108.00)
returning *;

INSERT INTO price_adjustments (room_category_id, valid_period, price_adjustment) VALUES
    (1, '[2023-01-15, 2023-02-14]', 11.00),
    (1, '[2023-01-10, 2023-01-20]', 7.00),
    (1, '[2023-01-28, 2023-02-14]', 8.00),
    (1, '[2023-01-17, 2023-02-03]', 13.00)
returning *;

INSERT INTO hotel_bookings (guest_name, room_category_id, booking_period) VALUES
    ('John Doe', 1, '[2023-01-15, 2023-01-20)'), 
    ('Jane Smith', 1, '[2023-01-30, 2023-02-02)'), 
    ('Jane Smith', 1, '[2023-02-25, 2023-03-03)'),
    ('Jordan Miller', 2, '[2023-01-30, 2023-03-02)')
returning *;

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE




category_id
category_name


1
single room

2
double room



INSERT 0 2




category_price_id
room_category_id
valid_period
base_price


1
1
[2023-01-01,2023-02-01)
80.00

2
1
[2023-02-01,2023-03-01)
85.00

3
1
[2023-03-01,2023-04-01)
88.00

4
2
[2023-01-01,2023-02-01)
100.00

5
2
[2023-02-01,2023-03-01)
105.00

6
2
[2023-03-01,2023-04-01)
108.00



INSERT 0 6




adjustment_id
room_category_id
valid_period
price_adjustment


1
1
[2023-01-15,2023-02-15)
11.00

2
1
[2023-01-10,2023-01-21)
7.00

3
1
[2023-01-28,2023-02-15)
8.00

4
1
[2023-01-17,2023-02-04)
13.00



INSERT 0 4




booking_id
guest_name
room_category_id
booking_period


1
John Doe
1
[2023-01-15,2023-01-20)

2
Jane Smith
1
[2023-01-30,2023-02-02)

3
Jane Smith
1
[2023-02-25,2023-03-03)

4
Jordan Miller
2
[2023-01-30,2023-03-02)



INSERT 0 4


--only base price (sumation) of the associated booking_id (booking_period)
SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    SUM((  upper(hb.booking_period * cp.valid_period) 
         - lower(hb.booking_period * cp.valid_period)) 
        * cp.base_price )
FROM hotel_bookings hb
JOIN category_prices cp 
ON hb.room_category_id = cp.room_category_id 
AND hb.booking_period && cp.valid_period
GROUP BY hb.booking_id, 
  hb.guest_name, 
  hb.room_category_id, 
  hb.booking_period;


booking_id
guest_name
room_category_id
booking_period
sum


1
John Doe
1
[2023-01-15,2023-01-20)
400.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
245.00

3
Jane Smith
1
[2023-02-25,2023-03-03)
516.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
3248.00



SELECT 4


--Only price adjustments (sumation) of the associated booking_id (booking_period)
SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    SUM((  upper(hb.booking_period * pa.valid_period) 
         - lower(hb.booking_period * pa.valid_period)) 
        * pa.price_adjustment ) as booking_price_adjustment
FROM hotel_bookings hb
JOIN price_adjustments pa 
ON hb.room_category_id = pa.room_category_id 
AND hb.booking_period && pa.valid_period
GROUP BY hb.booking_id, 
  hb.guest_name, 
  hb.room_category_id, 
  hb.booking_period;


booking_id
guest_name
room_category_id
booking_period
booking_price_adjustment


1
John Doe
1
[2023-01-15,2023-01-20)
129.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
96.00



SELECT 2


SELECT
	hb.booking_id,
	hb.guest_name,
	hb.room_category_id,
	hb.booking_period,
	SUM((
		upper(hb.booking_period * cp.valid_period) -
		lower(hb.booking_period * cp.valid_period)
	) * cp.base_price) + subquery.booking_price_adjustment AS total_price
FROM
	hotel_bookings hb
JOIN category_prices cp ON hb.room_category_id = cp.room_category_id
	AND hb.booking_period && cp.valid_period
LEFT JOIN (
	SELECT
		hb.booking_id,
		COALESCE(SUM((
			upper(hb.booking_period * pa.valid_period) -
			lower(hb.booking_period * pa.valid_period)
		) * pa.price_adjustment), 0) AS booking_price_adjustment
	FROM
		hotel_bookings hb
	LEFT JOIN price_adjustments pa ON hb.room_category_id = pa.room_category_id
		AND hb.booking_period && pa.valid_period
	GROUP BY
		hb.booking_id
) subquery ON hb.booking_id = subquery.booking_id
GROUP BY
	hb.booking_id,
	hb.guest_name,
	hb.room_category_id,
	hb.booking_period,
	subquery.booking_price_adjustment;


booking_id
guest_name
room_category_id
booking_period
total_price


1
John Doe
1
[2023-01-15,2023-01-20)
529.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
341.00

3
Jane Smith
1
[2023-02-25,2023-03-03)
516.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
3248.00



SELECT 4

category_price_id	room_category_id	valid_period	base_price
1	1	[2023-01-01,2023-02-01)	80.00
2	1	[2023-02-01,2023-03-01)	85.00
3	1	[2023-03-01,2023-04-01)	88.00
4	2	[2023-01-01,2023-02-01)	100.00
5	2	[2023-02-01,2023-03-01)	105.00
6	2	[2023-03-01,2023-04-01)	108.00

adjustment_id	room_category_id	valid_period	price_adjustment
1	1	[2023-01-15,2023-02-15)	11.00
2	1	[2023-01-10,2023-01-21)	7.00
3	1	[2023-01-28,2023-02-15)	8.00
4	1	[2023-01-17,2023-02-04)	13.00

booking_id	guest_name	room_category_id	booking_period
1	John Doe	1	[2023-01-15,2023-01-20)
2	Jane Smith	1	[2023-01-30,2023-02-02)
3	Jane Smith	1	[2023-02-25,2023-03-03)
4	Jordan Miller	2	[2023-01-30,2023-03-02)

booking_id	guest_name	room_category_id	booking_period	sum
1	John Doe	1	[2023-01-15,2023-01-20)	400.00
2	Jane Smith	1	[2023-01-30,2023-02-02)	245.00
3	Jane Smith	1	[2023-02-25,2023-03-03)	516.00
4	Jordan Miller	2	[2023-01-30,2023-03-02)	3248.00

booking_id	guest_name	room_category_id	booking_period	booking_price_adjustment
1	John Doe	1	[2023-01-15,2023-01-20)	129.00
2	Jane Smith	1	[2023-01-30,2023-02-02)	96.00

booking_id	guest_name	room_category_id	booking_period	total_price
1	John Doe	1	[2023-01-15,2023-01-20)	529.00
2	Jane Smith	1	[2023-01-30,2023-02-02)	341.00
3	Jane Smith	1	[2023-02-25,2023-03-03)	516.00
4	Jordan Miller	2	[2023-01-30,2023-03-02)	3248.00