turbot/mastodon
steampipe plugin install mastodon

Table: mastodon_follower - Query Mastodon Followers using SQL

Mastodon is a free and open-source self-hosted social networking service. It allows anyone to host their own server node in the network, and its various separately operated user bases are federated across many different sites. These sites are connected as a federated social network, allowing users from different servers to interact with each other.

Table Usage Guide

The mastodon_follower table provides insights into follower relationships within the Mastodon social network. As a social media analyst, explore follower-specific details through this table, including follower and followed account details, and associated metadata. Utilize it to uncover information about followers, such as their relationships with other accounts, and the growth of their networks.

Important Notes

  • You must specify the followed_account_id column in the where or join clause to query this table.

Examples

List followers

Discover the segments that have a high follower count in a social media platform. This can be used to identify popular users and understand their follower to following ratio, which can be useful for targeting influencer marketing campaigns.

select
acct,
username,
display_name,
followers_count,
following_count,
statuses_count
from
mastodon_follower
where
followed_account_id = '1'
limit
10;
The PostgreSQL query provided does not contain any PostgreSQL - specific functions
or data types,
JSON functions,
or joins that would need to be converted to SQLite syntax.Therefore,
the SQLite query is the same as the PostgreSQL query:
select
acct,
username,
display_name,
followers_count,
following_count,
statuses_count
from
mastodon_follower
where
followed_account_id = '1'
limit
10;

Count followers by month of account creation

Explore the growth of followers over time by counting the number of new followers added each month. This can help to understand the effectiveness of social media strategies and identify periods of significant growth or decline.

with my_account_id as (
select
id :: text
from
mastodon_my_account
limit
1
)
select
to_char(mf.created_at, 'yyyy-mm') as created,
count(*)
from
mastodon_follower mf
join my_account_id mai on mf.followed_account_id :: text = mai.id
group by
created
order by
created
with my_account_id as (
select
cast(id as text) as id
from
mastodon_my_account
limit
1
)
select
strftime('%Y-%m', mf.created_at) as created,
count(*)
from
mastodon_follower mf
join my_account_id mai on cast(mf.followed_account_id as text) = mai.id
group by
created
order by
created;

Schema for mastodon_follower

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The account ID.
accttextusername@server for the account.
created_attimestamp with time zoneTimestamp when the account was created.
display_nametextDisplay name for the account.
followed_account_idtext=ID of the account who is being followed.
follower_account_idtextID of the follower account.
followers_countbigintNumber of followers for the account.
following_countbigintNumber of accounts this account follows.
instance_qualified_account_urltextAccount URL prefixed with my instance.
notetextDescription of the account.
servertextServer for the account.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statuses_countbigintToots from this account.
urltextURL for the account.
usernametextUsername for the account.

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)" -- mastodon

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

steampipe_export_mastodon --config '<your_config>' mastodon_follower