Mastodon tooters additionally tweet | InfoWorld

I lasted tweeted on Dec 22. (It was, unsurprisingly, a hyperlink to a weblog publish about Mastodon.) As we speak I puzzled what proportion of the individuals who seem in my Mastodon timeline at present additionally appeared on Twitter at present.

To start out, I wrote this question, which tries to match Twitter and Mastodon usernames. When it finds a match, it studies the day on which that individual final tweeted.

with mastodon as (
  choose
    substring(username from 1 for 15) as username, -- twitter names are max 15 chars
    'from:' || substring(username from 1 for 15) as question -- we'll question twitter utilizing, e.g., 'from:judell'
  from
    mastodon_toot
  the place
    timeline="house"
  restrict
    500
)
choose
  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 be part of
  twitter_search_recent t -- see https://hub.steampipe.io/plugins/turbot/twitter/tables/twitter_search_recent
on
  t.question = m.question
group by
  mastodon_person,
  twitter_person
order by
  last_tweet_day desc

That is my favourite type of Steampipe question: two totally different APIs, every represented as a Postgres desk, mixed with a SQL JOIN.

The outcome appears 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     |
+-----------------+-----------------+----------------+

Subsequent I created a desk from the above question.

create desk public.mastdon_twitter as 
  -- sql as above

After which ran this question.

choose
  last_tweet_day,
  depend(*)
from
  mastodon_twitter
the place
  last_tweet_day shouldn't be null
group by
  last_tweet_day
order by
  last_tweet_day desc

Right here’s the outcome.

+----------------+-------+
| last_tweet_day | depend |
+----------------+-------+
| 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 five hundred toots represented right here had been created by 93 individuals who tooted at present.

choose depend(*) from mastodon_twitter

+-------+
| depend |
+-------+
| 93    |
+-------+

Of these 93 folks, 48 have matching usernames.

choose depend(*) from mastodon_twitter the place last_tweet_day shouldn't be null

+-------+
| depend |
+-------+
| 48    |
+-------+

Of the 48 with matching usernames, 36 additionally tweeted at present.

So there’s my reply: 75% of the individuals who appeared in my Mastodon house timeline (once I sampled it simply now) additionally appeared on Twitter at present.

See additionally:

1. Hope for the fediverse
2. Construct a Mastodon dashboard with Steampipe
3. Searching the fediverse
4. A Bloomberg terminal for Mastodon
5. Create your personal Mastodon UX
6. Lists and folks on Mastodon
7. Mastodon tooters additionally tweet