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)表示支持。

1. cn区最低保障系统 上线了!
2. cn区低保计划（鼓励新人）真的适合你么？
3. 今天(2017-10-20) 银行股东名单

• 你的哪些好友已经好久没玩STEEM了？
• Finding Inactive Steemians

---

• SteemIt SP Delegation Tool 简易SP代理工具
• Steemit 在线工具，API接口和教程
• SteemIt Tutorials, Tools and APIs