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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
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.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
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