-NaN% posters and hamster smurfing!
-
The most amusement I had at the whole endeavour was the video of the CEO doing the ice bucket challenge.
-
I think it's one of the ones HJP knocked up a while back and posted the results to.
Faking the mention so I don't get a notification doesn't really work when you're going to link to one of my posts anyway...
WITH exclusions AS ( /* Which categories to exclude from counters */ SELECT user_id, id, topic_id, post_number FROM posts WHERE raw LIKE '%[UUID removed to prevent this thread accidentally being marked as excluded]%' 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 DATE_TRUNC('day', bp.created_at) , count(bp.id) FROM badge_posts bp JOIN users u ON bp.user_id = u.id WHERE bp.created_at > '01/01/14' AND bp.topic_id NOT IN ( /* Excluded topics */ SELECT topic_id FROM exclusions ) AND u.username LIKE 'PJH' GROUP BY DATE_TRUNC('day', bp.created_at) ORDER BY DATE_TRUNC('day', bp.created_at) ASC date_trunc | count ---------------------+------- 2014-05-20 00:00:00 | 18 2014-05-21 00:00:00 | 48 2014-05-22 00:00:00 | 19 2014-05-23 00:00:00 | 25 <229 lines snipped> 2015-01-21 00:00:00 | 43 2015-01-22 00:00:00 | 30 2015-01-23 00:00:00 | 16 2015-01-24 00:00:00 | 12 2015-01-25 00:00:00 | 2 (238 rows) Elapsed: 0.595s Backup taken: 2015-01-26 09:08:30.205981
and with hindsight, I don't think that first
WHERE
clause is actually necessary.
-
and with hindsight, I don't think that first WHERE clause is actually necessary.
a LIMIT clause might be though. ;-)
-
Faking the mention so I don't get a notification doesn't really work when you're going to link to one of my posts anyway
Was just carrying on from referring you as HJP in another topic, wasn't actually hiding the notification.
-
Digging the new avatar. ;)
-
nice avatar @PJH
now we just need to get @boomzilla to change and we'll have all the active staff covered!
-
Needs descending sort and limit, or ~last month only. But looks nice.
Could be nice to graph it as well but that would probably require returning output rows for days on which no posts were made. I think I saw @riking post something with counters at some point though?
-
```
WITH exclusions AS ( /* Which categories to exclude from counters /
SELECT user_id, id, topic_id, post_number
FROM posts
WHERE raw LIKE '%[UUID removed to prevent this thread accidentally being marked as excluded]%' 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 DATE_TRUNC('day', bp.created_at) , count(bp.id)
FROM badge_posts bp
JOIN users u ON bp.user_id = u.id
AND bp.topic_id NOT IN ( / Excluded topics */
SELECT topic_id
FROM exclusions
)
AND u.username ILIKE $1::varchar
GROUP BY DATE_TRUNC('day', bp.created_at)
ORDER BY DATE_TRUNC('day', bp.created_at) DESC
LIMIT $2::intturned that into a prepared statement and reversed sort order. not sure how to pad that with 0's for days with no posts (not that i could be used to test that ;-))
-
If you had a list of days you could use an outer join and whatever the equivalent of NVL is in Postgres.
Something like this, as a sub-query outer joined to the query as it is now.
select to_date('01/01/2014','DD/MM/YYYY;)+rownum from badge_posts where rownum < sysdate-to_date('01/01/2014','DD/MM/YYYY')
It's a safe bet the number of rows in badge_posts will always be more than days since the start of the forum.
That'd probably work in Oracle, might need tweaking for Postgres but the idea is there.
-
WTF does Postgres require a bloody OVER clause for row_number().
-
And not allow row_number in the WHERE clause.
Where's the I-hate-Postgres club?!
-
down the hall, first left after the I hate Oracle club.
-
Is that next to the I-hate-MySQL club?
-
-
Ahhh, yes. And I suspect I-hate-MSSQL is down the hall, yes?
-
Ahhh, yes. And I suspect I-hate-MSSQL is down the hall, yes?
yes, just past the bit where gravity suddenly reverses.
mind your step if you go that way.
-
This gets you a list of dates from the start date (01/01/2015 here) to now in Postgres.
select date from (select to_date('01/01/2015','DD/MM/YYYY')+(row_number() over())::integer date, (row_number() over()) rn from badge_posts) list where (rn < to_char(date_trunc('day',now()-to_date('01/01/2015','DD/MM/YYYY')),'DD')::integer)
Outer join to the other queries, use COALESCE to insert 0 where the row is null for the column which comes from the badge_posts query.
-
mind your step if you go that way.
Definitely watch out for the people in the I-Love-PHP-Club.
Proper weirdos.
-
-
I'm not risking biscuits made by someone who loves PHP.
-
-
These were tasty.
-
Ahhh, yes. And I suspect I-hate-MSSQL is down the hall, yes?
There are two kinds of RDBMS: the kind people complain about and the kind no one uses.
-
There are two kinds of
RDBMSsoftware: the kind people complain about and the kind no one uses.FTFY
-
These were tasty.
Most people don't literally mean dogfood when they talk about dogfooding.
-
Most people don't literally mean dogfood when they talk about dogfooding.
What? OOoops!
-
@dkf Is Doing It Wrongโข
-
Most people don't literally mean dogfood when they talk about dogfooding.
Bonios are tasty too, but not as tasty as the German-recipe cookies I baked for Christmas (picture above)
-
-
/me switches to american dictionary
cookies of course.
although a nice savory biscuit is good too.
-
Congratulations on taking the wooden spoon @boomzilla
well you haven't yet, but i just got back into the 5% club so it's only a matter of time till that badger query runs and you get the spoon.
-
@statsbot monthposts
-
don't you mean: @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-20 03:59:04
-
currently 85 posts/month will get you into the 25% club, but it tales over 1.4k to get the wooden spoon.
dang that's a spread.
-
Well, @boomzilla gets a a few of his posts by doing his job.
-
and i've been less chattery in areas that are eligible
that reminds me i do want to create a query for monthposts that doesn't have those exclusions and see what happens. ;-)
-
Well, @boomzilla gets a a few of his posts by doing his job.
@blakeyrat doesn't think so.
-
yes, but what else is new?
;-)
-
@shadowmod postmonths
-
PostMonths loopback0 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-21 03:58:30
-
On track for December's performance, might fall out of the 5% at that rate.
-
@shadowmod postmonths
-
PostMonths aliceif 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-21 03:58:30
-
@shadowmod postmonths
-
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-21 03:58:30
-
2, 6, 17, 62, 132, 202, 383, 425, 642
And there's still a week to goโฆ I seriously think I'll break 800, maybe even 900โฆ
Probably not 1,000 though; it'll be a while until I get to @accalia levels of posting
-
@shadowmod postmonths
-
PostMonths tar 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-21 03:58:30
-
I'm not 100% sure I understand the X axis there...
:/