Investigating the reputation system & stats porn | Final update

[Don Pellegrino]

I decided to investigate the reputation system and how some posters got so much rep. I wasn't sure what I would find, but I decided before starting that if someone had made alt accounts just to boost their rep I'd turn all those +reps into -reps. I also made some general stats sorting posts and posters by quality. There's a lot of IT folks here, so I'm posting the SQL as well for your enjoyment. That took quite a bit of time to do, so I hope you'll appreciate it! Anyway, let's get started with general stats.

First thing I did was look at the types of rep given.

  Spoiler:

SELECT
	reputation,
	COUNT(*) AS nb
FROM
	reputation
GROUP BY
	reputation

0 rep is what happens when someone with really low rep tries to +/-rep someone else.. it just doesn't add anything.

But then.. what the hell is those 2-3 reps?

  Spoiler:

SELECT
	ur.username AS user,
	r.reputation AS received,
	ug.username AS 'from',
	r.reason,
	DATE(FROM_UNIXTIME(r.dateline)) AS date
FROM
	reputation r
INNER JOIN user ur ON r.userid = ur.userid
INNER JOIN user ug ON r.whoadded = ug.userid
WHERE
	r.reputation > 1
ORDER BY
	r.dateline ASC

It's really old rep, from the first day the rep system was turned on (July 2, 2011 which is the day right after the switch to VB) before it was changed to +/-1 all the time to prevent people with high rep from having too much rep power and creating situations where people are afraid to speak against them because they'd get negrepped into oblivion instantly.

Next I sorted people by the ratio of given +reps compared to -reps.

  Spoiler:

SELECT
	@rownum:=@rownum+1 AS rank,
	z.*
FROM (
	SELECT
		u.username AS user,
		(plusreps.total * 100 / (plusreps.total + ABS(negreps.total))) AS '%',
		u.reputation AS rep,
		plusreps.total AS '+reps',
		ABS(negreps.total) AS '-reps'
	FROM 
		user u
	INNER JOIN (
		SELECT
			r.whoadded,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation > 0 
		GROUP BY
			r.whoadded
	) plusreps ON u.userid = plusreps.whoadded
	INNER JOIN (
		SELECT
			r.whoadded,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation < 0 
		GROUP BY
			r.whoadded
	) negreps ON u.userid = negreps.whoadded
	ORDER BY
		`%` DESC
	LIMIT 30
) z, (SELECT @rownum:=0) y

These guys are the chill bros of FHC. For example indi has given 285 +reps and only 14 -reps.

Same thing, but reversed, to see the uncool people.

  Spoiler:

SELECT
	@rownum:=@rownum+1 AS rank,
	z.*
FROM (
	SELECT
		u.username AS user,
		(plusreps.total * 100 / (plusreps.total + ABS(negreps.total))) AS '%',
		u.reputation AS rep,
		plusreps.total AS '+reps',
		ABS(negreps.total) AS '-reps'
	FROM 
		user u
	INNER JOIN (
		SELECT
			r.whoadded,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation > 0 
		GROUP BY
			r.whoadded
	) plusreps ON u.userid = plusreps.whoadded
	INNER JOIN (
		SELECT
			r.whoadded,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation < 0 
		GROUP BY
			r.whoadded
	) negreps ON u.userid = negreps.whoadded
	ORDER BY
		`%` ASC
	LIMIT 30
) z, (SELECT @rownum:=0) y

Same thing again, but only with the posters with 100 or more rep.

  Spoiler:

SELECT
	@rownum:=@rownum+1 AS rank,
	z.*
FROM (
	SELECT
		u.username AS user,
		(plusreps.total * 100 / (plusreps.total + ABS(negreps.total))) AS '%',
		u.reputation AS rep,
		plusreps.total AS '+reps',
		ABS(negreps.total) AS '-reps'
	FROM 
		user u
	INNER JOIN (
		SELECT
			r.whoadded,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation > 0 
		GROUP BY
			r.whoadded
	) plusreps ON u.userid = plusreps.whoadded
	INNER JOIN (
		SELECT
			r.whoadded,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation < 0 
		GROUP BY
			r.whoadded
	) negreps ON u.userid = negreps.whoadded
	WHERE
		u.reputation >= 100
	ORDER BY
		`%` DESC
) z, (SELECT @rownum:=0) y

Daco and Sponk are the chillest and spasm and Lallante the most bitter.

And now this is the most important one: list of the posters with 100 or more rep and the posters that have given them 8 or more rep. The top posters that didn't have at least one major contributor are not included.

  Spoiler:

SELECT
	ur.username AS user,
	ur.reputation AS rep,
	GROUP_CONCAT(CONCAT(ug.username, ' : +', total.total) SEPARATOR ', ') AS 'received the most rep from'
FROM (
	SELECT
		r.userid,
		r.whoadded,
		SUM(r.reputation) AS total
	FROM
		reputation r
	WHERE
		r.reputation > 0
	GROUP BY
		r.userid,
		r.whoadded
	ORDER BY
		r.userid,
		total DESC
) total
INNER JOIN user ur ON total.userid = ur.userid
INNER JOIN user ug ON total.whoadded = ug.userid
WHERE
	ur.reputation >= 100 AND
	total.total > 7
GROUP BY
	total.userid
ORDER BY
	ur.reputation DESC

It's borderline cartel-ish, but not enough to go full madmin on them.

And while we're at it, those who have given the most -rep to the posters with negative rep.

  Spoiler:

SELECT
	ur.username AS user,
	ur.reputation AS rep,
	GROUP_CONCAT(CONCAT(ug.username, ' : ', total.total) SEPARATOR ', ') AS 'received the most negrep from'
FROM (
	SELECT
		r.userid,
		r.whoadded,
		SUM(r.reputation) AS total
	FROM
		reputation r
	WHERE
		r.reputation < 0
	GROUP BY
		r.userid,
		r.whoadded
	ORDER BY
		r.userid,
		total ASC
) total
INNER JOIN user ur ON total.userid = ur.userid
INNER JOIN user ug ON total.whoadded = ug.userid
WHERE
	ur.reputation < 0 AND
	total.total < -5
GROUP BY
	total.userid
ORDER BY
	ur.reputation ASC

Amantus and Tropic9 are particularly bitter.

I also looked at how much rep the >= 100 guys have compared to everyone else..

  Spoiler:

SELECT
	stats.test AS 'all plusreps',
	stats.top AS 'plusreps received by >=100 users',
	(stats.top * 100 / stats.test) AS '%'
FROM (
	SELECT
		(SELECT
			COUNT(*) AS total
		FROM
			reputation
		WHERE
			reputation > 0
		) AS test,
		(SELECT
			COUNT(*) AS total
		FROM
			reputation r
		INNER JOIN user u ON r.userid = u.userid
		WHERE
			r.reputation > 0 AND
			u.reputation >= 100) AS top
) stats

19 posters received roughly 25% of all the given +reps.

Now who is the best poster? This is the list of >=100 rep posters sorted by the percentage of received rep that is a +rep.

  Spoiler:

SELECT
	@rownum:=@rownum+1 AS rank,
	z.*
FROM (
	SELECT
		u.username AS user,
		(plusreps.total * 100 / (plusreps.total + ABS(negreps.total))) AS '%',
		u.reputation AS rep,
		plusreps.total AS '+reps',
		ABS(negreps.total) AS '-reps'
	FROM 
		user u
	INNER JOIN (
		SELECT
			r.userid,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation > 0 
		GROUP BY
			r.userid
	) plusreps ON u.userid = plusreps.userid
	INNER JOIN (
		SELECT
			r.userid,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation < 0 
		GROUP BY
			r.userid
	) negreps ON u.userid = negreps.userid
	WHERE
		u.reputation >= 100
	ORDER BY
		`%` DESC
) z, (SELECT @rownum:=0) y

And just for fun, the same thing with <0 rep posters. Percentage of received rep that is a -rep.

  Spoiler:

SELECT
	@rownum:=@rownum+1 AS rank,
	z.*
FROM (
	SELECT
		u.username AS user,
		(ABS(negreps.total) * 100 / (plusreps.total + ABS(negreps.total))) AS '%',
		u.reputation AS rep,
		plusreps.total AS '+reps',
		ABS(negreps.total) AS '-reps'
	FROM 
		user u
	INNER JOIN (
		SELECT
			r.userid,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation > 0 
		GROUP BY
			r.userid
	) plusreps ON u.userid = plusreps.userid
	INNER JOIN (
		SELECT
			r.userid,
			SUM(r.reputation) AS total
		FROM
			reputation r
		WHERE
			r.reputation < 0 
		GROUP BY
			r.userid
	) negreps ON u.userid = negreps.userid
	WHERE
		u.reputation < 0
	ORDER BY
		`%` DESC
) z, (SELECT @rownum:=0) y

I quickly realized that it's not the best way to find who's the best poster, so instead here's the list of all the posters sorted by the average number of posts needed to increase their rep by 1 (not the same as to receive 1 +rep). Another very important detail is that the rep system started on 2011-07-02 so this is ONLY with the posts from that date onwards. Only the users users with >= 250 posts because less than that screws up the stats.

  Spoiler:

SELECT
	@rownum:=@rownum+1 AS rank,
	z.*
FROM (
	SELECT
		stats.user,
		stats.rep,
		stats.postcount,
		stats.nb AS 'average nb of posts to get 1 rep'
	FROM (
		SELECT
			u.username AS user,
			u.reputation AS rep,
			t.nb AS postcount,
			(t.nb / (u.reputation - 10)) AS nb
		FROM
			user u
		INNER JOIN (
			SELECT
				userid,
				COUNT(*) AS nb
			FROM
				post
			WHERE
				DATE(FROM_UNIXTIME(dateline)) >= '2011-07-02'
			GROUP BY
				userid
			ORDER BY
				nb DESC
		) t ON u.userid = t.userid
		WHERE
			u.posts >= 250 AND
			u.reputation > 10
	) stats
	ORDER BY
		stats.nb ASC
	LIMIT 30
) z, (SELECT @rownum:=0) y

I'm not surprised that babes win.

Same thing, but only with >=100 rep posters.

  Spoiler:

SELECT
	@rownum:=@rownum+1 AS rank,
	z.*
FROM (
	SELECT
		stats.user,
		stats.rep,
		stats.postcount,
		stats.nb AS 'average nb of posts to get 1 rep'
	FROM (
		SELECT
			u.username AS user,
			u.reputation AS rep,
			t.nb AS postcount,
			(t.nb / (u.reputation - 10)) AS nb
		FROM
			user u
		INNER JOIN (
			SELECT
				userid,
				COUNT(*) AS nb
			FROM
				post
			WHERE
				DATE(FROM_UNIXTIME(dateline)) >= '2011-07-02'
			GROUP BY
				userid
			ORDER BY
				nb DESC
		) t ON u.userid = t.userid
		WHERE
			u.reputation >= 100
	) stats
	ORDER BY
		stats.nb ASC
) z, (SELECT @rownum:=0) y

Finally, here are the best and worst posts with the number of received rep and clickable links. If for example it has received 20 +reps and 10 -reps, it will be listed as '10'.

  Spoiler:

SELECT
	top.nb,
	u.username AS user,
	CONCAT('http://failheap-challenge.com/showthread.php?', p.threadid, '&p=', top.postid, '#post', top.postid) AS url
FROM
	(SELECT
		userid,
		postid,
		SUM(reputation) AS nb
	FROM
		reputation
	GROUP BY
		postid) top
INNER JOIN post p ON top.postid = p.postid
INNER JOIN user u ON top.userid = u.userid
WHERE
	top.nb >= 10
GROUP BY
	top.postid
ORDER BY
	top.nb DESC

+38 reps for the top one.

38 Hatepeace Lovewar http://failheap-challenge.com/showth...096#post204096
20 Jade Constantine http://failheap-challenge.com/showth...793#post415793
20 Nartek http://failheap-challenge.com/showth...396#post313396
17 Selb http://failheap-challenge.com/showth...146#post260146
16 stormyfs-shitpoastin http://failheap-challenge.com/showth...574#post381574
16 Devec http://failheap-challenge.com/showth...457#post289457
16 Nartek http://failheap-challenge.com/showth...143#post464143
14 Dirk Magnum http://failheap-challenge.com/showth...249#post343249
14 Grendelfreak http://failheap-challenge.com/showth...854#post491854
14 Entity http://failheap-challenge.com/showth...657#post151657
14 Nartek http://failheap-challenge.com/showth...578#post496578
13 EntroX http://failheap-challenge.com/showth...489#post372489
13 SpaceSperge http://failheap-challenge.com/showth...463#post471463
13 Chakrai http://failheap-challenge.com/showth...383#post387383
12 Spaztick http://failheap-challenge.com/showth...003#post411003
12 Lachesis VII http://failheap-challenge.com/showth...089#post307089
11 The Monkeysphere http://failheap-challenge.com/showth...559#post459559
11 Frug http://failheap-challenge.com/showth...239#post383239
11 spasm http://failheap-challenge.com/showth...348#post225348
11 Ophichius http://failheap-challenge.com/showth...712#post482712
11 Resi http://failheap-challenge.com/showth...124#post307124
11 Spaztick http://failheap-challenge.com/showth...585#post374585
11 Redclaws http://failheap-challenge.com/showth...600#post443600
11 Steph http://failheap-challenge.com/showth...647#post494647
10 GiDiYi http://failheap-challenge.com/showth...460#post454460
10 marcus xero http://failheap-challenge.com/showth...344#post450344
10 EchoEpsilon23 http://failheap-challenge.com/showth...159#post476159
10 ctrlchris http://failheap-challenge.com/showth...745#post410745
10 L'oiseau http://failheap-challenge.com/showth...299#post398299
10 ctrlchris http://failheap-challenge.com/showth...675#post439675
10 Samp http://failheap-challenge.com/showth...230#post346230
10 Frug http://failheap-challenge.com/showth...298#post418298

Worst posts

  Spoiler:

SELECT
	top.nb,
	u.username AS user,
	CONCAT('http://failheap-challenge.com/showthread.php?', p.threadid, '&p=', top.postid, '#post', top.postid) AS url
FROM
	(SELECT
		userid,
		postid,
		SUM(reputation) AS nb
	FROM
		reputation
	GROUP BY
		postid) top
INNER JOIN post p ON top.postid = p.postid
INNER JOIN user u ON top.userid = u.userid
WHERE
	top.nb <= -6
GROUP BY
	top.postid
ORDER BY
	top.nb ASC

-21 AkJon Ferguson http://failheap-challenge.com/showth...851#post294851
-13 Spaztick http://failheap-challenge.com/showth...725#post312725
-13 E T http://failheap-challenge.com/showth...202#post418202
-12 AsomBroso http://failheap-challenge.com/showth...555#post210555
-11 Al Simmons http://failheap-challenge.com/showth...523#post235523
-9 DoraTheExplorer http://failheap-challenge.com/showth...240#post150240
-9 Random Task http://failheap-challenge.com/showth...610#post433610
-9 XenosisReaper http://failheap-challenge.com/showth...508#post365508
-8 AkJon Ferguson http://failheap-challenge.com/showth...101#post294101
-8 zangorus http://failheap-challenge.com/showth...663#post336663
-7 LordsServant http://failheap-challenge.com/showth...986#post333986
-7 Spawinte http://failheap-challenge.com/showth...597#post397597
-7 God http://failheap-challenge.com/showth...814#post436814
-7 Al Simmons http://failheap-challenge.com/showth...002#post391002
-7 XenosisReaper http://failheap-challenge.com/showth...013#post156013
-7 Marlona Sky http://failheap-challenge.com/showth...656#post374656
-7 Smuggo http://failheap-challenge.com/showth...902#post456902
-7 QuackBot http://failheap-challenge.com/showth...765#post402765
-7 marcus xero http://failheap-challenge.com/showth...623#post447623
-6 Leviathan http://failheap-challenge.com/showth...772#post316772
-6 Cippalippus http://failheap-challenge.com/showth...940#post433940

EDIT: More stats on pages 2 and 3.

[Dark Flare]

list of bitter negreppers is entirely unsurprising

[EntroX]

list of bitter negreppers is entirely unsurprising

my thoughts exactly

[Joshua Foiritain]

Not featured in any of the lists, i am not a good poaster

Of course im not a bad poaster either so i guess its still a win.

[Tyrehl]

Yey im 7th on the chill bros list \o/ also ground floor :>


Thanks for the great thread, the +/-rep posts at the end were awesome (took me some time to find the clickable links so I wrote them manually instead )

[lubica]

That was truly an amazing post by HL, also, damn that is some proper statpr0n

[Cool09]

Excellent stats good sir.

[lt]

I feel that I should start using the rep system more.

What are the 'titles' for rep?

will be famous soon enough
shining bacon of light etc etc...

[Liare]

excellent post, and a bit of anal-retentive correction. Indi and Mynxee is, as far as i know, females and yet you lump them in with the "guys"

[Phrixus Zephyr]

It's borderline cartel-ish, but not enough to go full madmin on them.

You must spread some Reputation around before giving it to X again.

You sure about that?

[indeterminacy]

sickpuppy unironically +reping the shit outta Jolin

[Lorkin Desal]

This is a good thread and I plusrepped the OP.

[Seamus]

+re.....wait.....

[Mrenda]

All the best posters have around 60 rep. Me, FMAR, Mr. Marram, and Mynxee!

[Spaztick]

Are you telling me I'm terrible at posting? Because I don't want to have to face that possibility.

[Devec]

So is this the end of the rep conspiracy? Or are the representatives still on their way to present their irreputable evidence?

[whispous]

One of the best posts in weeks


Yet I shall refrain from +repping you because it's my democratic right

[whispous]

j/k +rep

[Ralara]

15th / 16th best poster ever

[Nordstern]

Circlejerk confirmed by data.

I feel vindicated.

Also, FMAR doesn't negrep, he simply infracts.