Yeah but you have to be careful cause it can be slow
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
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
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.
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
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.
Did you try changing the 7 in the last line to a 1?
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
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.
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?
Reason is that above code I posted shows users that have been active every day for the set interval.
Try something more like this:
SELECT * from users where users.last_seen_at > current_date - interval '30' day
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?
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?
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
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
GROUP BY user_id
HAVING count(*) > 0
it doesn’t work, I need people that have been active at least one day for a set interval
Query does not return a 'user_id' column
Change it to run daily.
I have this error when this query is running:
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.
process_id 23857 application_version ccf9b7067135f37bde23c80456d21b2d1924f858 current_db default job Jobs::BadgeGrant opts messagecode_desc Exception granting badges extrabadge_id 128
and this one:
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?
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?