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.
