참고로, Data Explorer를 사용하여 기존 쿼리를 찾을 수 있습니다.
예: SELECT * from badges WHERE query LIKE '%10 days%'
365일용 쿼리는 다음과 같습니다:
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
그리고 10일용 쿼리는:
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
따라서 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
… 실행해 보니 정상적으로 동작하고 결과도 합리적으로 보이긴 하지만, 결과를 면밀히 확인해 보지는 않았습니다 