30 天访客徽章

支持
1

我注意到，已有徽章授予连续访问论坛 10 天或 365 天的用户。但我想在我的论坛中创建一个自动徽章，授予连续访问论坛 30 天的用户。

最重要的是，我想知道相关的 SQL 语句。

3 个赞
2

顺便一提，你可以使用数据探索器找到现有的查询。

例如：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

……我测试过这个查询可以运行，返回的结果看起来也合理，但我还没有仔细验证结果 :sweat_smile:

5 个赞
3

非常感谢，我真的很感激。

2 个赞
4

@merefield

Captura Query
Captura Query499×662 90.3 KB

我一直在尝试复制代码，但似乎被阻止了，再次感谢。:heart:

1 个赞
5

遇到问题了？具体是指什么呢？（请原谅我的西班牙语说得不好）我刚刚检查过，一切正常。请确认您已填写所有必填字段，并且上方没有验证错误。如果保存成功，您应该在左侧列表底部看到新徽章。您还可以使用 SQL 下方的链接预览结果。

2 个赞
6

我试图在论坛中复制与 10 天相关的徽章 SQL 代码，然后将其改为 30 天，但无法复制。不过，多亏了您的帮助，我成功创建了我想要的徽章。

2 个赞
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.