MariaDB 10.2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users
(
    id integer PRIMARY KEY, 
    user_name varchar(5)
);
INSERT INTO users
    (id, user_name)
VALUES
    (1, 'Jack'),
    (2, 'Peter'),
    (3, 'Ali'),
    (4, 'James')
;
CREATE TABLE tags
(
    id  integer NOT NULL PRIMARY KEY, 
    tag varchar(5)
) ;
INSERT INTO tags
    (id, tag)
VALUES
    (1, 'php'),
    (2, 'html'),
    (3, 'css'),
    (4, 'mysql') ;
CREATE TABLE reputations
(
    id  integer PRIMARY KEY, 
    post_id  integer /* REFERENCES posts(id) */, 
    user_id integer REFERENCES users(id), 
    score integer, 
    reputation integer, 
    date_time integer
);
CREATE INDEX idx_reputation_user_id ON reputations(user_id);
CREATE INDEX idx_reputation_post_id ON reputations(post_id);
INSERT INTO reputations
    (id, post_id, user_id, score, reputation, date_time)
VALUES
    (1, 1, 1, 1, 5, 1500489844),
    (2, 4, 3, -1, -2, 1500499815),
    (3, 2, 3, 1, 5, 1500584821),
    (4, 3, 1, 1, 5, 1501389166),
    (5, 2, 4, 1, 5, 1501399142),
    (6, 2, 1, -1, -2, 1501399142),
    (7, 4, 1, 0, 15, 1501481186),
    (8, 5, 1, 1, 5, 1501481297)
CREATE TABLE post_tag
(
    post_id integer /* REFERENCES posts(id) */, 
    tag_id integer REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);
CREATE INDEX idx_post_tag_tag ON post_tag(tag_id) ;
INSERT INTO post_tag
    (post_id, tag_id)
VALUES
    (1, 2),
    (1, 4),
    (2, 2),
    (3, 1),
    (3, 4),
    (4, 3),
    (5, 1);
-- List of tags for users, together with reputations and scores
SELECT
    u.id, u.user_name, t.tag, sum(r.reputation) AS tag_reputation, sum(r.score) AS tag_score,count(r.post_id) as post_count
FROM
    users u
    LEFT JOIN reputations r ON r.user_id = u.id AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
    JOIN post_tag pt ON pt.post_id = r.post_id
    JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
    u.id, u.user_name, t.tag 
ORDER BY
    u.id, tag_reputation DESC;


id
user_name
tag
tag_reputation
tag_score
post_count


1
Jack
css
15
0
1

1
Jack
php
10
2
2

1
Jack
mysql
5
1
1

1
Jack
html
-2
-1
1