steampipe plugin install twitter

Table: twitter_user_follower - Query Twitter User Followers using SQL

Twitter is a social media platform that allows users to post and interact with messages known as "tweets". A significant aspect of Twitter is the concept of "followers". A follower on Twitter is another user who has subscribed to your tweets, meaning your tweets will appear in their timeline.

Table Usage Guide

The twitter_user_follower table provides insights into the followers of a specific Twitter user. As a social media analyst, you can explore follower-specific details through this table, including follower count, location, and associated metadata. Utilize it to uncover information about followers, such as their geographical distribution, follower count trends, and the verification status of followers.

Important Notes

  • The user_id field must be set in the where clause.

Examples

List followers for a user

Explore which Twitter users are following a specific account. This can be useful for understanding the reach and influence of that account.

select
*
from
twitter_user_follower
where
user_id = '1318177503995985921';
-- @steampipeio
select
*
from
twitter_user_follower
where
user_id = '1318177503995985921';
-- @steampipeio;

List followers by username

Identify instances where you can find out who is following a specific user on Twitter. This can be useful for understanding the demographics of your audience or identifying influential followers. Via subselect:

select
uf.id,
uf.username
from
twitter_user_follower as uf
where
uf.user_id in (
select
id
from
twitter_user
where
username = 'steampipeio'
);
select
uf.id,
uf.username
from
twitter_user_follower as uf
where
uf.user_id in (
select
id
from
twitter_user
where
username = 'steampipeio'
);

Via join:

select
uf.id,
uf.username
from
twitter_user_follower as uf,
twitter_user as u
where
uf.user_id = u.id
and u.username = 'steampipeio';
select
uf.id,
uf.username
from
twitter_user_follower as uf,
twitter_user as u
where
uf.user_id = u.id
and u.username = 'steampipeio';

Find the top 10 followers for a user

Determine the top ten users who are following a specific user on Twitter, ranked by the number of their own followers. This can be useful for identifying influential followers and understanding the reach of your social network. Via join:

select
uf.id,
uf.username,
(uf.public_metrics ->> 'followers_count') :: int as follower_count
from
twitter_user_follower as uf,
twitter_user as u
where
uf.user_id = u.id
and u.username = 'steampipeio'
order by
follower_count desc
limit
10;
select
uf.id,
uf.username,
cast(
json_extract(uf.public_metrics, '$.followers_count') as integer
) as follower_count
from
twitter_user_follower as uf,
twitter_user as u
where
uf.user_id = u.id
and u.username = 'steampipeio'
order by
follower_count desc
limit
10;

Schema for twitter_user_follower

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
created_attimestamp with time zoneThe UTC datetime that the user account was created on Twitter.
descriptiontextThe text of this user's profile description (also known as bio), if the user provided one.
entitiesjsonbEntities are JSON objects that provide additional information about hashtags, urls, user mentions, and cashtags associated with the description.
idtextThe unique identifier of this user.
locationtextThe location specified in the user's profile, if the user provided one. As this is a freeform value, it may not indicate a valid location, but it may be fuzzily evaluated when performing searches with location queries.
nametextThe name of the user, as they’ve defined it on their profile. Not necessarily a person’s name.
pinned_tweetjsonbContains withholding details for withheld content, if applicable.
pinned_tweet_idtextUnique identifier of this user's pinned Tweet.
profile_image_urltextThe URL to the profile image for this user, as shown on the user's profile.
protectedtextIndicates if this user has chosen to protect their Tweets (in other words, if this user's Tweets are private).
public_metricsjsonbContains details about activity for this user.
urltextThe URL specified in the user's profile, if present.
user_idtext=ID of the user who is followed by these users.
usernametextThe Twitter screen name, handle, or alias that this user identifies themselves with. Usernames are unique but subject to change.
verifiedbooleanIndicates if this user is a verified Twitter User.
withheldjsonbContains withholding details for withheld content, if applicable.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- twitter

You can pass the configuration to the command with the --config argument:

steampipe_export_twitter --config '<your_config>' twitter_user_follower