Early Adopter badge

data & reporting
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
2

Presuming you want to reward (still?) active posters:

Two things:

  1. Simplistically, the first 10 users are going to include @system and whichever test users you played with before going live
  2. 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
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
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