clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926637 fiddles created (21818 in the last week).

CREATE TABLE users ( id integer PRIMARY KEY, user_name varchar(5) );
 hidden batch(es)


INSERT INTO users (id, user_name) VALUES (1, 'Jack'), (2, 'Peter'), (3, 'Ali'), (4, 'James') ;
 hidden batch(es)


CREATE TABLE tags ( id integer NOT NULL PRIMARY KEY, tag varchar(5) ) ;
 hidden batch(es)


INSERT INTO tags (id, tag) VALUES (1, 'php'), (2, 'html'), (3, 'css'), (4, 'mysql') ;
 hidden batch(es)


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 );
 hidden batch(es)


CREATE INDEX idx_reputation_user_id ON reputations(user_id);
 hidden batch(es)


CREATE INDEX idx_reputation_post_id ON reputations(post_id);
 hidden batch(es)


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)
 hidden batch(es)


CREATE TABLE post_tag ( post_id integer /* REFERENCES posts(id) */, tag_id integer REFERENCES tags(id), PRIMARY KEY (post_id, tag_id) );
 hidden batch(es)


CREATE INDEX idx_post_tag_tag ON post_tag(tag_id) ;
 hidden batch(es)


INSERT INTO post_tag (post_id, tag_id) VALUES (1, 2), (1, 4), (2, 2), (3, 1), (3, 4), (4, 3) ;
 hidden batch(es)


SELECT u.id, u.user_name, coalesce(sum(r.score), 0) as score, coalesce(sum(r.reputation), 0) as reputation 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)) */ GROUP BY u.id, u.user_name ORDER BY reputation DESC, score DESC ; /* +----+--------+-------+------------+----------+ | id | name | score | reputation | tags | +----+--------+-------+------------+----------+ | 1 | Jack | 0 | 18 | css,php | | 3 | Ali | 1 | 5 | html | | 2 | Peter | 0 | 0 | NULL | +----+--------+-------+------------+----------+ -- Note: It's ordered by reputation, score columns */
id user_name score reputation
1 Jack 0 18
4 James 1 5
2 Peter 0 0
3 Ali 0 0
 hidden batch(es)


-- List of tags for users, together with reputations SELECT u.id, u.user_name, t.tag, sum(r.reputation) AS tag_reputation 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 GROUP BY u.id, u.user_name, t.tag ORDER BY u.id, tag_reputation DESC;
id user_name tag tag_reputation
1 Jack css 15
1 Jack mysql 5
1 Jack php 5
1 Jack html -2
4 James html 5
 hidden batch(es)


SELECT q1.user_id, q1.user_name, q1.score, q1.reputation, substring_index(group_concat(q2.tag ORDER BY q2.tag_reputation DESC SEPARATOR ','), ',', 2) AS top_two_tags FROM (SELECT u.id AS user_Id, u.user_name, coalesce(sum(r.score), 0) as score, coalesce(sum(r.reputation), 0) as reputation 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)) */ GROUP BY u.id, u.user_name ) AS q1 LEFT JOIN ( SELECT r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation FROM reputations r JOIN post_tag pt ON pt.post_id = r.post_id JOIN tags t ON t.id = pt.tag_id WHERE r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */ GROUP BY user_id, t.tag ) AS q2 ON q2.user_id = q1.user_id GROUP BY q1.user_id, q1.user_name, q1.score, q1.reputation ORDER BY q1.reputation DESC, q1.score DESC ;
user_id user_name score reputation top_two_tags
1 Jack 0 18 css,mysql
4 James 1 5 html
2 Peter 0 0
3 Ali 0 0
 hidden batch(es)