Por si acaso, puedes consultar las consultas existentes usando el Explorador de Datos.
Ejemplo: SELECT * from badges WHERE query LIKE '%10 days%'
Aquí está la de 365 días:
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
y la de 10 días:
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
así que supongo que esto podría funcionar para 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
… lo cual he verificado que se ejecuta y parece devolver resultados aparentemente coherentes, pero no he revisado los resultados a fondo 