-NaN% posters and hamster smurfing!
-
It's dead, Jim.
-
Given the timing of my posts last night, I rather suspect I accidentally unplugged it and it's gone into hibernation.
-
Ah, layer 8 error.
-
@shadowmod attendance antiquarian
-
Attendance antiquarian 50 WITH StartingPoints AS ( SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum FROM user_visits AS A WHERE NOT EXISTS ( SELECT * FROM user_visits AS B WHERE B.visited_at = A.visited_at - 1 AND B.user_id = A.user_id ) ), EndingPoints AS ( SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum FROM user_visits AS A WHERE NOT EXISTS ( SELECT * FROM user_visits AS B WHERE B.visited_at = A.visited_at + 1 AND B.user_id = A.user_id ) ) SELECT u.username, S.visited_at AS start_range, E.visited_at AS end_range, (E.visited_at - S.visited_at +1) AS Days FROM StartingPoints AS S JOIN EndingPoints AS E ON E.rownum = S.rownum JOIN users u ON u.id=S.user_id AND u.username ILIKE $1::varchar ORDER BY S.visited_at DESC LIMIT $2::int username | start_range | end_range | days antiquarian | 2014-11-10 | 2015-01-10 | 62 antiquarian | 2014-09-07 | 2014-11-08 | 62 antiquarian | 2014-08-17 | 2014-09-05 | 20 antiquarian | 2014-07-27 | 2014-08-15 | 20 antiquarian | 2014-07-13 | 2014-07-25 | 13 antiquarian | 2014-07-06 | 2014-07-11 | 6 antiquarian | 2014-06-22 | 2014-07-03 | 12 antiquarian | 2014-06-07 | 2014-06-19 | 13 antiquarian | 2014-06-01 | 2014-06-05 | 5 antiquarian | 2014-05-25 | 2014-05-29 | 5 antiquarian | 2014-05-20 | 2014-05-22 | 3 Backup Date: Sun, 11 Jan 2015 03:33:17 GMT
-
@shadowmod monthposts
-
MonthPosts WITH exclusions AS ( /* Which categories to exclude from counters */ SELECT user_id, id, topic_id, post_number FROM posts WHERE raw LIKE '%[Magic Exclusion UUID]%' AND user_id IN ( SELECT gu.user_id FROM group_users gu WHERE group_id IN( SELECT g.id FROM groups g WHERE g.name IN ('admins') ) ) ), LastMonth AS ( /* Count eligible posts from last month */ SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*) FROM badge_posts bp JOIN users u on u.id=bp.user_id AND bp.user_id NOT IN ( /* ignore bots */ SELECT gu.user_id FROM group_users gu WHERE group_id IN( SELECT g.id FROM groups g WHERE g.name IN ('bots') ) ) WHERE topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month' GROUP BY u.username, bp.user_id HAVING count(*) > 1 ORDER BY count(*) DESC, bp.user_id ), TotalUsers AS ( SELECT max(row_number) from LastMonth ), QUERY AS ( SELECT username, row_number, cast(row_number*100.0/TotalUsers.max AS numeric(36,2)) as percent, count, CURRENT_DATE granted_at FROM LastMonth, TotalUsers WHERE cast(row_number*100.0/TotalUsers.max AS numeric(36,2)) <= 25) SELECT row_number as rank, username, percent, count FROM QUERY Backup Date: Sun, 11 Jan 2015 03:33:17 GMT
-
@accalia appears to be in no danger of losing her spoon.
-
Maybe maybe not. Last week i was at almost 1.6k posts.
:-)
-
@accalia appears to be in no danger of losing her spoon.
I'm not feeling motivated enough to try to take it back, and @boomzilla'd need to step up his game massively.
-
I'm not feeling motivated enough to try to take it back,
oh you're no fun anymore.
:-P
-
-
Nope. Too much effort to post that much.
i'm not even really trying!
no. i'm serious.
stop laughing!
-
I'm not feeling motivated enough to try to take it back, and @boomzilla'd need to step up his game massively.
My game is right where it belongs.
-
-
-
My game is right where it belongs.
Well, my post implied you'd actually want the spoon.
-
-
Well, my post implied you'd actually want the spoon.
I wouldn't turn it down or anything, but I'm trying not to post for the purpose of e-peenery.
-
-
And vice versa — I'm making very little progress on finishing my kids' overdue Christmas presents.
-
A barrier to post count.
Post count is overrated. [spoiler]Replies to a single post would be more interesting, and especially the number of unique people writing replies to that post. Now, I'm not saying that metric is ungameable, but it's quite a lot bigger challenge; it involves a lot more coöperation.[/spoiler]
-
huh... now there's an interesting idea....
@pjh would it be easy enough to find who has the most replied to post (excluding OP) for the purposes of awarding a badge?
-
select u.username, p.id, count(*) from post_replies pr join posts p on pr.post_id = p.id join users u on p.user_id = u.id where p.post_number > 1 group by u.username, p.id order by count(*) desc limit 10;
username | id | count -------------+-----+------- rikingtest2 | 42 | 4 riking | 46 | 2 riking | 41 | 1 riking | 183 | 1 riking | 181 | 1 riking | 184 | 1 riking | 178 | 1 loopback0 | 216 | 1 riking | 175 | 1 riking | 176 | 1
Should do it.
-
excellent!
now to see if PJH wishes to turn that into a badge.. :-D
-
@pjh would it be easy enough to find who has the most replied to post (excluding OP) for the purposes of awarding a badge?
not at a keyboard to go grubbing around the db schema, but my gut feeling is that this (sh|w)ould be fairly simple...
OP on a topic would have to be excluded for, hopefully, obvious reasons.
Suggestions, and reply counts (though I think I need to get our current metrics here,) for such a badge series welcome.
-
I also knocked up these while getting my head around the schema the other day, not sure how many are useful/interesting here but here goes.
[code]
--Who flags who
select u1.username flagger, u2.username flaggee, count() flags
from post_actions pa
join posts p on pa.post_id = p.id
join users u1 on pa.user_id = u1.id
join users u2 on p.user_id = u2.id
where pa.post_action_type_id = 7
group by u1.username, u2.username
order by count() desc
limit 10;
[/code]
[code]
--Top flaggers
select u.username, count() flags
from post_actions pa
join users u on pa.user_id = u.id
where pa.post_action_type_id = 7
group by u.username
order by count() desc
limit 25;
[/code]
[code]
--Most flagged
select u.username, count() flags
from post_actions pa
join posts p on pa.post_id = p.id
join users u on p.user_id = u.id
where pa.post_action_type_id = 7
group by u.username
order by count() desc
limit 25;
[/code]
[code]
--Who likes who
select u1.username liker, u2.username likee, count() likes
from user_actions ua
join users u1 on ua.user_id = u1.id
join posts p on ua.target_post_id = p.id
join users u2 on p.user_id = u2.id
where ua.action_type = 1
and ua.target_topic_id != 1000 /exclude specific topic/
group by u1.username, u2.username
order by count() desc
limit 25;
[/code]
[code]
--Top likers
select u.username, count() likes
from user_actions ua
join users u on ua.user_id = u.id
where ua.action_type = 1
and ua.target_topic_id != 1000 /exclude specific topic/
group by u.username
order by count() desc
limit 25;
[/code]
[code]
--Most liked
select u.username, count() likes
from user_actions ua
join posts p on ua.target_post_id = p.id
join users u on p.user_id = u.id
where ua.action_type = 1
and ua.target_topic_id != 1000 /exclude specific topic/
group by u.username
order by count() desc
limit 25;
[/code]
-
hmm... a series?
well i had only thought of mosst replied badge......
how about:
Most Replied Of All Time: obvious
Most Replied: Most replied post this month (if unique for preference)
Interesting Post: 10 replies
Curious Post: 25 replies
Engaging Post: 50 replies
-
Interesting Post: 10 replies
Curious Post: 25 replies
Engaging Post: 50 replies
/me approves
-
I was thinking of BSG like (!) the mediocre post badges.
-
yes. Interesting (10) is bronze
curious (25) is silver
engaging(50) is goldthe other two are "brown" like the spoon.
or at least that was my thought.
-
I'm not sure about the limits (hence metrics comment earlier) . Witness the current carnage we have with the defaults if one mistakenly posts to /1000...
-
Most Replied: Most replied post this month (if unique for preference)
Would that require the base post to be this month, or just the counted replies?
-
-
I think that 4 or 5 is a more appropriate threshold for marking a post as "interesting", as in, abnormally interesting. I just read through several large parts of older threads, and 4 replies was a fairly good indicator of a post that sparked replies.
There were only a few with 6-7.
Basically, I don't like that the default gold badge of 50 likes is practically unattainable and I'm complaining about it here. In the context of counting replies. Because that makes sense
.
EDIT: Here's a post that would have been badgered with a 5 replies threshold: http://what.thedailywtf.com/t/this-corporate-lodging-thread-has-been-derailed/7008/40?u=riking
-
I'm not sure about the limits (hence metrics comment earlier)
[postgres@sofa ~]$ LIMIT=70 sql_tdwtf most_replied SELECT COUNT(*) c, p.id, bp.reply_to_post_number, bp.topic_id, u.username FROM badge_posts bp JOIN posts p ON bp.reply_to_post_number=p.post_number and bp.topic_id=p.topic_id JOIN users u on u.id=p.user_id WHERE bp.reply_to_post_number IS NOT NULL AND bp.reply_to_post_number != 1 GROUP BY bp.reply_to_post_number, bp.topic_id, p.id, u.username ORDER BY c DESC LIMIT 70 c | id | reply_to_post_number | topic_id | username ----+--------+----------------------+----------+-------------------- 10 | 3475 | 298 | 344 | codinghorror 10 | 4261 | 74 | 407 | codinghorror 10 | 17539 | 343 | 1092 | dhromed 10 | 191045 | 15 | 6956 | blakeyrat 10 | 122644 | 7 | 4059 | Arantor 9 | 2822 | 4 | 365 | codinghorror 9 | 3753 | 77 | 365 | codinghorror 8 | 1538 | 221 | 238 | codinghorror 8 | 64232 | 15303 | 1000 | DoctorJones 8 | 78307 | 40 | 3098 | blakeyrat 8 | 59810 | 14230 | 1000 | mott555 7 | 110174 | 22409 | 1000 | HardwareGeek 7 | 111995 | 62 | 3790 | nullptr 7 | 134295 | 89 | 4359 | Maciejasjmj 7 | 76148 | 75 | 3032 | ScholRLEA 7 | 138687 | 40 | 4514 | redwizard 7 | 114766 | 36 | 3789 | chubertdev 7 | 159633 | 31 | 5135 | Yamikuronue 7 | 160224 | 30565 | 1000 | mott555 7 | 173403 | 34 | 5443 | mott555 7 | 194442 | 34 | 7031 | accalia 7 | 343 | 14 | 238 | morbiuswilters 7 | 190202 | 7171 | 1673 | blakeyrat 7 | 86618 | 64 | 3300 | Mason_Wheeler 7 | 137031 | 48 | 4346 | PJH 7 | 123172 | 3227 | 1673 | abarker 7 | 52877 | 12610 | 1000 | PJH 7 | 134346 | 918 | 3833 | Arantor 7 | 2631 | 144 | 344 | codinghorror 7 | 136005 | 97 | 4417 | codinghorror 7 | 11020 | 113 | 893 | mott555 7 | 194802 | 1405 | 2628 | nightware 7 | 176358 | 15 | 5538 | codinghorror 7 | 74785 | 555 | 2914 | sam 7 | 188113 | 20 | 6879 | PJH 7 | 21717 | 1996 | 1000 | codinghorror 7 | 34013 | 4985 | 1000 | Arantor 7 | 72672 | 22 | 2932 | BC_Programmer 7 | 56973 | 13661 | 1000 | abarker 7 | 194550 | 70 | 6911 | flabdablet 6 | 156807 | 69 | 4985 | VaelynPhi 6 | 199535 | 66 | 7142 | anonymous234 6 | 77783 | 68 | 3096 | ben_lubar 6 | 44010 | 61 | 1870 | Groaner 6 | 195880 | 10 | 7069 | blakeyrat 6 | 156127 | 11 | 5038 | cartman82 6 | 192092 | 66 | 6956 | CoyneTheDup 6 | 3028 | 56 | 260 | moderator 6 | 2453 | 48 | 344 | codinghorror 6 | 181245 | 44 | 5610 | aliceif 6 | 2466 | 56 | 344 | codinghorror 6 | 112874 | 8 | 3833 | darkmatter 6 | 47427 | 10 | 1970 | Kuro 6 | 78145 | 51 | 3103 | sam 6 | 27652 | 13 | 1504 | cartman82 6 | 703 | 9 | 270 | apapadimoulis 6 | 11291 | 2 | 926 | codinghorror 6 | 120661 | 10 | 4006 | blakeyrat 6 | 11896 | 41 | 936 | dhromed 6 | 108330 | 2 | 3699 | presidentsdaughter 6 | 151099 | 58 | 4886 | blakeyrat 6 | 100548 | 3 | 3596 | aliceif 6 | 137911 | 7 | 4512 | anonymous234 6 | 165823 | 35 | 5287 | PJH 6 | 186036 | 34 | 6777 | flabdablet 6 | 17362 | 7 | 1165 | sam 6 | 58126 | 3 | 2251 | DoctorJones 6 | 74068 | 29 | 2953 | Keith 6 | 163292 | 6 | 5243 | accalia 6 | 135740 | 29 | 4426 | cartman82 (70 rows) Elapsed: 2.133s Backup taken: 2015-01-19 03:56:06.53156 [postgres@sofa ~]$
```select u.username, p.id, count() from post_replies pr
join posts p on pr.post_id = p.id
join users u on p.user_id = u.id
where p.post_number > 1
group by u.username, p.id
order by count() desc
limit 10;The problem with that one is it's including stuff like PM's and hidden categories like:
[postgres@sofa ~]$ LIMIT=70 sql_tdwtf most_replied2
SELECT COUNT(), p.id, u.username
FROM post_replies pr
JOIN posts p ON pr.post_id = p.id
JOIN users u ON p.user_id = u.id
WHERE p.post_number > 1
GROUP BY u.username, p.id
ORDER BY count() desc
limit 10;count | id | username
-------+--------+--------------
23 | 150685 | Zoidberg
17 | 39219 | Matches
16 | 39220 | Matches
15 | 39221 | Matches
15 | 35445 | abarker
14 | 39223 | Matches
13 | 39224 | Matches
12 | 4261 | codinghorror
12 | 39226 | Matches
11 | 150566 | Jaloopa
(10 rows)Elapsed: 2.12s
Backup taken: 2015-01-19 03:56:06.53156Note also a rather interesting discrepancy with (e.g.) /p/4261 between the two methods...
-
possibly we should make it unique replies? most of @Zoidberg's replies is @accalia anonymized through @sockbot...
23 | 150685 | Zoidberg
http://what.thedailywtf.com/t/will-sockbot-summon-zoidberg/4896/43
-
Hooray! People are paying attention to me!
-
@accalia has summoned me, and so I appear.
-
Note also a rather interesting discrepancy with (e.g.) /p/4261 between the two methods...
[postgres@sofa ~]$ psql -d discourse -c "SELECT id, post_number FROM badge_posts WHERE reply_to_post_number=298 AND topic_id=344 > ORDER BY id" id | post_number ------+------------- 3480 | 299 3482 | 300 3525 | 317 3539 | 320 3540 | 321 3541 | 322 3552 | 324 3559 | 327 3583 | 336 3650 | 360 (10 rows) [postgres@sofa ~]$
[postgres@sofa ~]$ psql -d discourse -c "SELECT post_id, reply_id FROM post_replies WHERE post_id=3475 > ORDER BY reply_id ASC" post_id | reply_id ---------+---------- 3475 | 3480 3475 | 3482 3475 | 3494 3475 | 3525 3475 | 3539 3475 | 3540 3475 | 3541 3475 | 3552 3475 | 3559 3475 | 3583 3475 | 3650 (11 rows) [postgres@sofa ~]$
-
-
The problem with that one is it's including stuff like PM's and hidden categorie
I figured it'd need some tweaking. Had missed badge_posts.
-
Which should count, yes?
-
But don't appear in
badge_posts
it appears.But there's still a discrepancy -
/p/3475
has - in fact - 12 replies if you go look at it.badge_posts
has 10,post_replies
has 11. Where is DC getting 12 from?
-
Where is DC getting 12 from
where are you getting 12 from?
EDIT: i also count 11 posts appearing in the dropdown
-
Hmm. I was positive that was 12 when I looked at it
last nightearly this morning...
-
-
Lack of sleep probably - see edit.
-
-
Grubbing around SQL on here while 1/2 watching Criminal Minds on the TV.