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.mastodon_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.
—
1 https://blog.jonudell.net/2022/11/28/autonomy-packet-size-friction-fanout-and-velocity/
2 https://blog.jonudell.net/2022/12/06/mastodon-steampipe-and-rss/
3 https://blog.jonudell.net/2022/12/10/browsing-the-fediverse/
4 https://blog.jonudell.net/2022/12/17/a-bloomberg-terminal-for-mastodon/
5 https://blog.jonudell.net/2022/12/19/create-your-own-mastodon-ux/
6 https://blog.jonudell.net/2022/12/22/lists-and-people-on-mastodon/
7 https://blog.jonudell.net/2022/12/29/how-many-people-in-my-mastodon-feed-also-tweeted-today/
8 https://blog.jonudell.net/2022/12/31/instance-qualified-mastodon-urls/
9 https://blog.jonudell.net/2023/01/16/mastodon-relationship-graphs/
10 https://blog.jonudell.net/2023/01/21/working-with-mastodon-lists/
11 https://blog.jonudell.net/2023/01/26/images-considered-harmful-sometimes/
12 https://blog.jonudell.net/2023/02/02/mapping-the-wider-fediverse/
13 https://blog.jonudell.net/2023/02/06/protocols-apis-and-conventions/
14 https://blog.jonudell.net/2023/02/14/news-in-the-fediverse/
15 https://blog.jonudell.net/2023/02/26/mapping-people-and-tags-on-mastodon/
16 https://blog.jonudell.net/2023/03/07/visualizing-mastodon-server-moderation/
17 https://blog.jonudell.net/2023/03/14/mastodon-timelines-for-teams/
18 https://blog.jonudell.net/2023/04/03/the-mastodon-plugin-is-now-available-on-the-steampipe-hub/
19 https://blog.jonudell.net/2023/04/11/migrating-mastodon-lists/
20 https://blog.jonudell.net/2023/05/24/when-the-rubber-duck-talks-back/
One thought on “How many people in my Mastodon feed also tweeted today?”