MySQL - GROUP BY with JOIN query

I am using MySQL 8.0.23 I have 4 tables: houses, intervals, discounts, and availability. **Houses:** create table houses ( id int auto_increment primary key, name varchar(255) null, occupancy tinyint null ); insert into houses (id, name, occupancy) values (1, 'Celopatra', 2); +----+-----------+-----------+ | id | name | occupancy | +----+-----------+-----------+ | 1 | Cleopatra | 4 | +----+-----------+-----------+ **Intervals:** create table intervals ( id int auto_increment primary key, house_id int null, start_date date null, end_date date null, adult_price int null, child_price int null, min_stay int null, max_stay int null ); insert into intervals (id, house_id, start_date, end_date, adult_price, child_price, min_stay, max_stay) values (1, 1, '2021-02-01', '2021-02-05', 40, 30, 2, 4), (2, 1, '2021-02-06', '2021-02-10', 50, 40, 2, 4), (3, 1, '2021-02-11', '2021-02-15', 60, 50, 2, 4); +----+----------+------------+------------+-------------+-------------+----------+----------+ | id | house_id | start_date | end_date | adult_price | child_price | min_stay | max_stay | +----+----------+------------+------------+-------------+-------------+----------+----------+ | 1 | 1 | 2021-02-01 | 2021-02-05 | 40 | 30 | 2 | 4 | | 2 | 1 | 2021-02-06 | 2021-02-10 | 50 | 40 | 2 | 4 | | 3 | 1 | 2021-02-11 | 2021-02-15 | 60 | 50 | 2 | 4 | +----+----------+------------+------------+-------------+-------------+----------+----------+ **Availability:** This table is an expanded version of the intervals table. I am using the PHP backend to generate days for each interval period to query for availability. However, the price column in this table is not related to adult_price, child_price in the intervals table. It is a minimum deposit price for each day. create table availability ( id int null, interval_id int null, date date null, price int null, is_available int null ); insert into availability (id, interval_id, date, price, is_available) values (1, 1, '2021-02-01', 100, 1), (2, 1, '2021-02-02', 100, 1), (3, 1, '2021-02-03', 100, 1), (4, 1, '2021-02-04', 100, 1), (5, 1, '2021-02-05', 100, 1), (6, 2, '2021-02-06', 120, 1), (7, 2, '2021-02-07', 120, 1), (8, 2, '2021-02-08', 120, 1), (9, 2, '2021-02-09', 120, 1), (10, 2, '2021-02-10', 120, 1), (11, 3, '2021-02-11', 130, 1), (12, 3, '2021-02-12', 130, 1), (13, 3, '2021-02-13', 130, 1), (14, 3, '2021-02-14', 130, 1), (15, 3, '2021-02-15', 130, 1); +------+-------------+------------+-------+--------------+ | id | interval_id | date | price | is_available | +------+-------------+------------+-------+--------------+ | 1 | 1 | 2021-02-01 | 100 | 1 | | 2 | 1 | 2021-02-02 | 100 | 1 | | 3 | 1 | 2021-02-03 | 100 | 1 | | 4 | 1 | 2021-02-04 | 100 | 1 | | 5 | 1 | 2021-02-05 | 100 | 1 | | 6 | 2 | 2021-02-06 | 120 | 1 | | 7 | 2 | 2021-02-07 | 120 | 1 | | 8 | 2 | 2021-02-08 | 120 | 1 | | 9 | 2 | 2021-02-09 | 120 | 1 | | 10 | 2 | 2021-02-10 | 120 | 1 | | 11 | 3 | 2021-02-11 | 130 | 1 | | 12 | 3 | 2021-02-12 | 130 | 1 | | 13 | 3 | 2021-02-13 | 130 | 1 | | 14 | 3 | 2021-02-14 | 130 | 1 | | 15 | 3 | 2021-02-15 | 130 | 1 | +------+-------------+------------+-------+--------------+ **Discounts:** Type 1 represents percent discounts, 2 represents fixed amount discounts. create table discounts ( id int auto_increment primary key, interval_id int null, discount_type int null, discount_amount int null, status int null ); insert into discounts (id, interval_id, discount_type, discount_amount, status) values (1, 1, 1, 20, 1), (2, 1, 2, 40, 1); +----+-------------+---------------+-----------------+--------+ | id | interval_id | discount_type | discount_amount | status | +----+-------------+---------------+-----------------+--------+ | 1 | 1 | 1 | 20 | 1 | | 2 | 1 | 2 | 40 | 1 | +----+-------------+---------------+-----------------+--------+ **Final goal:** I want to list available houses by their final price (after discount applied - discount may not occur as well) for the specific date period. To achieve that firstly I am looking for available houses for the given date, after that I am sending the following parameters: sum, minimum start date, house id, and day_count to the calculate function. Now, I was wondering is it possible to optimize this query. I thought, maybe instead of passing the min start date to calculate function, passing interval id directly would be much efficient. How would you implement the same functionality in a better way? What would you say about the performance of this query? You can view DDL and DML statements via this link CREATE FUNCTION calculate(sum INTEGER, minStartDate DATE, house_id INTEGER, day_count INTEGER) RETURNS INTEGER DETERMINISTIC BEGIN DECLARE interval_id INTEGER; set interval_id = (select id from intervals s where s.start_date = minStartDate and s.house_id = house_id); SET sum = IFNULL((SELECT sum - sum * d.discount_amount / 100 FROM discounts d WHERE d.interval_id = interval_id and d.discount_type = 1), sum); SET sum = IFNULL((SELECT sum - day_count * d.discount_amount FROM discounts d WHERE d.interval_id = interval_id and d.discount_type = 2), sum); return sum; END; select house_id, sum(a.price) as price, count(a.id) as day_count, min(i.start_date), calculate(sum(a.price), min(i.start_date), house_id, count(a.id)) as final_price from availability a inner join intervals i on i.id = a.interval_id where a.date > '2021-02-03' and a.date < '2021-02-13' and a.is_available = 1 group by i.house_id having day_count = 9;