3^n Likes Given Badge?
-
https://meta.discourse.org/t/grant-badge-every-time-someone-gives-out-5-likes/24200/5?u=riking
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.
-
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!
-
-
Query edited.
-
Without /t/1000: http://pastebin.com/vzbV7kZ9
With: http://pastebin.com/pxhRis2r
-
-
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.
-
@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?
-
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
I suppose I should make one of those two.
-
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?
-
count | liked
-------+--------------
213 | blakeyrat
211 | chubertdev
205 | boomzilla
186 | ArantorIronic that the person who refuses to give out any likes has received the most.
-
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.
-
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...
-
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.
-
The ensuing rant should be entertaining.
and would make the like worth the effort.