Sum weights ​belonging to (date)range types and their intersections

Question

I'm creating a hotel booking tool for our desk/reception using PostgreSQL 16. To answer booking requests, I need both the total price and the associated daily prices of the stay.

Current setup + dummy data: dbfiddle.uk

Short rundown of the tables:

category_prices:
This table stores the base_prices for each room category. At any date there is exactly one dataset "active" for each category. => No overlaps and only one price per date and category.

price_adjustments:
This table includes unexpected price increases, primarily due to increased occupancy. In this table there can (theoretically) be any number of overlaps between the intervals/dateranges. The values/prices belonging to these intervals must be summed up accordingly.

My current attempt calculates the total base_price and the total price_adjustment for each stay/interval and sums them up but I don't know how to compute the daily prices (or even where to start):

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;

I'm fairly new to databases (this is my first subquery).

I tried to figure out a way to generate an object/dataset/array/SELECT that contains/returns something like:

{ interval_1 : relevant_base_price + sum(relevant_price_adjustments)
, interval_2 : relevant_base_price + sum(relevant_price_adjustments)
, ...
, interval_n : relevant_base_price + sum(relevant_price_adjustments)}

where the intervals have no overlaps and their union equals the initial interval/daterange but I failed in every attempt.

Edit: As requested, tables and dummy data (dbfiddle copy):

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 *;

Answer 1

You ask for daily prices:

SELECT bp.booking_id, bp.guest_name, bp.room_category_id
     , d.the_day::date
     , bp.base_price
     , COALESCE(pa.sum_price_adjustment, 0) AS sum_price_adjustment
     , COALESCE(sum(bp.base_price)           OVER (PARTITION BY bp.booking_id), 0)
     + COALESCE(sum(pa.sum_price_adjustment) OVER (PARTITION BY bp.booking_id), 0) AS total_price_of_booking
FROM  (
   -- days not covered in category_prices are lost!
   SELECT hb.booking_id
        , hb.guest_name, hb.room_category_id
        , hb.booking_period * cp.valid_period AS bp_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
   ) bp
CROSS  JOIN generate_series(lower(bp.bp_period)::timestamp
                          , (upper(bp.bp_period) - 1)::timestamp
                          , interval '1 day') AS d(the_day)
CROSS  JOIN LATERAL (  -- can be INNER JOIN because agg always produces row
   SELECT sum(price_adjustment) AS sum_price_adjustment
   FROM   price_adjustments pa
   WHERE  pa.room_category_id = bp.room_category_id
   AND    pa.valid_period @> d.the_day::date
   ) pa
ORDER  BY bp.booking_id, d.the_day;

fiddle

• Step 1: Compute intersections between booking and base prices in subquery bp. (You already had that.) See:

  • Average stock history table

• Step 2: Generate series of days for each intersection in (implicitly) LATERAL subquery d. See:

  • Generating time series between two dates in PostgreSQL
  • What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

• Step 3: Compute sum_price_adjustment per day in a LATERAL subquery.

• Step 4: Optionally, add the total price per booking with window functions.

One row per day for each booking, with base price and the sum of price adjustments.
Plus the total price for the booking (optional).

Notes

Date ranges are stored with inclusive lower and exclusive upper bound. See:

• Extract number of days per week from multiple date ranges

My query produces incorrect / missing results if entries in category_prices are incomplete or overlapping. Make sure of consistent base data.

Also, be sure to have applicable indices to make this fast.

• Find overlapping date ranges in PostgreSQL
• Optimizing queries on a range of timestamps (two columns)
• How to get index scan for OR'ed time-range predicates?