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 |