-NaN% posters and hamster smurfing!
-
huh... plot.ly strikes again. :-D
-
@shadowmod postmonths
-
PostMonths dkf 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-22 03:59:46
-
@shadowmod postmonths
-
PostMonths Luhmann 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-22 03:59:46
-
Doubt I'll see any 'improvement' before May. March will be busy. Got some days of in April ...
-
@shadowmod postmonths
-
PostMonths PleegWat 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-22 03:59:46
-
Working is a barrier to browsing, let alone posting.
-
Doing It Wrong™
-
I write PHP for a living, what part of my life is doing it right? *snigger*
-
Touché,
-
-
@shadowmod postmonths
-
PostMonths abarker 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-22 03:59:46
-
On track to match last August. I'm slipping.
-
@shadowmod postmonths
-
PostMonths FrostCat 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-24 03:54:59
-
Well, that seems suspicious.
@shadowmod monthposts
-
cooldown.
@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: 2015-02-24 03:54:59
-
I did mine in a PM, but mine is a bit amusing given my tendency to want to lurk, mostly post in the restricted threads, and recent lack of time/desire to post:
-
I'm wondering how long my reign as most-chatty-sub-1000-posts user will continue....
-
@shadowmod postmonths
I blame @accalia
-
PostMonths RaceProUK 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-24 03:54:59
-
@shadowmod postmonths
-
PostMonths Jaloopa 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-24 03:54:59
-
@shadowmod postmonths
-
PostMonths abarker 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-24 03:54:59
-
Yep, still on track to match last August.
-
dang that's a popular query. :-D
-
It's probably just 'cause it's new—everyone wants to see what theirs looks like...
-
true. i'll have to come up with a new query soon. ;-)
-
@shadowmod 2nposts
-
2nPosts % 50 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') ) ) ) SELECT u.username, count(*) AS posts, log(2.0, count(*)) AS log2posts FROM badge_posts bp JOIN users u on u.id=bp.user_id AND u.username ILIKE $1::varchar WHERE topic_id NOT IN ( /* Topics with less than 10 posts */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) < 10 ) AND topic_id NOT IN ( /* Excluded topics */ SELECT topic_id FROM exclusions ) AND u.username ILIKE $1::varchar GROUP BY u.username ORDER BY count(*) DESC LIMIT $2::int Backup Date: 2015-02-24 03:54:59
-
Based on schedule, refresh in about 2 hours ...
-
2, 6, 17, 62, 132, 202, 383, 425, 642
Almost an x3 growth rate. It won't take as long as you think. :)
-
yep. and unless i mis my guess i'll be joining 2^13 today. :-P
@shadowmod attendance
-
Attendance accalia 25 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 accalia | 2014-08-22 | 2015-02-24 | 186 accalia | 2014-06-16 | 2014-06-17 | 2 accalia | 2014-06-03 | 2014-06-04 | 2 accalia | 2014-05-29 | 2014-05-29 | 1 Backup Date: 2015-02-24 03:54:59
-
I believe I'll be making the jump to 2^12. But if not, I win our bet. So, win-win for me. :)
-
@shadowmod postmonths
-
i forget, what was our wager?
-
PostMonths HardwareGeek 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') ) ) ) SELECT count(*) AS posts, to_char(created_at,'YYYY-MM') AS month FROM badge_posts bp WHERE bp.topic_id NOT IN ( /* short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND bp.topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND bp.user_id in (SELECT id FROM users WHERE username ILIKE $1::varchar) GROUP BY to_char(created_at,'YYYY-MM') ORDER BY to_char(created_at,'YYYY-MM') Backup Date: 2015-02-24 03:54:59
-
Really just who would make the next 2^n first. We never did attach a specific wager, that I recall.
-
ohh.
well you have an advantage on the next one. i have twice as far to go than you do.
:-P
-
well you have an advantage on the next one. i have twice as far to go than you do.
Yeah, but look at the discrepancy in our post rates. I don't stand a chance!
-
hey. with all the flamewars i've been avoiding around here you can pick up steam real easy!
:-P
-
hey. with all the flamewars i've been avoiding around here you can pick up steam real easy!
And get embroiled myself? No thanks!
-
hmm... fair enough, but that is how @boomzilla managed to take the spoon for a day!
-
hmm... fair enough, but that is how @boomzilla managed to take the spoon for a day!
TBF, he was pretty close already.