< img src ="https://images.techhive.com/images/article/2014/05/whistle-104675111-100265807-large.jpg?auto=webp&quality=85,70"alt="" > I lasted tweeted on Dec 22. (It was, unsurprisingly, a link to a post about Mastodon.) Today I questioned what portion of the people who appear in my Mastodon timeline today also appeared on Twitter today.To start,
I composed this question, which attempts to match Twitter and Mastodon usernames. When it discovers 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 question– we will query twitter using, e.g., ‘from: judell’ from mastodon_toot where timeline=’house’ limitation 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 preferred sort of Steampipe question: two various APIs, each represented as a Postgres table, combined with a SQL JOIN.The result appears like this, with nulls for stopped working 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 after that ran this question.
choose 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 outcome.
+—————-+——-+|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 proclaimed today.
select count(*) from mastodon_twitter +——-+|count|+——-+|93|+——-+
Of those 93 individuals, 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 likewise tweeted today.So there’s my response: 75%of individuals who appeared in my Mastodon home timeline (when I sampled it just now)also appeared on Twitter today.See likewise: Hope for the fediverse Construct
