turbot/mastodon
steampipe plugin install mastodon

Table: mastodon_toot_favourite - Query Mastodon Toot Favourites using SQL

Mastodon is an open-source, self-hosted social networking service that allows users to create and distribute multimedia posts called "toots". The Mastodon Toot Favourites feature allows users to mark specific toots that they like, similar to the "like" or "favourite" feature on other social media platforms. This feature is useful for users who want to save or highlight certain toots for later viewing.

Table Usage Guide

The mastodon_toot_favourite table provides insights into the favourited toots within the Mastodon social networking service. As a social media analyst, explore details about favourited toots through this table, including the toot ID, account ID, and creation time. Utilize it to uncover information about user engagement trends, such as which toots are most commonly favourited and the timing of these favourites.

Examples

Get recent favourite toots, ordered by boost ("reblog") count

Explore the most popular recent posts on Mastodon, ranked by the number of times they've been shared or "boosted". This can help identify trending topics or influential users within the network.

select
created_at,
username,
replies_count,
reblogs_count,
content,
url
from
mastodon_toot_favourite
order by
reblogs_count desc
limit
60;
The given PostgreSQL query does not contain any PostgreSQL - specific functions
or data types,
JSON functions,
or joins.Therefore,
it can be used in SQLite without any modifications.So,
the SQLite query equivalent to the given PostgreSQL query is: ` ` ` sql
select
created_at,
username,
replies_count,
reblogs_count,
content,
url
from
mastodon_toot_favourite
order by
reblogs_count desc
limit
60;
### Count favourites by day
Discover the popularity of posts by tracking the number of favourites received each day. This helps in understanding user engagement trends and peak activity periods.
```sql+postgres
select
to_char(created_at, 'YY-MM-DD') as day,
count(*)
from
mastodon_toot_favourite
group by
day
limit
100;
select
strftime('%Y-%m-%d', created_at) as day,
count(*)
from
mastodon_toot_favourite
group by
day
limit
100;

Count favourites by person

Determine the popularity of individuals based on the number of favorites their posts receive. This can provide insights into who the most influential users are within a given community.

with data as (
select
case
when display_name = '' then username
else display_name
end as person
from
mastodon_toot_favourite
limit
100
)
select
person,
count(*)
from
data
group by
person
order by
count desc;
with data as (
select
case
when display_name = '' then username
else display_name
end as person
from
mastodon_toot_favourite
limit
100
)
select
person,
count(*)
from
data
group by
person
order by
count(*) desc;

Schema for mastodon_toot_favourite

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
accountjsonbAccount for toot author.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The account ID.
account_urltextAccount URL for toot author.
contenttextContent of the toot.
created_attimestamp with time zoneTimestamp when the toot was created.
display_nametextDisplay name for toot author.
followersbigintFollower count for toot author.
followingbigintFollowing count for toot author.
idtextID of the toot.
in_reply_to_account_idtextIf the toot is a reply, the ID of the replied-to toot's account.
instance_qualified_account_urltextAccount URL prefixed with my instance
instance_qualified_reblog_urltextUrl of the reblog (boost) of the toot, prefixed with my instance.
instance_qualified_urltextURL for the toot, as seen from my instance.
list_idtextId for a list that gathers toots.
querytextQuery string to find toots.
reblogjsonbReblog (boost) of the toot.
reblog_contenttextContent of reblog (boost) of the toot.
reblog_servertextServer of the boosted account.
reblog_usernametextUsername of the boosted account.
reblogs_countbigintBoost count for toot.
replies_countbigintReply count for toot.
servertextServer of toot author.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statusjsonbRaw status
urltextURL for the toot.
usernametextUsername for toot author.

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_toot_favourite