1. Home
  2. Categories
  3. Meta
  4. -NaN% posters and hamster smurfing!

-NaN% posters and hamster smurfing!

  • accalia
    FoxDev

    huh... plot.ly strikes again. :-D

    3
  • dkf
    Discourse touched me in a no-no place

    @shadowmod postmonths

    0 shadowmod riking 2 Replies
  • shadowmod 

    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


    Click for interactive graph.

    0
  • Luhmann
    BINNED

    @shadowmod postmonths

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0
  • Luhmann
    BINNED

    Doubt I'll see any 'improvement' before May. March will be busy. Got some days of in April ...

    0
  • PleegWat
    Java Dev

    @shadowmod postmonths

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0
  • Arantor

    Working is a barrier to browsing, let alone posting.

    3 loopback0 1 Reply
  • loopback0
    Discourse touched me in a no-no place

    Doing It Wrong™

    0 Arantor 1 Reply
  • Arantor

    I write PHP for a living, what part of my life is doing it right? *snigger*

    5 loopback0 1 Reply
  • loopback0
    Discourse touched me in a no-no place

    Touché,

    0 Arantor 1 Reply
  • Arantor

    @loopback0 said:

    Touchéè,

    TDWTFTFY

    3
  • abarker

    @shadowmod postmonths

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0 abarker 1 Reply
  • abarker

    On track to match last August. I'm slipping.

    0
  • FrostCat
    Discourse touched me in a no-no place

    @shadowmod postmonths

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0 FrostCat 1 Reply
  • FrostCat
    Discourse touched me in a no-no place

    Well, that seems suspicious.

    @shadowmod monthposts

    0 accalia 1 Reply
  • accalia
    FoxDev

    cooldown.

    @shadowmod monthposts

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0
  • ChaosTheEternal

    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:


    Click for interactive graph.

    1
  • tar

    I'm wondering how long my reign as most-chatty-sub-1000-posts user will continue....

    0
  • RaceProUK
    FoxDev

    @shadowmod postmonths

    I blame @accalia

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0
  • Jaloopa
    kills Dumbledore

    @shadowmod postmonths

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0
  • abarker

    @shadowmod postmonths

    0 shadowmod accalia HardwareGeek 3 Replies
  • shadowmod 

    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


    Click for interactive graph.

    0 abarker 1 Reply
  • abarker

    Yep, still on track to match last August.

    0
  • accalia
    FoxDev

    dang that's a popular query. :-D

    0 tar 1 Reply
  • tar

    It's probably just 'cause it's new—everyone wants to see what theirs looks like...

    0 accalia 1 Reply
  • accalia
    FoxDev

    true. i'll have to come up with a new query soon. ;-)

    0
  • abarker

    @shadowmod 2nposts

    0 shadowmod 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0 abarker 1 Reply
  • abarker

    Based on schedule, refresh in about 2 hours ...

    0 accalia 1 Reply
  • HardwareGeek

    @RaceProUK said:

    2, 6, 17, 62, 132, 202, 383, 425, 642

    Almost an x3 growth rate. It won't take as long as you think. :)

    1
  • accalia
    FoxDev

    yep. and unless i mis my guess i'll be joining 2^13 today. :-P

    @shadowmod attendance

    0 shadowmod abarker 2 Replies
  • shadowmod 

    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
    0
  • abarker

    I believe I'll be making the jump to 2^12. But if not, I win our bet. So, win-win for me. :)

    0 accalia 1 Reply
  • HardwareGeek

    @shadowmod postmonths

    0 shadowmod 1 Reply
  • accalia
    FoxDev

    i forget, what was our wager?

    0 abarker 1 Reply
  • shadowmod 

    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


    Click for interactive graph.

    0
  • abarker

    Really just who would make the next 2^n first. We never did attach a specific wager, that I recall.

    0 accalia 1 Reply
  • accalia
    FoxDev

    ohh.

    well you have an advantage on the next one. i have twice as far to go than you do.

    :-P

    0 abarker 1 Reply
  • abarker

    @accalia said:

    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!

    0 accalia 1 Reply
  • accalia
    FoxDev

    hey. with all the flamewars i've been avoiding around here you can pick up steam real easy!

    :-P

    0 abarker 1 Reply
  • abarker

    @accalia said:

    hey. with all the flamewars i've been avoiding around here you can pick up steam real easy!

    And get embroiled myself? No thanks!

    0 accalia 1 Reply
  • accalia
    FoxDev

    hmm... fair enough, but that is how @boomzilla managed to take the spoon for a day!

    0 abarker 1 Reply
  • abarker

    @accalia said:

    hmm... fair enough, but that is how @boomzilla managed to take the spoon for a day!

    TBF, he was pretty close already.

    0 accalia 1 Reply
Log in to reply