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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account	jsonb		Account for toot author.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The account ID.
account_url	text		Account URL for toot author.
content	text		Content of the toot.
created_at	timestamp with time zone		Timestamp when the toot was created.
display_name	text		Display name for toot author.
followers	bigint		Follower count for toot author.
following	bigint		Following count for toot author.
id	text		ID of the toot.
in_reply_to_account_id	text		If the toot is a reply, the ID of the replied-to toot's account.
instance_qualified_account_url	text		Account URL prefixed with my instance
instance_qualified_reblog_url	text		Url of the reblog (boost) of the toot, prefixed with my instance.
instance_qualified_url	text		URL for the toot, as seen from my instance.
list_id	text		Id for a list that gathers toots.
query	text		Query string to find toots.
reblog	jsonb		Reblog (boost) of the toot.
reblog_content	text		Content of reblog (boost) of the toot.
reblog_server	text		Server of the boosted account.
reblog_username	text		Username of the boosted account.
reblogs_count	bigint		Boost count for toot.
replies_count	bigint		Reply count for toot.
server	text		Server of toot author.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
status	jsonb		Raw status
url	text		URL for the toot.
username	text		Username 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