3 tables joins on One to many

I think the simplest way to do this is here:

SELECT n.id, n.nvl_title,
       COUNT(c.id) AS nvl_chapters,
       (select AVG(nr.rate_value) from novels_ratings nr where nr.novel_id = n.id) as nvl_rating,
       MAX(c.createdAt) AS nvl_last_update
FROM novels n
left JOIN chapters c ON c.nvl_id = n.id AND c.chp_status = 'Active'
WHERE n.nvl_status IN ("Active", "Finished") 
GROUP BY n.id;

Very straightforward, and it should perform well too.