subquery - MySQL sum two values from different tables based on same key and sum those together

Question

I already have a working query, but I have no clue if this is a good solution, because I have 3 sub queries and a join.

Scenario: Users can create threads and can create replies. Users can vote on these to give them karma (like Reddit).

Tables:

threads (user_id, title, upvotes_count, downvotes_count) replies (user_id, body, upvotes_count, downvotes_count)

What I want: Sum up the total karma based on upvotes and downvotes (threads+replies) for each user for the past month.

My ugly working query:

SELECT *, SUM(karma.threadkarma + karma.replieskarma) as karma FROM (
    SELECT t.user_id, t.karma as threadkarma, r.karma as replieskarma
    FROM (
        SELECT user_id, SUM(t.upvotes_count-t.downvotes_count) as karma
        FROM threads t
        WHERE t.created_at > CURDATE() - INTERVAL 30 DAY 
        GROUP BY t.user_id
    ) as t
    JOIN (
        SELECT r.user_id, SUM(r.upvotes_count-r.downvotes_count) as karma
        FROM replies r
        WHERE r.created_at > CURDATE() - INTERVAL 30 DAY 
        GROUP BY r.user_id
    ) as r
    GROUP BY user_id
) as karma
GROUP BY karma.user_id

question from:https://stackoverflow.com/questions/65947232/mysql-sum-two-values-from-different-tables-based-on-same-key-and-sum-those-toget

Answer 1

Waitting for answers