dbrgn
(Danilo)
March 12, 2015, 1:27pm
1
What about this?
Pioneer / Early Adopter
Granted to the first 10 users that posted something on the forum. (Note that you should probably exclude all test and system accounts from this query…)
SELECT MIN(id) AS post_id, user_id, MIN(created_at) AS granted_at
FROM Posts
GROUP BY user_id
ORDER BY MIN(created_at)
LIMIT 10
5 Likes
PJH
(PJH)
March 12, 2015, 2:21pm
2
Presuming you want to reward (still?) active posters:
Two things:
Simplistically, the first 10 users are going to include @system and whichever test users you played with before going live
You’re going off the posts table, not the users table, in which case you’re going to run up against the same problem.
So, with your query as is (joining to users
to get a username, and adding post count), on our board:
discourse=# SELECT MIN(p.id) AS post_id, count(*) as post_count, user_id, username, MIN(p.created_at) AS granted_at
discourse-# FROM Posts p
discourse-# JOIN users u on u.id=p.user_id
discourse-# GROUP BY user_id, username
discourse-# ORDER BY MIN(p.created_at)
discourse-# LIMIT 10;
post_id | post_count | user_id | username | granted_at
---------+------------+---------+---------------+----------------------------
1 | 1446 | -1 | system | 2014-02-03 11:33:49.542763
2 | 2141 | 12 | PaulaBean | 2014-02-03 11:33:49.808751
4 | 43 | 1 | zogstrip | 2014-02-06 15:50:54.871174
5 | 28 | 3 | Remy | 2014-02-06 15:51:35.092875
20 | 3 | 8 | awinter | 2014-04-27 02:08:11.289662
24 | 57 | 10 | InedoJohn | 2014-04-30 23:29:46.081481
40 | 3 | 11 | kharnagy | 2014-05-01 23:25:24.60669
62 | 8431 | 20 | PJH | 2014-05-07 19:08:44.10598
329 | 336 | 4 | apapadimoulis | 2014-05-19 23:17:03.744205
331 | 16 | 72 | Mason_Wheeler | 2014-05-20 00:41:04.917464
(10 rows)
I draw your attention to the post counts, and will note that both @system and @PaulaBean aren’t real users there.
(to be continued… I messed up my next query…)
2 Likes
dbrgn
(Danilo)
March 12, 2015, 2:31pm
3
You’re right about the system user, that would have to be excluded explicity. Test users should probably be removed anyways.
Actually I used bad wording in my post. I meant the first 10 users that posted something on Discourse (: I’ll edit that.
1 Like
PJH
(PJH)
March 12, 2015, 2:32pm
4
But that also counts PM’s flags and other hidden stuff. Moving from posts
to badge_posts
instead things get a little better:
discourse=# SELECT MIN(p.id) AS post_id, count(*) as post_count, user_id, username, MIN(p.created_at) AS granted_at
discourse-# FROM badge_posts p
discourse-# JOIN users u on u.id=p.user_id
discourse-# GROUP BY user_id, username
discourse-# ORDER BY MIN(u.created_at)
discourse-# LIMIT 10;
post_id | post_count | user_id | username | granted_at
---------+------------+---------+----------------+----------------------------
2 | 176 | 12 | PaulaBean | 2014-02-03 11:33:49.808751
329 | 288 | 4 | apapadimoulis | 2014-05-19 23:17:03.744205
331 | 16 | 72 | Mason_Wheeler | 2014-05-20 00:41:04.917464
332 | 2 | 378 | drachenstern | 2014-05-20 00:41:32.127184
333 | 4714 | 558 | ben_lubar | 2014-05-20 00:42:07.805326
334 | 432 | 15 | morbiuswilters | 2014-05-20 00:45:11.150218
340 | 6936 | 123 | HardwareGeek | 2014-05-20 00:58:24.331848
341 | 16 | 24 | El_Heffe | 2014-05-20 01:00:28.866042
342 | 38 | 73 | galgorah | 2014-05-20 01:01:28.884775
344 | 6 | 302 | Steeldragon | 2014-05-20 01:03:37.318729
(10 rows)
Again, we have @PaulaBean in there (but she could be special-cased) but we still have a problem with at least one test user in there. But post counts aren’t a reliable indicator there since one of the low-counters is actually an active member.
But, that’s sorting by first-posters, not first-signers:
discourse=# SELECT MIN(p.id) AS post_id, count(*) as post_count, user_id, username, MIN(p.created_at) AS granted_at
discourse-# FROM badge_posts p
discourse-# JOIN users u on u.id=p.user_id
discourse-# GROUP BY user_id, username
discourse-# ORDER BY MIN(u.created_at)
discourse-# LIMIT 10;
post_id | post_count | user_id | username | granted_at
---------+------------+---------+----------------+----------------------------
3019 | 30 | -1 | system | 2014-05-23 14:43:00.005025
6107 | 24 | 1 | zogstrip | 2014-06-03 15:10:24.521304
6515 | 21 | 3 | Remy | 2014-06-04 12:41:56.283767
329 | 288 | 4 | apapadimoulis | 2014-05-19 23:17:03.744205
4134 | 93 | 6 | mark_bowytz | 2014-05-28 17:00:04.554124
2 | 176 | 12 | PaulaBean | 2014-02-03 11:33:49.808751
334 | 432 | 15 | morbiuswilters | 2014-05-20 00:45:11.150218
429 | 1248 | 16 | dhromed | 2014-05-20 10:05:50.856007
408 | 7345 | 17 | blakeyrat | 2014-05-20 06:50:54.655219
366 | 13619 | 18 | boomzilla | 2014-05-20 01:35:13.399921
(10 rows)
Once again, @system and Paula. Note that our site went live in May, but was setup earlier (note Paula’s timestamp in Feb.)
Also some of those users are no longer (currently - one is ‘on leave’) active.
So @dbrgn - you’d need to make a few more assumptions (read: clauses in your query) to get a decent set of awards out of it.
3 Likes