What cool badge queries have you come up with?

badge

(Kane York) #44

Regular Emeritus

SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 3

Description: Users with this badge have been a regular user of this forum now or in the past. The badge is awarded when the user first attained Trust Level 3.

Leader Emeritus

SELECT user_id, granted_at
FROM user_badges
WHERE badge_id = 4

Description: Users with this badge have been an exemplary user of this forum now or in the past. The badge is awarded when the user first attained Trust Level 4.


Revocation query: NO (This is the important one :slight_smile: )
Targets posts: NO
Trigger: Daily
As a title: YES
Granted multiple: NO
Show badge: YES
Show post granting badge: NO
Enable: YES


(Jacob Chapel) #45

Couldn’t this be rewritten as:

SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE post_number = 1
AND msecs > 10000
AND ( topic_id = 206 OR topic_id = 1066 OR topic_id = 1043 )
GROUP BY user_id
HAVING count(*) = 3

I’m no SQL expert, just seems like it should be possible given the duplication in the original query.


(Crackmacs) #46

Would there be a way to assign a badge based on platform? Like, if someone were to login to the forum using Android or iOS or Windows and so on


(PJH) #47

Presuming 10 seconds is sufficient for all the posts, and the first post in each topic is the one being targeted, yes…


(PJH) #48

I don’t believe that sort of data is retained or accessible from the badge queries.


(Jacob Chapel) #49

Yeah, not sure what a good read time is, but 10 seconds seems okay.

I would also assume the first post is the focus.

Also couldn’t this be simplified a bit more? (Again naive SQL assumptions)

SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE post_number = 1
AND msecs > 10000
AND topic_id IN ( 206, 1066, 1043 )
GROUP BY user_id
HAVING count(*) = 3

(John ) #50

All are pretty great, though this one strikes me as the most extensible.

But this is just code golf now, right?


(PJH) #51

I had the same thought myself…


(Jacob Chapel) #52

I think it boils down to maintainable queries. Duplication is a pain to work around.


(Alessio Fattorini) #53

That’s great! Last question, how do you calculate the TOP poster of the month?


(Danilo) #54

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

(PJH) #55

The only difference (everything else above these is exactly the same) between the four queries is the last clause:

25%er:

FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.10 AND
    row_number < TotalUsers.max *.25

10%er:

FROM LastMonth, TotalUsers
WHERE row_number >= TotalUsers.max *.05 AND
    row_number < TotalUsers.max *.10

5%er:

FROM LastMonth, TotalUsers
WHERE row_number < TotalUsers.max *.05 AND 
row_number != 1

Top poster:

FROM LastMonth, TotalUsers
WHERE row_number = 1

Badge collection examples
Visual forum statistics
(PJH) #56

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…)


(Danilo) #57

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.


(PJH) #58

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.


(Alessio Fattorini) #59

@PJH do you implemented a badge on “read time” yet? :smiley:


(PJH) #60

Nope, not yet. May look into it sometime…


(Alessio Fattorini) #61

It could be really cool! Looking forward to your query :smiley:


(PJH) #62

There is actually a time_read field in user_stats - should be fairly simple to use that, but bear in mind I think it’s fudged somewhat (leaving a tab open for 24 hrs won’t, for example, add 24 hrs to your time reading.)

I did mess around with it else-forum back in October, though not sure if I got it quite right…

[postgres@sofa ~]$ sql_tdwtf time_read 

SELECT u.username, (time_read /86400 || ' days') || ' ' || TO_CHAR((time_read % 86400 || ' second')::interval, 'HH24:MI:SS') AS duration
FROM user_stats us
JOIN users u ON u.id=us.user_id
ORDER BY time_read DESC
LIMIT 25

    username     |     duration     
-----------------+------------------
 HardwareGeek    | 28 days 04:51:56
 ChaosTheEternal | 27 days 07:02:10
 PJH             | 23 days 09:40:24
 boomzilla       | 20 days 15:46:17
 aliceif         | 19 days 17:45:37
 ben_lubar       | 16 days 22:27:15
 Luhmann         | 16 days 21:06:04
 Onyx            | 16 days 07:52:28
 Arantor         | 16 days 06:16:00
 abarker         | 16 days 05:37:31
 faoileag        | 14 days 17:40:01
 Maciejasjmj     | 14 days 11:29:28
 cartman82       | 14 days 09:34:25
 Keith           | 14 days 05:16:52
 dkf             | 14 days 03:28:55
 blakeyrat       | 13 days 18:54:00
 darkmatter      | 13 days 15:24:54
 accalia         | 13 days 02:27:41
 Zecc            | 13 days 01:04:15
 antiquarian     | 12 days 01:04:01
 Matches         | 11 days 19:21:03
 VinDuv          | 11 days 07:47:35
 loopback0       | 10 days 12:45:37
 locallunatic    | 10 days 12:38:35
 reverendryan    | 10 days 08:41:02
(25 rows)

Edit - for comparison, that query returns the following as of 0400UTC this morning:

    username     |      duration
-----------------+--------------------
 HardwareGeek    | 56 day(s) 19:12:15
 ChaosTheEternal | 49 day(s) 22:41:29
 accalia         | 46 day(s) 13:42:15
 boomzilla       | 44 day(s) 17:11:23
 aliceif         | 42 day(s) 04:06:41
 PJH             | 41 day(s) 05:50:33
 reverendryan    | 37 day(s) 07:46:37
 Onyx            | 34 day(s) 20:06:48
 dkf             | 34 day(s) 06:35:03
 abarker         | 32 day(s) 19:26:00
 Luhmann         | 31 day(s) 22:07:55
 ben_lubar       | 31 day(s) 15:37:57
 loopback0       | 31 day(s) 00:54:37
 blakeyrat       | 30 day(s) 13:40:02
 Maciejasjmj     | 27 day(s) 00:17:55
 Jaloopa         | 26 day(s) 17:59:05
 Polygeekery     | 25 day(s) 09:11:23
 antiquarian     | 25 day(s) 02:50:19
 Zecc            | 24 day(s) 07:41:55
 VinDuv          | 22 day(s) 10:42:21
 locallunatic    | 22 day(s) 09:30:10
 cartman82       | 22 day(s) 01:41:44
 tar             | 21 day(s) 22:43:10
 PleegWat        | 21 day(s) 10:25:17
 Buddy           | 21 day(s) 05:23:12
(25 rows)

Elapsed: 0.031s

(Channing Hinton) #63

I get a message of

An error occurred: Contract violation: Query does not return a 'granted_at' column

when trying that one.