SteemSQL Tutorial - Finding Inactive Steemians that You Follow

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 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

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

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: