Мне приходит в голову только join на вложенные select:
select user.user_id, (coalesce(topics.sum, 0) + coalesce(comments.sum, 0)) as rating from tb_user user
left join (select user_id, sum(topic_rating) as sum from tb_topic where topic_date_add > NOW() - INTERVAL 7 DAY group by user_id) topics on topics.user_id = user.user_id
left join (select user_id, sum(comment_rating) as sum from tb_comment where comment_date > NOW() - INTERVAL 7 DAY group by user_id) comments on comments.user_id = user.user_id
order by rating desc limit 10;