-NaN% posters and hamster smurfing!
-
There's a reason I limited it to 30 days
-
Why is that? I really want to see a graph of my full posting history. Each day is what, 20 bytes? That's not a lot.
-
You'd be happy with a graph that has 300 x-axis items?
-
It's basically a line graph that uses more ink at that point, but yeah.
-
Submit a PR then and i'll review it. You know where the code lives. :-D
-
-
I was thinking more you'd copy postdays into a new query called postalldays?
-
I'm accepting pull requests on my pull request!
-
Except mine's forked from @accalia's
-
hmm... looks good. @PJH any objections to removing the 30 day limit for the PostDays query?
I'm assuming not, but Shadowmod is your bot not mine, so best to check. ;-)
-
- PostMonth (30 days)
- PostYear (365, not 356)
- PostAll (we're getting close if you ignore the test stuff before May 20)
Or make it a take parameter (month, year, all)
There's also whether the year/all should optionally split by day or month.
Basically, I'd prefer some thought go into it rather than have the matrix of queries grow organically.
-
Basically, I'd prefer some thought go into it rather than have the matrix of queries grow organically.
fair enough.
in that case i'll have to add it as a TODO.
@TODO $enhancement improve statsporn module to have a more sensible query matrix.
-
Todo d2b995a2, (accalia:enhancement) improve statsporn module to have a more sensible query matrix.
-
And I'm out of the club again.
-
... I think I accidentally took your badge.
-
@shadowmod monthposts
-
No hard feelings. I have other things to do that are more important than posting here, like looking for a job; I really shouldn't be anywhere near the top group of posters.
Filed under: Life — I think I used to know what that word meant. Once, long ago.
-
MonthPosts Backup Date: 2015-03-16 03:58:53
Query
``` 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</details> [<img src="https://plot.ly/~shadowmod/309.svg" height="500" width="700" /><br/>Click for interactive graph.](https://plot.ly/~shadowmod/309) <!--​ Posted by SockBot 0.16 "Hazardous Hera" on Mon, 16 Mar 2015 20:22:16 GMT-->
-
All that graph's doing is making me sad that I'm so loud…
-
Are you coming for mine next?
-
Probably not.
I often have near-silent days.
-
449 HardwareGeek 4.70% poster 449 aliceif 5.13% poster
Oh my god.
-
Me too, probably will over the next couple of days.
-
According to that, @aliceif and I are dead-even tied for number of posts, but the percentile ranking is different, with one of us over and one of us under the magic 5% boundary, and it's not clear to me whether it's deterministic which is which. The ordering the opposite of the current badge status.
And Hanzo'd.
-
Only the count is used to order, but the username is in the
group by
part of the query, and that may cause some ordering (as an implementation detail).
-
The @shadowmod query orders by posts then by user ID; it's not unreasonable that the real badger query doesn't do the by-user-ID part, hence the different result
Paging @accalia and @PJH to confirm…
@boomzilla said:Only the count is used to order
Sure about that?
@shadowmod said:ORDER BY count(*) DESC, bp.user_id
-
Well, it's the case for the 2nposts query. Which was the one I looked at.
:whistlesnonchalontly:
-
it's not unreasonable that the real badger query doesn't do the by-user-ID part, hence the different result
Only orders by post count, assigns a row number to the result and does a percentage based on the resultant row number. Implementation defined as to what happens if two users have the exact same post count.
-
-
After I've run the stats tomorrow, I'll know…
-
does a percentage based on the resultant row number. Implementation defined as to what happens if two users have the exact same post count.
So users with the same post count will indeed get different badges if the rows straddle the boundary, and the row ordering for equal post count is based on bp.user_id, at least in that snippet, so deterministically favoring the earlier user.
-
That ... would be you, though.
-
Yes; however, we may not have been tied when the badge query was run.
-
All that graph's doing is making me sad that I'm so loud…
That's a big gap behind you. And that's a log chart, too.
-
That's a big gap behind you.
Tell me about it…
@abarker said:And that's a log chart, too.
That's what makes it so scary!…
…and I've just added to the tally.
-
-
-
yep. and you know it!
-
Maybe a challenge would help.
@RaceProUK: I hereby challenge you to only post in excluded topics for 24 hours. Begin whenever you wish, so long as you notify at least 3 other members of the forum that you are beginning the challenge. I suggest that you start at a time when shadowmod obtains a new backup for easy verification that you have successfully completed the challenge.
-
What? This topic is actually active?
Well then, since I am a member of The Lifeless, it is tracked again!
Yes, you're allowed to react with "Oh, shit...". I won't be angry.
-
@todo $CHALLENGE Only post in excluded topics for 24 hours. Begin whenever I wish, so long as I notify at least 3 other members of the forum that I am beginning the challenge. I should probably start at a time when shadowmod obtains a new backup for easy verification that I have successfully completed the challenge.
-
Todo 7911bdac, (RaceProUK:CHALLENGE) Only post in excluded topics for 24 hours. Begin whenever I wish, so long as I notify at least 3 other members of the forum that I am beginning the challenge. I should probably start at a time when shadowmod obtains a new backup for easy verification that I have successfully completed the challenge.
-
If he fails maybe we could Jeff 24 hours' worth of his posts into an excluded category?
-
That's evil.
I like it.
-
Wait. Shit. Possible loophole. Hats, we need to speak.
-
Maybe a challenge would help.
@RaceProUK: I hereby challenge you to only post in excluded topics for 24 hours. Begin whenever you wish, so long as you notify at least 3 other members of the forum that you are beginning the challenge. I suggest that you start at a time when shadowmod obtains a new backup for easy verification that you have successfully completed the challenge.
Additional condition: the FIA WEC Silverstone weekend (12-14 April) doesn't count. I won't be that active that weekend (will be busy watching cars go vroom), so it'd be a cheat to do the challenge then.
-
I should probably start at a time when shadowmod obtains a new backup for easy verification that I have successfully completed the challenge.
If you're aiming for a zero height bar on
postdays
, then it really needs to be 0000UTC-0000UTC, with the results available around 0420UTC.
-
@RaceProUK should probably add 12 hours each side, y'know, just to be sure. Don't wanna get caught out by Discoroundingâ„¢. .
-
Only twelve?
-
If you're aiming for a zero height bar on
postdays
, then it really needs to be 0000UTC-0000UTC, with the results available around 0420UTC.Good point. @RaceProUK, consider my advice amended according to @PJH's input.