Necro Badge Proposal


  • sockdevs

    hmm.....

    i wonder if we could get a badge for necroing a topic.

    something like necroraise a topic that remains active for a week after being raised or something.



  • That'd be a hell of a query if it's even possible.


  • sockdevs

    i might try to figure it out if i get bored.


  • Winner of the 2016 Presidential Election

    @accalia said:

    i wonder if we could get a badge for necroing a topic.

    Evil ideas thread is that :arrow_down: :rewind: :fa_xing: :repeat: :fa_arrows: way.

    Speaking of, I have one of my own but... I'll keep that one to myself :imp:



  • select p2.user_id, p2.created_at granted_at, p2.id post_id
    from badge_posts p1, badge_posts p2
    where p1.post_number + 1 = p2.post_number
    and p1.topic_id = p2.topic_id
    and p1.created_at + INTERVAL('2 month') < p2.created_at
    


  • Thought it'd be more difficult than that :laughing:

    Don't you need p1.topic_id = p2.topic_id (or whatever the field is called)?



  • @accalia said:

    if i get bored.

    and when you are not posting?


  • sockdevs

    Well, yes. Posting is a barrier to working on SQL


  • Discourse touched me in a no-no place

    [postgres@sofa ~]$ sql_tdwtf necro
    
            select p2.user_id, p2.created_at granted_at, p2.id post_id
            from badge_posts p1, badge_posts p2
            where p1.post_number + 1 = p2.post_number
            and p1.topic_id = p2.topic_id
            and p1.created_at + INTERVAL '2 months' < p2.created_at
    
    
     user_id |         granted_at         | post_id 
    ---------+----------------------------+---------
          69 | 2014-09-13 16:48:25.085355 |   80827
         665 | 2015-01-27 21:19:25.870408 |  211798
          51 | 2015-01-13 15:35:05.137474 |  195522
         110 | 2014-10-22 04:39:26.713152 |  129493
         558 | 2014-11-24 05:41:57.79345  |  158726
         549 | 2014-10-06 14:04:42.409033 |  110384
         823 | 2014-09-03 19:02:18.611965 |   72025
        1758 | 2014-12-13 22:15:36.681074 |  172559
         558 | 2014-08-25 22:13:09.330814 |   65894
         123 | 2014-12-18 19:30:59.012104 |  177104
         303 | 2014-10-13 11:31:49.389184 |  118348
          30 | 2014-10-07 08:06:11.425846 |  111267
         603 | 2014-10-10 17:56:37.157735 |  116614
         561 | 2015-02-18 01:55:48.978158 |  237022
          69 | 2014-10-31 17:36:23.98186  |  140022
         110 | 2014-10-14 17:57:23.568361 |  119405
         561 | 2015-02-07 22:20:23.853022 |  226272
         133 | 2014-11-28 15:27:05.564285 |  161505
         598 | 2015-02-18 19:59:06.39806  |  238112
         671 | 2015-01-05 21:59:37.239436 |  187749
         883 | 2015-02-02 21:35:12.339444 |  219324
         922 | 2014-08-19 11:21:31.195436 |   59487
        1096 | 2014-09-17 21:47:10.413347 |   85222
         242 | 2014-10-27 13:50:25.201552 |  134573
          43 | 2014-12-24 01:51:16.409065 |  180941
        1096 | 2014-09-19 15:42:32.316533 |   88093
         581 | 2014-07-28 13:37:14.959117 |   44293
         558 | 2014-12-24 01:55:15.787767 |  180945
         824 | 2014-10-29 17:23:28.525684 |  137348
         110 | 2014-10-11 05:17:39.830951 |  117066
          43 | 2014-10-29 01:37:21.498483 |  136574
          43 | 2014-11-07 03:51:13.741915 |  144875
         689 | 2015-01-07 09:05:11.91973  |  189185
         123 | 2014-12-17 18:54:54.468511 |  175991
         671 | 2014-12-29 19:21:53.87424  |  183807
         304 | 2014-11-24 13:58:40.65091  |  158830
        1758 | 2014-12-14 03:39:38.708374 |  172620
         561 | 2015-01-20 10:01:33.75537  |  201898
         587 | 2014-11-07 15:12:27.882268 |  145265
         598 | 2014-11-18 16:17:22.48841  |  154294
         561 | 2014-10-29 02:57:00.951483 |  136688
         561 | 2015-01-12 08:49:43.700048 |  194335
          20 | 2015-02-13 15:50:32.498873 |  233038
        1513 | 2014-10-28 19:28:38.106737 |  136037
         601 | 2015-01-21 04:38:13.260443 |  203282
         676 | 2014-11-22 03:47:09.478429 |  158228
         110 | 2014-09-18 17:44:16.143776 |   86546
         561 | 2015-02-18 00:58:19.407358 |  236977
         922 | 2015-01-15 11:21:17.081875 |  197409
          20 | 2014-10-24 13:20:59.581604 |  132598
         549 | 2015-01-28 20:44:24.213972 |  213432
        1767 | 2014-12-16 23:16:21.138058 |  175166
         617 | 2014-09-10 12:12:17.965323 |   77382
          18 | 2014-10-02 11:30:41.037324 |  105244
        1125 | 2015-01-22 07:50:36.573104 |  204886
         123 | 2014-10-01 00:45:28.829016 |  100169
         940 | 2014-12-01 14:40:53.46301  |  162329
         643 | 2014-08-23 18:45:23.52024  |   64721
          18 | 2015-01-15 15:33:20.532036 |  197697
         883 | 2014-10-30 21:58:41.827395 |  139057
         762 | 2014-11-18 14:48:37.099017 |  154147
         762 | 2015-01-24 12:39:21.658277 |  208071
         824 | 2014-10-27 19:43:01.264167 |  135039
          20 | 2015-01-27 14:27:18.957494 |  210873
         234 | 2015-01-31 00:28:45.778263 |  216840
        1842 | 2015-01-15 06:47:07.082174 |  197332
         561 | 2015-02-11 19:41:52.231166 |  230818
         110 | 2014-10-30 01:10:29.607576 |  137985
         561 | 2015-02-06 06:21:00.98865  |  223995
         922 | 2015-01-31 18:57:35.976538 |  217347
          18 | 2014-09-09 22:22:50.494868 |   77021
         681 | 2014-11-27 20:19:17.210515 |  161333
         617 | 2015-01-22 11:59:40.928611 |  204999
         304 | 2014-12-24 19:21:46.269047 |  181252
         643 | 2014-08-21 18:54:32.371527 |   62870
         561 | 2015-02-19 10:14:02.127202 |  238674
         304 | 2014-10-03 17:34:29.833232 |  108850
          18 | 2015-02-18 13:37:04.662183 |  237374
        1096 | 2014-09-19 19:16:37.186707 |   88803
          18 | 2014-09-01 21:09:28.485907 |   70487
        1688 | 2015-01-15 17:21:30.211583 |  197828
         823 | 2015-01-23 22:54:31.569417 |  207610
        1323 | 2014-12-17 23:15:10.855775 |  176282
          44 | 2015-02-22 00:46:03.970726 |  242119
          18 | 2014-12-05 21:26:15.412255 |  166359
          43 | 2014-12-15 22:01:18.843108 |  173863
        1758 | 2014-12-13 21:16:24.174038 |  172549
         598 | 2014-10-30 14:49:17.20827  |  138424
        1707 | 2014-12-01 19:05:14.807859 |  162599
    (89 rows)
    
    Elapsed: 4.021s
    Backup taken:  2015-02-22 03:59:46.642075
    

  • Discourse touched me in a no-no place

    @PJH said:

    Elapsed: 4.021s

    So… no more than a daily job then?


  • sockdevs

    I see no possible way to abuse that query for free badgers

    ;-)


  • Discourse touched me in a no-no place

    @dkf said:

    So… no more than a daily job then?

    Given the choices I have, correct...



  • @riking said:

    ```
    select p2.user_id, p2.created_at granted_at, p2.id post_id
    from badge_posts p1, badge_posts p2
    where p1.post_number + 1 = p2.post_number
    and p1.topic_id = p2.topic_id
    and p1.created_at + INTERVAL('2 month') < p2.created_at

    
    That only works for the necro itself though. When awarding badges for necro, you want the topic to somehow acctually reactivate (say, at least 10 posts from 3 users over the next week?)

  • mod

    @accalia said:

    I see no possible way to abuse that query for free badgers

    ;-)

    TL4+ have an advantage ...


  • sockdevs

    @abarker said:

    TL4+ have an advantage ...

    you have my attention. :stuck_out_tongue:


  • mod

    @accalia said:

    you have my attention. :stuck_out_tongue:

    Two words: closed topics.


  • sockdevs

    but that will just bump the topic. as proposed the topic has to stay active.



  • @riking's badge doesn't take that into account. :laughing:


  • mod

    @accalia said:

    but that will just bump the topic. as proposed the topic has to stay active.

    You're assuming that multiple TL4+ don't work together to get it. Also:

    @loopback0 said:

    @riking's badge doesn't take that into account.



  • You could have a second badge which was granted to someone when the topic stayed active for X days.


  • area_deu

    @abarker said:

    work together

    A badge-acquiring conspiracy?
    I :heart: that idea!



  • This shit again.


  • Winner of the 2016 Presidential Election

    Ah, I see, @abarker reacting and moving stuff within a minute.

    And @boomzilla is not doing his job, again!


  • mod

    TBH, I didn't realize it was in the wrong topic until @blakeyrat showed up.


  • Winner of the 2016 Presidential Election

    Give blakey a janitor badge!


  • mod

    I have none of the necessary powers. I can neither create nor assign such a badge. :(



  • Maybe @blakeyrant should get TL4 as an experiment as to whether he'd actually move posts or just bitch about it some more.

    That said, I think we know the answer.


  • Winner of the 2016 Presidential Election

    @blakeyrat said:

    This shit again.

    And here I thought you were ignoring this category because it hurts your soul to be party to gamification in a community.


  • Discourse touched me in a no-no place

    @loopback0 said:

    @riking's badge doesn't take that into account.

    I'll investigate tomorrow, but I suspect since the current query is already at 4 seconds, it won't be cheap...

    @Arantor said:

    And here I thought you were ignoring this category because it hurts your soul to be party to gamification in a community.

    http://what.thedailywtf.com/t/the-little-blue-circles-with-the-numbers/3416/60


  • Winner of the 2016 Presidential Election

    Oh. Did not see that.



  • @PJH said:

    I'll investigate tomorrow, but I suspect since the current query is already at 4 seconds, it won't be cheap...

    Quite, hence my original comment further back about it being a hell of a query.

    You could do it was two badges, one for necro, one for the necro resulting in a topic active for x days.
    The latter would be cheap as it'd only be using the small subset of data from the badges table for holders of the first and then building on it.


  • mod

    @Arantor said:

    And here I thought you were ignoring this category because it hurts your soul to be party to gamification in a community.

    He said that before I jeffed the posts from another category.


  • sockdevs

    @accalia said:

    i wonder if we could get a badge for necroing a topic.

    Maybe a badge for the most badges too? :stuck_out_tongue_winking_eye:


    In all seriousness though, badges should really be for things that should be encouraged, like posting, liking, and being a pendantic dickweed; I'm not sure necroing topics fits that bill.

    <!-- Emoji'd by MobileEmoji 0.2.0-->


  • select p2.user_id, p2.created_at granted_at, p2.id post_id
    from badge_posts p1, badge_posts p2, badge_posts p3
    where
        p1.post_number + 1 = p2.post_number
    and p2.post_number + 4 = p3.post_number
    and p1.topic_id = p2.topic_id
    and p2.topic_id = p3.topic_id
    and p1.created_at + INTERVAL('2 month') < p2.created_at
    and p2.created_at + INTERVAL('4 days ') > p3.created_at
    

    4 posts in 4 days after the necro



  • That's a badge per post that's 4 days after the necro isn't it?

    Nevermind.


  • sockdevs

    I don't think so

    Never mind :laughing:

    <!-- Emoji'd by MobileEmoji 0.2.0-->

    I was in the middle of typing it; figured I may as well aim for that 1000 posts/month mark :laughing:



  • Nope, only the first necroer will satisfy the 2 month interval :wink:



  • @RaceProUK said:

    I was in the middle of typing it; figured I may as well aim for that 1000 posts/month mark

    Hey, that's why I hit reply! To inflate my post count.



  • Hm,

    Select user_id, created_at granted_at, id post_id
    from badge_posts necro
    where exists (
        select 1 from badge_posts prev
        where prev.created_at + INTERVAL('2 month') < necro.created_at
        and prev.topic_id = necro.topic_id
        and prev.post_number + 1 = necro.post_number
    ) and exists (
        select 1 from badge_posts followers
        where followers.created_at < necro.create_at + INTERVAL('4 day') 
        and followers.topic_id = necro.topic_id
        and followers.post_number >= necro.post_number
        having count( distinct user_id ) > 3
        and count( post_id ) > 10
    )
    

    Require 10 followups from 3 distinct people, other than the necroer, in the next 4 days.

    Probably takes an age to run.

    EDIT: Missing )


  • sockdevs

    @riking said:

    ```
    select p2.user_id, p2.created_at granted_at, p2.id post_id
    from badge_posts p1, badge_posts p2, badge_posts p3
    where
    p1.post_number + 1 = p2.post_number
    and p2.post_number + 4 = p3.post_number
    and p1.topic_id = p2.topic_id
    and p2.topic_id = p3.topic_id
    and p1.created_at + INTERVAL('2 month') < p2.created_at
    and p2.created_at + INTERVAL('4 days ') > p3.created_at

    
    4 posts in 4 days after the necro</blockquote>
    A good start, but to avoid spam abuse (look who's talking!), it should really guard against the same user posting all four posts.
    
    <!-- Emoji'd by MobileEmoji 0.2.0-->

  • area_deu

    You forgot:

    • Cheated reads
    • Spamming posts
    • Not having a life
    • Not getting jokes


  • select p2.user_id, p2.created_at granted_at, p2.id post_id
    from badge_posts p1, badge_posts p2, badge_posts p3
    where 1 = 1
    and p1.topic_id = p2.topic_id
    and p2.topic_id = p3.topic_id
    and p1.post_number + 1 = p2.post_number
    and p2.post_number + 4 = p3.post_number
    and p1.user_id != p2.user_id
    and p2.user_id != p3.user_id
    and p1.created_at + 60 * INTERVAL '1 day' < p2.created_at
    and p2.created_at + 4  * INTERVAL '1 day' > p3.created_at
    

    Happy now?


  • sockdevs

    Still not sure it's right; tell you what, I'll see if I can work something out, and get back to you ;)

    <!-- Emoji'd by MobileEmoji 0.2.0-->


  •                                                                                   QUERY PLAN                                                                                  
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=3348.52..19100.98 rows=1 width=16)
       Join Filter: (t.id = p.topic_id)
       ->  Nested Loop  (cost=3348.23..19091.14 rows=9 width=40)
             Join Filter: (t.id = p_1.topic_id)
             ->  Hash Join  (cost=3347.94..17353.04 rows=1590 width=32)
                   Hash Cond: (t_2.id = t.id)
                   ->  Hash Join  (cost=1116.42..15022.43 rows=22186 width=24)
                         Hash Cond: (p_2.topic_id = t_2.id)
                         ->  Seq Scan on posts p_2  (cost=0.00..12855.48 rows=82867 width=20)
                               Filter: (deleted_at IS NULL)
                         ->  Hash  (cost=1043.10..1043.10 rows=5866 width=4)
                               ->  Hash Join  (cost=2.99..1043.10 rows=5866 width=4)
                                     Hash Cond: (t_2.category_id = c_2.id)
                                     ->  Index Only Scan using idx_topics_front_page on topics t_2  (cost=0.41..906.47 rows=20103 width=8)
                                           Index Cond: ((deleted_at IS NULL) AND (visible = true))
                                           Filter: visible
                                     ->  Hash  (cost=2.29..2.29 rows=23 width=4)
                                           ->  Seq Scan on categories c_2  (cost=0.00..2.29 rows=23 width=4)
                                                 Filter: (allow_badges AND (NOT read_restricted))
                   ->  Hash  (cost=2211.89..2211.89 rows=1570 width=8)
                         ->  Hash Join  (cost=1119.41..2211.89 rows=1570 width=8)
                               Hash Cond: (t.id = t_1.id)
                               ->  Hash Join  (cost=2.99..1043.10 rows=5866 width=4)
                                     Hash Cond: (t.category_id = c.id)
                                     ->  Index Only Scan using idx_topics_front_page on topics t  (cost=0.41..906.47 rows=20103 width=8)
                                           Index Cond: ((deleted_at IS NULL) AND (visible = true))
                                           Filter: visible
                                     ->  Hash  (cost=2.29..2.29 rows=23 width=4)
                                           ->  Seq Scan on categories c  (cost=0.00..2.29 rows=23 width=4)
                                                 Filter: (allow_badges AND (NOT read_restricted))
                               ->  Hash  (cost=1043.10..1043.10 rows=5866 width=4)
                                     ->  Hash Join  (cost=2.99..1043.10 rows=5866 width=4)
                                           Hash Cond: (t_1.category_id = c_1.id)
                                           ->  Index Only Scan using idx_topics_front_page on topics t_1  (cost=0.41..906.47 rows=20103 width=8)
                                                 Index Cond: ((deleted_at IS NULL) AND (visible = true))
                                                 Filter: visible
                                           ->  Hash  (cost=2.29..2.29 rows=23 width=4)
                                                 ->  Seq Scan on categories c_1  (cost=0.00..2.29 rows=23 width=4)
                                                       Filter: (allow_badges AND (NOT read_restricted))
             ->  Index Scan using index_posts_on_topic_id_and_post_number on posts p_1  (cost=0.29..1.08 rows=1 width=24)
                   Index Cond: (topic_id = p_2.topic_id)
                   Filter: ((deleted_at IS NULL) AND (user_id <> p_2.user_id) AND ((created_at + '4 days'::interval) > p_2.created_at) AND (p_2.post_number = (post_number + 4)))
       ->  Index Scan using index_posts_on_topic_id_and_post_number on posts p  (cost=0.29..1.08 rows=1 width=20)
             Index Cond: (topic_id = p_1.topic_id)
             Filter: ((deleted_at IS NULL) AND (user_id <> p_1.user_id) AND ((created_at + '60 days'::interval) < p_1.created_at) AND (p_1.post_number = (post_number + 1)))
    (45 rows)
    


  • Without the badge_post view:

    meta_discourse=# EXPLAIN select p2.user_id, p2.created_at granted_at, p2.id post_id
    from posts p1, posts p2, posts p3
    where 1 = 1
    and p1.topic_id = p2.topic_id
    and p2.topic_id = p3.topic_id
    and p1.post_number + 1 = p2.post_number
    and p2.post_number + 4 = p3.post_number
    and p1.user_id != p2.user_id
    and p2.user_id != p3.user_id
    and p1.created_at + 60 * INTERVAL '1 day' < p2.created_at
    and p2.created_at + 4  * INTERVAL '1 day' > p3.created_at;
                                                      QUERY PLAN                                                   
    ---------------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=39745.65..43140.42 rows=35 width=16)
       Join Filter: (p1.topic_id = p3.topic_id)
       ->  Merge Join  (cost=39745.35..41556.91 rows=1728 width=28)
             Merge Cond: ((p1.topic_id = p2.topic_id) AND (((p1.post_number + 1)) = p2.post_number))
             Join Filter: ((p1.user_id <> p2.user_id) AND ((p1.created_at + '60 days'::interval) < p2.created_at))
             ->  Sort  (cost=19872.68..20086.80 rows=85648 width=20)
                   Sort Key: p1.topic_id, ((p1.post_number + 1))
                   ->  Seq Scan on posts p1  (cost=0.00..12855.48 rows=85648 width=20)
             ->  Sort  (cost=19872.68..20086.80 rows=85648 width=24)
                   Sort Key: p2.topic_id, p2.post_number
                   ->  Seq Scan on posts p2  (cost=0.00..12855.48 rows=85648 width=24)
       ->  Index Scan using index_posts_on_topic_id_and_post_number on posts p3  (cost=0.29..0.90 rows=1 width=20)
             Index Cond: ((topic_id = p2.topic_id) AND (post_number = (p2.post_number + 4)))
             Filter: ((p2.user_id <> user_id) AND ((p2.created_at + '4 days'::interval) > created_at))
    (14 rows)
    

    Runs pretty fast, and it works: https://meta.discourse.org/t/about-the-rest-api/3285/3



  • Now that's interesting.... why does p2-p3 get a index scan but not p1-p2?



  • On further thinking, this definitely needs a bots exclusion on the followers subquery, or you can necro with @summonbot for the badge. And may as well exclude the necroer from followups entirely.

    Also, as @riking suggested in his last verion, use posts instead of badge_posts for performance.

    Select user_id, created_at granted_at, id post_id
    from badge_posts necro
    where exists (
        select 1 from posts prev
        where prev.created_at + INTERVAL('2 month') < necro.created_at
        and prev.topic_id = necro.topic_id
        and prev.post_number + 1 = necro.post_number
    ) and exists (
        select 1 from posts followers
        where followers.created_at < necro.create_at + INTERVAL('4 day') 
        and followers.topic_id = necro.topic_id
        and followers.post_number > necro.post_number
        and followers.user_id != necro.user_id
        and followers.user_id not in (....bots...)
        having count( distinct user_id ) >= 3
        and count( post_id ) >= 10
    )
    

    I don't have the not-a-bot subquery handy.



  • Yes, Master @PleegWat, by your command I shall summon @discoursebot, @signatureguy, @mottbott, @sockbot, @zoidberg, and @codinghorrorbot.<!-- t8343 p47 -->



  • I agree with whatever @SummonBot posted just above.<t8343p48>



  • @SummonBot has summoned me, and so I appear.

    <!-- Posted by SockBot 0.15.1 "Zany Zoe" on Sun, 22 Feb 2015 21:53:24 GMT-->

Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.