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(10, 2)
);

CREATE TABLE hotel_bookings 
(
    booking_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(255),
    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 hotel_bookings (guest_name, room_category_id, booking_period) VALUES
    ('John Doe', 1, '[2023-01-15, 2023-01-20)'), --correct calced, 1 intersection
    ('Jane Smith', 1, '[2023-01-30, 2023-02-02)'), -- 2 shares (need/want a sumup)
    ('Jane Smith', 1, '[2023-02-25, 2023-03-03)'),
    ('Jordan Miller', 2, '[2023-01-30, 2023-03-02)')-- 3 shares (need/want a sumup)
returning *;

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-01-31)
80.00

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

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

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

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

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



INSERT 0 6




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


SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    jsonb_pretty(jsonb_object_agg(cp.valid_period,cp.base_price)),
    SUM(CASE WHEN  upper(hb.booking_period * cp.valid_period) = upper(hb.booking_period) 
             THEN (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period)) * cp.base_price
             ELSE (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period) + 1) * cp.base_price
        END )
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
jsonb_pretty
sum


1
John Doe
1
[2023-01-15,2023-01-20)
{
    "[2023-01-01,2023-01-31)": 80.00
}
400.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
{
    "[2023-01-01,2023-01-31)": 80.00,
    "[2023-02-01,2023-02-28)": 85.00
}
245.00

3
Jane Smith
1
[2023-02-25,2023-03-03)
{
    "[2023-02-01,2023-02-28)": 85.00,
    "[2023-03-01,2023-03-31)": 88.00
}
516.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
{
    "[2023-01-01,2023-01-31)": 100.00,
    "[2023-02-01,2023-02-28)": 105.00,
    "[2023-03-01,2023-03-31)": 108.00
}
3248.00



SELECT 4


UPDATE category_prices 
SET valid_period=daterange(lower(valid_period),upper(valid_period),'[]')
RETURNING *;

--that's the same as inserting them with the inclusive upper bound `]` in the first place
truncate category_prices;
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 *;


SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    jsonb_pretty(jsonb_object_agg(cp.valid_period,cp.base_price)),
    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;


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



UPDATE 6



TRUNCATE TABLE




category_price_id
room_category_id
valid_period
base_price


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

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

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

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

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

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



INSERT 0 6




booking_id
guest_name
room_category_id
booking_period
jsonb_pretty
sum


1
John Doe
1
[2023-01-15,2023-01-20)
{
    "[2023-01-01,2023-02-01)": 80.00
}
400.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
{
    "[2023-01-01,2023-02-01)": 80.00,
    "[2023-02-01,2023-03-01)": 85.00
}
245.00

3
Jane Smith
1
[2023-02-25,2023-03-03)
{
    "[2023-02-01,2023-03-01)": 85.00,
    "[2023-03-01,2023-04-01)": 88.00
}
516.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
{
    "[2023-01-01,2023-02-01)": 100.00,
    "[2023-02-01,2023-03-01)": 105.00,
    "[2023-03-01,2023-04-01)": 108.00
}
3248.00



SELECT 4

category_price_id	room_category_id	valid_period	base_price
1	1	[2023-01-01,2023-01-31)	80.00
2	1	[2023-02-01,2023-02-28)	85.00
3	1	[2023-03-01,2023-03-31)	88.00
4	2	[2023-01-01,2023-01-31)	100.00
5	2	[2023-02-01,2023-02-28)	105.00
6	2	[2023-03-01,2023-03-31)	108.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	jsonb_pretty	sum
1	John Doe	1	[2023-01-15,2023-01-20)	{ "[2023-01-01,2023-01-31)": 80.00 }	400.00
2	Jane Smith	1	[2023-01-30,2023-02-02)	{ "[2023-01-01,2023-01-31)": 80.00, "[2023-02-01,2023-02-28)": 85.00 }	245.00
3	Jane Smith	1	[2023-02-25,2023-03-03)	{ "[2023-02-01,2023-02-28)": 85.00, "[2023-03-01,2023-03-31)": 88.00 }	516.00
4	Jordan Miller	2	[2023-01-30,2023-03-02)	{ "[2023-01-01,2023-01-31)": 100.00, "[2023-02-01,2023-02-28)": 105.00, "[2023-03-01,2023-03-31)": 108.00 }	3248.00

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

booking_id	guest_name	room_category_id	booking_period	jsonb_pretty	sum
1	John Doe	1	[2023-01-15,2023-01-20)	{ "[2023-01-01,2023-02-01)": 80.00 }	400.00
2	Jane Smith	1	[2023-01-30,2023-02-02)	{ "[2023-01-01,2023-02-01)": 80.00, "[2023-02-01,2023-03-01)": 85.00 }	245.00
3	Jane Smith	1	[2023-02-25,2023-03-03)	{ "[2023-02-01,2023-03-01)": 85.00, "[2023-03-01,2023-04-01)": 88.00 }	516.00
4	Jordan Miller	2	[2023-01-30,2023-03-02)	{ "[2023-01-01,2023-02-01)": 100.00, "[2023-02-01,2023-03-01)": 105.00, "[2023-03-01,2023-04-01)": 108.00 }	3248.00