I lasted tweeted on Dec 22. (It was, unsurprisingly, a link to a blog post about Mastodon.) Today I wondered what percentage of the people who appear in my Mastodon timeline today also appeared on Twitter today.

To start, I wrote this query, which tries to match Twitter and Mastodon usernames. When it finds a match, it reports the day on which that person last tweeted.

with mastodon as (   select     substring(username from 1 for 15) as username, -- twitter names are max 15 chars     'from:' || substring(username from 1 for 15) as query -- we will query twitter using, e.g., 'from:judell'   from     mastodon_toot   where     timeline = 'home'   limit     500 ) select   m.username as mastodon_person,   t.author->>'username' as twitter_person,   max(to_char(t.created_at, 'YYYY-MM-DD')) as last_tweet_day from    mastodon m left join   twitter_search_recent t -- see https://hub.steampipe.io/plugins/turbot/twitter/tables/twitter_search_recent on   t.query = m.query group by   mastodon_person,   twitter_person order by   last_tweet_day desc 

This is my favorite kind of Steampipe query: two different APIs, each represented as a Postgres table, combined with a SQL JOIN.

The result looks like this, with nulls for failed matches.

+-----------------+-----------------+----------------+ | mastodon_person | twitter_person  | last_tweet_day | +-----------------+-----------------+----------------+ | AlanSill        | null            | null           | | Colarusso       | null            | null           | | ...                                                | | williamgunn     | null            | null           | | xian            | null            | null           | | ...                                                | | futurebird      | futurebird      | 2022-12-29     | | glynmoody       | glynmoody       | 2022-12-29     | | ...                                                | | khinsen         | khinsen         | 2022-12-23     | | blaine          | blaine          | 2022-12-23     | +-----------------+-----------------+----------------+ 

Next I created a table from the above query.

create table public.mastdon_twitter as    -- sql as above 

And then ran this query.

select   last_tweet_day,   count(*) from   mastodon_twitter where   last_tweet_day is not null group by   last_tweet_day order by   last_tweet_day desc 

Here’s the result.

+----------------+-------+ | last_tweet_day | count | +----------------+-------+ | 2022-12-29     | 36    | | 2022-12-28     | 6     | | 2022-12-27     | 1     | | 2022-12-26     | 1     | | 2022-12-25     | 2     | | 2022-12-23     | 2     | +----------------+-------+ 

The 500 toots represented here were created by 93 people who tooted today.

select count(*) from mastodon_twitter  +-------+ | count | +-------+ | 93    | +-------+ 

Of those 93 people, 48 have matching usernames.

select count(*) from mastodon_twitter where last_tweet_day is not null  +-------+ | count | +-------+ | 48    | +-------+ 

Of the 48 with matching usernames, 36 also tweeted today.

So there’s my answer: 75% of the people who appeared in my Mastodon home timeline (when I sampled it just now) also appeared on Twitter today.

See also:

1. Hope for the fediverse
2. Build a Mastodon dashboard with Steampipe
3. Browsing the fediverse
4. A Bloomberg terminal for Mastodon
5. Create your own Mastodon UX
6. Lists and people on Mastodon
7. Mastodon tooters also tweet