I have seen that this badge exists for users who visit the forum consecutively for 10 or 365 days, but I want to create an automatic badge in my forum that is awarded to a person who visited the forum for 30 consecutive days.
Above all, what I’m interested in is getting the SQL.
FWIW, you can find the existing queries using the Data Explorer.
e.g: SELECT * from badges WHERE query LIKE '%10 days%'
Here’s the one for 365 days:
WITH consecutive_visits AS ( SELECT user_id , visited_at , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s FROM user_visits ), visits AS ( SELECT user_id , MIN(visited_at) "start" , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank" FROM consecutive_visits GROUP BY user_id, s HAVING COUNT(*) >= 365 ) SELECT user_id , "start" + interval '365 days' "granted_at" FROM visits WHERE "rank" = 1
and 10 days:
WITH consecutive_visits AS ( SELECT user_id , visited_at , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s FROM user_visits ), visits AS ( SELECT user_id , MIN(visited_at) "start" , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank" FROM consecutive_visits GROUP BY user_id, s HAVING COUNT(*) >= 10 ) SELECT user_id , "start" + interval '10 days' "granted_at" FROM visits WHERE "rank" = 1
so I’m guessing this might do the trick for 30:
WITH consecutive_visits AS ( SELECT user_id , visited_at , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s FROM user_visits ), visits AS ( SELECT user_id , MIN(visited_at) "start" , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank" FROM consecutive_visits GROUP BY user_id, s HAVING COUNT(*) >= 30 ) SELECT user_id , "start" + interval '30 days' "granted_at" FROM visits WHERE "rank" = 1
… which I checked runs and seems to come back with apparently sensible results but I’ve not checked the results thoroughly 
Thank you very much, I appreciate it.
Stuck? How do you mean? (Please forgive my poor Spanish) I’ve just checked and it works fine. Check you have entered all the required fields and there are no validation errors above. If it Saves, you should see the new badge at the bottom of the list on the left. You can also preview the results using the link below the SQL.
I was trying in my forum to copy the SQL from the bagde referring to the 10 days and then change to 30, but it won’t let me copy it, but thanks to your help I was able to make the badge I wanted.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
