Table: mastodon_following - Query Mastodon Following using SQL

Mastodon is a decentralized, open-source social network. A Mastodon Following is a list of accounts that a user has chosen to follow. It represents the user's interest in the posts of these accounts and is a fundamental aspect of user interaction within the Mastodon platform.

Table Usage Guide

The mastodon_following table provides insights into the accounts a user is following within Mastodon. As a social media analyst, explore following-specific details through this table, including the status of following, follower counts, and associated metadata. Utilize it to understand user behavior, such as their interest patterns, the connections between users, and the dynamics of user interactions.

Important Notes

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

Examples

List following

Analyze the Mastodon social network to identify who a certain user is following, their account details, and their level of activity. This can be useful for understanding a user's social circle and their engagement within the platform.

select
  acct,
  username,
  display_name,
  followers_count,
  following_count,
  statuses_count
from
  mastodon_following
where
  following_account_id = '1'
limit
  10;

select
  acct,
  username,
  display_name,
  followers_count,
  following_count,
  statuses_count
from
  mastodon_following
where
  following_account_id = '1'
limit
  10;

Count follows by month of account creation

Analyze the number of new followers an account has gained each month. This can provide insights into the account's growth trends and popularity over time.

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_following mf
  join my_account_id mai on mf.following_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_following mf
  join my_account_id mai on cast(mf.following_account_id as text) = mai.id
group by
  created
order by
  created;

Schema for mastodon_following

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The account ID.
acct	text		username@server for the account.
created_at	timestamp with time zone		Timestamp when the account was created.
display_name	text		Display name for the account.
followed_account_id	text		ID of the follower account.
followers_count	bigint		Number of followers for the account.
following_account_id	text	=	ID of the account who is following.
following_count	bigint		Number of accounts this account follows.
instance_qualified_account_url	text		Account URL prefixed with my instance.
note	text		Description of the account.
server	text		Server for the account.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
statuses_count	bigint		Toots from this account.
url	text		URL for the account.
username	text		Username 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_following