SteemSQL Tutorial - Finding Inactive Steemians that You Follow - SteemSQL 系列教程之 - 你的哪些好友已经好久没玩STEEM了?
Image Credit: Pixabay.com
If you have a following list that you would like to unfollow if he/she is inactive for at least 7 days, you might run the following SQL query (thanks to @arcange 's STEEMSQL). Being inactive means that he/she did not make a post or comment.
select author, datediff(day, T.last, GetUTCDate())
from
(
select author, max(created) "last"
from Comments
where author in ('followingid1', 'followingid2' ...)
group by author
) T
where
datediff(day, T.last, GetUTCDate()) > 7
The nested SQL groups the posts by author and returns the last activity time, store in column "last". The main query then further filters out the active users by datediff(day, T.last, GetUTCDate()) > 7
We can further optimise this SQL by storing the date columns in the inner SQL query. The following IDs can be fetched by query the Followers
view. So the query becomes:
select author, T.days
from
(
select author, max(created) "last", datediff(day, max(created), GetUTCDate()) "days"
from Comments
where author in (
select
following
from
followers
where
follower = 'justyy'
)
group by author
) T
where
T.days > 7
order by
T.days desc
The above query will list @justyy 's most inactive following user IDs, this gives results:
Image Credit: steemsql.com
假设你想知道你关注的哪些好友很久没玩STEEM了,你可以通过 @arcange 的 STEEMSQL 来进行查询。我们可以简单的定义7天内如果用户没有发表评论或者文章就算没玩(因为可以通过机器人自动点赞)
select author, datediff(day, T.last, GetUTCDate())
from
(
select author, max(created) "last"
from Comments
where author in ('followingid1', 'followingid2' ...)
group by author
) T
where
datediff(day, T.last, GetUTCDate()) > 7
嵌套的SQL用于按用户排序并返回最近一次活动时间 last
。主SQL则把这时间再一次过滤,去掉7天内有活动的用户。datediff(day, T.last, GetUTCDate()) > 7
我们可以把时间天数间隔保存在嵌套的SQL里当成一字段,我们还可以从视图 Followers
中取得关注的列表。
select author, T.days
from
(
select author, max(created) "last", datediff(day, max(created), GetUTCDate()) "days"
from Comments
where author in (
select
following
from
followers
where
follower = 'justyy'
)
group by author
) T
where
T.days > 7
order by
T.days desc
上面的SQL就返回了我关注的这用户中已经不玩STEEM的,你也试试看吧?
@justyy 是 https://justyy.com 的博主,在 @tumutanzi 大哥 的介绍下加入 STEEMIT,写些帖子挣些小钱养家糊口。
@justyy 也是CN 区的点赞机器人,对优质内容点赞,只要代理给 @justyy 每天收利息(年化率10%)并能获得一次至少2倍(VP 200%+)的点赞,大鱼 @htliao 都加入了这个计划(530 SP)表示支持。
- cn区最低保障系统 上线了!
- cn区低保计划(鼓励新人)真的适合你么?
- 今天(2017-10-20) 银行股东名单
This post was resteemed by @steemitrobot!
Good Luck!
The @steemitrobot users are a small but growing community.
Check out the other resteemed posts in steemitrobot's feed.
Some of them are truly great. Please upvote this comment for helping me grow.
Thanks for the tips. they are very helpful.
@cmtzco has voted on behalf of @minnowpond. If you would like to recieve upvotes from minnowponds team on all your posts, simply FOLLOW @minnowpond.
To receive an upvote send 0.25 SBD to @minnowpond with your posts url as the memo
To receive an reSteem send 0.75 SBD to @minnowpond with your posts url as the memo
To receive an upvote and a reSteem send 1.00SBD to @minnowpond with your posts url as the memo
社区待久了就有感情,常看到的那些ID,即便以前没什么交流,很久看不到,还是会有种失落的感觉。
很久没看到一哥了!
一哥估计已经退出江湖了,从至成了传说。
This post has been ranked within the top 80 most undervalued posts in the second half of Oct 20. We estimate that this post is undervalued by $14.90 as compared to a scenario in which every voter had an equal say.
See the full rankings and details in The Daily Tribune: Oct 20 - Part II. You can also read about some of our methodology, data analysis and technical details in our initial post.
If you are the author and would prefer not to receive these comments, simply reply "Stop" to this comment.
Thanks... so if it is undervalued.... can you upvote it? LOL
on the flip side, if you want to check who's following you are inactive, you can try this: http://steemit.deadfollowers.info/
Thanks for sharing the tool.
Also you can just swap followers and following in the above Query ..