3^n Likes Given Badge?



  • Here's a badge query that awards 1 copy for every 4^n likes you give. (Someone with 64 likes will have 4 copies of the badge - 1, 4, 16, 64.)

    WITH RECURSIVE Counter AS (
      SELECT 1 i 
      UNION ALL
      SELECT i * 3 -- Exponential!
      FROM Counter
      WHERE i < 100000
    ),
    user_like_count AS (
        SELECT pa.user_id, count(pa.user_id) count
        FROM post_actions pa
        LEFT OUTER JOIN posts p ON pa.post_id = p.id 
        WHERE post_action_type_id = 2
        -- Limit to the users who just casted likes
        AND (:backfill OR (pa.user_id IN (
          SELECT user_id FROM post_actions pa
          WHERE pa.post_id IN (:post_ids)
        )))
        -- Exclude Likes Topic
        AND p.topic_id NOT IN (1000)
        GROUP BY pa.user_id
        -- HAVING count(pa.user_id) > 10
    )
    SELECT user_like_count.user_id, current_timestamp granted_at, Counter.i post_id
    FROM user_like_count
    JOIN Counter ON user_like_count.count > Counter.i
    -- ORDER BY Counter.i DESC, user_id ASC
    ;
    

    This query should trigger on "user acts on a post", SHOULD target posts, but not show the post granting the badge, should be multiple grant, no need for revocation query.


  • Winner of the 2016 Presidential Election

    Will this be called the Anti-Blakeyrat-Badge?

    And can you exclude the Likes-Topic? Because I don't think that should count.

    Filed Under: We might blow the query otherwise!



  • @Kuro said:

    And can you exclude the Likes-Topic? Because I don't think that should count.

    Agreed.



  • Query edited.





  • @riking said:

    WHERE i < 100000

    That seems rather low...



  • Indeed. Some of us are well over 30000 already.



  • Updated, there was a unique constraint issue.

    Some may say my solution was a hack. They're absolutely correct.

    @HardwareGeek That's a hundred K, not 10 K.



  • @riking said:

    @HardwareGeek That's a hundred K, not 10 K.

    I know, but even that is likely to be hit sooner or later in this forum. However, eliminating t/1000 will delay that achievement significantly, but not indefinitely. It seems to me like an arbitrary limit; unless a higher limit causes the query to take unreasonably long to run, why not MAXINT?



  • @HardwareGeek said:

    unless a higher limit causes the query to take unreasonably long to run

    That was the case earlier when I switched the > with a <, but I don't think it matters now.



  • How about a badge series for likes from x^n unique people? (Where 2≤_x_≤5, probably)



  • I see someone's reading Meta :stuck_out_tongue:

    I suppose I should make one of those two.



  • @tar said:

    How about a badge series for likes from x^n unique people? (Where 2≤x≤5, probably)

    Probably not the most efficient way of doing this (especially given the runtime,) but since I had most of what's here already lying around ready to copy/paste..

    [postgres@sofa ~]$ sql_tdwtf count_likers
    # Distinct users who've liked someone
    WITH LikersLiked AS (
            SELECT l.username Liker, r.username Liked
            FROM post_actions pa
            INNER JOIN users l ON l.id=pa.user_id
            INNER JOIN posts p on p.id=pa.post_id
            INNER JOIN users r on r.id=p.user_id
            WHERE pa.post_action_type_id=2 AND
            p.topic_id != 1000
            GROUP BY Liker, Liked
    )
    SELECT COUNT(*), LikersLiked.Liked
    FROM LikersLiked
    GROUP BY LikersLiked.Liked
    ORDER BY count(*) DESC
    LIMIT 25
    
     count |    liked
    -------+--------------
       213 | blakeyrat
       211 | chubertdev
       205 | boomzilla
       186 | Arantor
       184 | mott555
       179 | accalia
       175 | cartman82
       173 | Maciejasjmj
       172 | dkf
       166 | hungrier
       165 | Keith
       164 | PJH
       158 | HardwareGeek
       158 | ben_lubar
       156 | VinDuv
       154 | Zecc
       153 | aliceif
       152 | FrostCat
       151 | Polygeekery
       150 | Onyx
       148 | anonymous234
       146 | abarker
       139 | Jaloopa
       137 | flabdablet
       136 | Yamikuronue
    (25 rows)
    
    Elapsed: 4.12s
    Backup taken:  2015-01-21 03:57:01.478024
    

    That will include, of course, stuff not included in badge_posts (PM's, etc.)



  • Yup, you then just need to cross join that with Counter to get the multiple badges effect, which is half of the original query.



  • WITH RECURSIVE
        Counter AS (
        SELECT 50 i
        UNION ALL
        SELECT i + 50
        FROM Counter
        WHERE i < 100000
      ),
        LikersLiked AS (
          SELECT
            pa.user_id Liker,
            p.user_id  Liked
          FROM post_actions pa
            INNER JOIN posts p ON p.id = pa.post_id
          WHERE pa.post_action_type_id = 2 AND
                p.topic_id != 1000
          GROUP BY Liker, Liked
      ),
        LikersLikedGrouped AS (
          SELECT
            count(LikersLiked.Liker) liker_count,
            LikersLiked.Liked        liked_user
          FROM LikersLiked
          GROUP BY LikersLiked.Liked
      )
    SELECT
      LikersLikedGrouped.liked_user,
      current_timestamp granted_at,
      Counter.i         post_id
    FROM LikersLikedGrouped
      JOIN Counter ON LikersLikedGrouped.liker_count > Counter.i
    ORDER BY Counter.i DESC, liked_user ASC
    ;
    

    Badge: Well Liked
    One of these badges is awarded for every 50 unique people that like your posts.



  • So there isn't going to be a 3^1, 3^2, 3^3 (or whatever the base will be) badge, just a single badge given multiple times?



  • @PJH said:

     count |    liked
    -------+--------------
    213 | blakeyrat
    211 | chubertdev
    205 | boomzilla
    186 | Arantor

    Ironic that the person who refuses to give out any likes has received the most.



  • @HardwareGeek said:

    has received from the most people.

    .. is more like it.

    Don't think I've got a most_likes query but could probably knock one up..



  • It'll be the list on /t/1000, until you exclude that. Pretty sure we went through those queries at some point in the past.



  • @PJH said:

    Don't think I've got a most_likes query but could probably knock one up..

    Ah - I do..

    [postgres@sofa ~]$ sql_tdwtf total_likes_x_1000
    # Total likes excluding /t/1000
    SELECT row_number() OVER (ORDER BY SUM(badge_posts.like_count) DESC) n,
       SUM(badge_posts.like_count) likes,
       users.username
    FROM badge_posts, users
    WHERE topic_id != 1000 AND
       badge_posts.user_id = users.id
    GROUP BY users.username
    ORDER BY SUM(badge_posts.like_count) DESC
    LIMIT 25
    
     n  | likes |   username
    ----+-------+--------------
      1 | 10168 | boomzilla
      2 |  7923 | blakeyrat
      3 |  7151 | chubertdev
      4 |  6658 | accalia
      5 |  5827 | Arantor
      6 |  5370 | FrostCat
      7 |  5192 | Polygeekery
      8 |  4814 | mott555
      9 |  4660 | PJH
     10 |  3973 | HardwareGeek
     11 |  3823 | abarker
     12 |  3611 | cartman82
     13 |  3508 | dkf
     14 |  3343 | Onyx
     15 |  3216 | Keith
     16 |  3172 | ben_lubar
     17 |  3142 | Yamikuronue
     18 |  2933 | Maciejasjmj
     19 |  2405 | loopback0
     20 |  2390 | aliceif
     21 |  2163 | darkmatter
     22 |  2118 | Matches
     23 |  2074 | Luhmann
     24 |  1937 | Zecc
     25 |  1912 | DoctorJones
    (25 rows)
    
    Elapsed: 1.386s
    Backup taken:  2015-01-21 03:57:01.478024
    [postgres@sofa ~]$
    


  • Your claim about irony is (almost) correct... :smiley:



  • @PJH said:

    has received from the most people.

    .. is more like it.

    Still ironic, especially if he gets "Most Liked" badges as a result. The ensuing rant should be entertaining.



  • @HardwareGeek said:

    The ensuing rant should be entertaining.

    and would make the like worth the effort.


Log in to reply
 

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