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 thewhere
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_followerwhere user_id = '1318177503995985921';-- @steampipeio
select *from twitter_user_followerwhere 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.usernamefrom twitter_user_follower as ufwhere uf.user_id in ( select id from twitter_user where username = 'steampipeio' );
select uf.id, uf.usernamefrom twitter_user_follower as ufwhere uf.user_id in ( select id from twitter_user where username = 'steampipeio' );
Via join:
select uf.id, uf.usernamefrom twitter_user_follower as uf, twitter_user as uwhere uf.user_id = u.id and u.username = 'steampipeio';
select uf.id, uf.usernamefrom twitter_user_follower as uf, twitter_user as uwhere 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_countfrom twitter_user_follower as uf, twitter_user as uwhere uf.user_id = u.id and u.username = 'steampipeio'order by follower_count desclimit 10;
select uf.id, uf.username, cast( json_extract(uf.public_metrics, '$.followers_count') as integer ) as follower_countfrom twitter_user_follower as uf, twitter_user as uwhere uf.user_id = u.id and u.username = 'steampipeio'order by follower_count desclimit 10;
Schema for twitter_user_follower
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
created_at | timestamp with time zone | The UTC datetime that the user account was created on Twitter. | |
description | text | The text of this user's profile description (also known as bio), if the user provided one. | |
entities | jsonb | Entities are JSON objects that provide additional information about hashtags, urls, user mentions, and cashtags associated with the description. | |
id | text | The unique identifier of this user. | |
location | text | The 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. | |
name | text | The name of the user, as they’ve defined it on their profile. Not necessarily a person’s name. | |
pinned_tweet | jsonb | Contains withholding details for withheld content, if applicable. | |
pinned_tweet_id | text | Unique identifier of this user's pinned Tweet. | |
profile_image_url | text | The URL to the profile image for this user, as shown on the user's profile. | |
protected | text | Indicates if this user has chosen to protect their Tweets (in other words, if this user's Tweets are private). | |
public_metrics | jsonb | Contains details about activity for this user. | |
url | text | The URL specified in the user's profile, if present. | |
user_id | text | = | ID of the user who is followed by these users. |
username | text | The Twitter screen name, handle, or alias that this user identifies themselves with. Usernames are unique but subject to change. | |
verified | boolean | Indicates if this user is a verified Twitter User. | |
withheld | jsonb | Contains 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