What cool badge queries have you come up with?

badge

(Sam Saffron) #128

Yeah but you have to be careful cause it can be slow


(Joe Z) #129

Anton’s (@meglio) Personal Page badge had me thinking of another idea, kinda based on it.

To most easily explain, imagine our members have cars, and each make has its own topic. I want a badge to go to users when they reply to a specific topic and post an image of their vehicle.

So for instance, if they have a Honda, they post a picture in the Honda topic and they get the badge. A text reply awards them nothing


#130

Thanks. This is very helpful. I am also thinking about encouraging users to initiate discussions. I am messing around your query to count original posts with p.post_number = 1


(Alessio Fattorini) #131

What about a badge like “Seen here last 30 days” or "Seen here last 60 days"
Clearly, you can lose it in case you haven’t visited discourse recently.


(Joe Z) #132

I’m not familiar with revocation queries, but here is how it is assigned. Last line is how many days

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)>=7

(Alessio Fattorini) #133

Sorry, maybe there was a misunderstanding… I meant at least 1 visit in the last 30 days. Not at least a visit each day, in the last 30 days. Results are not matching “Active Users” in the about panel.


(Jay Pfaffman) #134

Did you try changing the 7 in the last line to a 1?


(Alessio Fattorini) #135

If I set to 1, I obtain my total users. Correct.
If I change to 30 I see only 32 while active users in the last month are 300


(Jay Pfaffman) #136

Is that not what you’re looking for? The users who visited at least once in the past 30 days?

That’s because only 32 of the 300 visited the site each of the past 30 days. I’m not quite sure what Discoure’s definition of “Active” is.


(Alessio Fattorini) #137

My total users are 1.1k and active users (logged in) last month are around 300
Why does the badge show just 32? Setting 30 days?
Looks odd.


(Joe Z) #138

Reason is that above code I posted shows users that have been active every day for the set interval.


(Joe Z) #139

Try something more like this:

SELECT
* from users where users.last_seen_at > current_date - interval '30' day

#140

What would be a good badge query for awarding the member(s) who end up being the “Top Referrers” of the week / month?

Also, one for having the “Top Referred Topic(s)” of the week / month?


(OG) #141

I’d like to grant badge to user which has read number of pre-selected topics (“recommended reading”). Is it safe (also performance-wise) to use table topic_views for this badge?


(OG) #142

Poster of the month Badge

Variation of earlier mentioned top poster, this time awarded to one member each month:

SELECT DISTINCT ON (period) user_id, CONCAT(EXTRACT (YEAR FROM created_at), EXTRACT(MONTH FROM created_at)) AS period, COUNT(id) AS counter, MAX(created_at) AS granted_at
FROM badge_posts
WHERE created_at < date_trunc(‘month’, NOW())
GROUP BY period, user_id
ORDER BY period, counter DESC

First Upload Badge

SELECT user_id, MAX(created_at) AS granted_at
FROM uploads
GROUP BY user_id
HAVING count(*) > 0


(Alessio Fattorini) #143

it doesn’t work, I need people that have been active at least one day for a set interval

Contract violation:
Query does not return a 'user_id' column

@riking @mcwumbly


(Kane York) #144

Change it to run daily.


(Daniela) #145

I have this error when this query is running:
Info:

    Job exception: ERROR:  duplicate key value violates unique constraint "index_user_badges_on_badge_id_and_user_id_and_seq"
    DETAIL:  Key (badge_id, user_id, seq)=(128, 1, 0) already exists.

Env:

process_id 23857
application_version ccf9b7067135f37bde23c80456d21b2d1924f858
current_db default
job            Jobs::BadgeGrant
                opts
                messagecode_desc Exception granting badges
                extrabadge_id 128

and this one:

:slight_frown:


(Alessio Fattorini) #146

I already did it, I need a badge for who have been active at least one day in the last 30 days
and a badge for how have posted at least once in the last 30 days
Can you help me?


(OG) #147

Too bad… I don’t have any testing platform with that much users and history to check it… As I see it SQL query needs to be limited so that user can’t be awarded “Poster of the Month” more than one time… I’m not sure if adding unique post_id into the query would suffice… Can anybody test it?