Table: mastodon_weekly_activity - Query Mastodon Weekly Activity using SQL

Mastodon is a decentralized, open-source social network platform that emphasizes user privacy and online communities. It allows users to post messages, follow others, and interact with various types of content. The weekly activity in Mastodon includes data regarding posts, new followers, and other interactive actions taken by users on the platform.

Table Usage Guide

The mastodon_weekly_activity table provides insights into user activities within Mastodon. As a data analyst, explore activity-specific details through this table, including the number of posts, new followers, and other user interactions. Utilize it to uncover information about weekly user trends, the popularity of content, and the overall user engagement on the platform.

Examples

My home server's recent activity

Gain insights into the recent activities on your home server, such as statuses, logins, and registrations, sorted by week. This can help you understand user behavior and trends over time.

select
  week,
  statuses,
  logins,
  registrations
from
  mastodon_weekly_activity
order by
  week;

select
  week,
  statuses,
  logins,
  registrations
from
  mastodon_weekly_activity
order by
  week;

Activity on another server

Discover the segments of weekly activity, including statuses, logins, and registrations, on a specified server to understand user engagement trends over time. This can help in assessing the server's popularity and user activity patterns.

select
  week,
  statuses,
  logins,
  registrations
from
  mastodon_weekly_activity
where
  server = 'https://infosec.exchange'
order by
  week;

select
  week,
  statuses,
  logins,
  registrations
from
  mastodon_weekly_activity
where
  server = 'https://infosec.exchange'
order by
  week;

Schema for mastodon_weekly_activity

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The account ID.
logins	bigint		Weekly logins for a Mastodon instance.
registrations	bigint		Weekly registrations for a Mastodon instance.
server	text	=	Server whose activity is reported.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
statuses	bigint		Weekly toots for a Mastodon instance.
week	timestamp with time zone		First day of weekly activity for a Mastodon instance

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_weekly_activity