SteemSQL 系列教程之 - 你的哪些好友已经好久没玩STEEM了？

假设你想知道你关注的哪些好友很久没玩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

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

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的, 你也试试看吧?