Custom consecutive days badge


(PJH) #1

Want to reward people who come day after day after day? After day?

SELECT user_id, 0 post_id, current_timestamp granted_at, count(user_id) FROM user_visits WHERE date_trunc('day',user_visits.visited_at) >= date_trunc('day', now() - interval '14 day') AND (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) ) GROUP BY user_id HAVING count(user_id) >= 14 Change both instances of `14` to the same value to change the period of consecutive days' visits.

If run as a daily update, empirical evidence (until we get confirmation from Sam) would suggest daily queries run around midday UTC, so regular morning visitors will get it on the nth day, regular afternoon visitors may get it the day after, or perhaps not at all…

Edit: Added :backfill

Edit: Query is off-by-one, it should be >14, not >=14, otherwise people can miss one day in the period and still obtain the badge, however see below for a less system intensive query.


What cool badge queries have you come up with?
(PJH) #2

Just to point out to people who may be following this topic - I’ve changed my queries in light of back-end changes - they won’t run without those changes (if you’ve implemented them, check yoursite.com/logs for errors relating to badges to make sure they’re not being skipped)


(Sam Saffron) #3

This is not a good idea.

If you have a badge that is triggering after posting, its going to get post_ids, which means 0 in (:post_ids) is just going to resolve to nothing and your delta query do nothing.

You can always get user_id from the posts_ids

:backfill OR user_id IN ( 
  SELECT p1.user_id FROM posts p1 WHERE p1.id IN (:post_ids)
)

(PJH) #4

That’s the idea. There is no concept of post_id in those queries. That clause is only there because :post_ids is required to be in the query otherwise it won’t get run.

It will if user_id matches :user_ids. Or :backfill is true.


(Sam Saffron) #5

Thing is, that trigger will always have blank user ids. acts on post / creates or edits post will only ever get post_ids.

So if you trigger and have a clause with user_ids, it will be ignored. We are going to fix it so it disallows saving in this case cause it is wrong.


(PJH) #6

So user_ids aren’t passed in on those triggers? (i.e. the people who’ve done something resulting in generated post_ids.)


(Sam Saffron) #7

Correct, lots of users can be involved, actors, editors, original owners, past editors and so on


(PJH) #8

OK, I’ve added a NOT in there to make it true all the time.


(Kane York) #9

…what? no… just drop the OR clause

  AND  (:backfill OR user_id IN (:user_ids))

(PJH) #10

Then the badge doesn’t run because that parameter isn’t there.


(Kane York) #11

First Like appears to be working fine to me:


(PJH) #12

Found a new way of doing this that, I believe, while being (much) longer in terms of actual query statement, is shorter in execution since it

  1. Only needs to be run once per day (per badge)
  2. Will take into account any period covered by user_visits so when the query runs doesn’t matter - it can run at 2359 or 0001 and users will still get their badge {a}

Query typically takes less than a second on a user_visits table with 835 users present and 24,000 records.

SELECT username, user_id, current_timestamp granted_at, max(days) from (
    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.user_id AS user_id, 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
) x
GROUP BY x.username, x.user_id 
HAVING max(days)>=31

This query will award to anyone with any period of 31 consecutive days activity in user_vists (note that it is possible for someone to register a user_visit without reading any posts - this query does not ignore these entries.)

Change the 31 for different periods (we use 7, 31, 50, 100)

Other settings:


{a} the previous query in the first post depended on running between a user visiting that day and midnight UTC, in order for the user to qualify for the badge potentially missing users out.


What cool badge queries have you come up with?
(Louis Rossouw) #13

Adjusted this to allow multiple badge awards, and to add the correct granted_at date (as the end date of the range).
Need to tick Can be granted multiple times.

SELECT username, user_id, x.end_range as granted_at, max(days) from (
    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.user_id AS user_id, 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
) x
GROUP BY x.username, x.user_id , x.end_range
HAVING max(days)>=31

(Louis Rossouw) #14

Actually the above doesn’t work properly where multiple badges are awarded.