Most Quoted and Most Liked/Replied tweets as a feature

[abstractfairy]

Imagine this, you open a user's profile on community archive, you get to see their best (most liked or most replied?) tweets, and their most important tweets (most commonly quoted / foundational texts or tweets)

Most Quoted Tweet

This one actually has a lot of interest and demand. It also has a lot of use because the most quoted tweets will probably be foundational to the user's thinking and communicating styles. (e.g. "don't make me tap the sign")

The feature itself shouldn't be that hard to implement, in fact it's pretty much already implemented in SQL, all that's left is a front end for users. (replace the username nosilverv with the desired username)

WITH Q as (select * from (
  SELECT 
    id,
    expanded_url,
    urls.tweet_id,
    CASE 
      WHEN expanded_url ~ 'https?://(www\.)?(x\.com|twitter\.com)/[^/]+(/status/\d+)?/?($|\?)'
       AND expanded_url NOT LIKE '%search?%'
      THEN regexp_replace(expanded_url, '.*(x\.com|twitter\.com)/([^/?]+).*', '\2')
      ELSE NULL
    END AS QT_username,
    -- New column to extract tweet_id from URL
    CASE 
      WHEN expanded_url ~ '/status/\d+'
      THEN regexp_replace(expanded_url, '.*/status/(\d+)(?:\?.*)?$', '\1')
      ELSE NULL
    END AS url_tweet_id,
    a.username as tweet_username 
  FROM 
    public.tweet_urls urls 
    INNER JOIN public.tweets t on urls.tweet_id = t.tweet_id
    INNER JOIN public.account a on a.account_id = t.account_id
) as QT
WHERE 
  (expanded_url like'%x.com%' or expanded_url like '%twitter.com%')
  AND expanded_url NOT LIKE '%search?%'
  AND tweet_username = 'nosilverv' 
  AND QT_username = 'nosilverv'
  )--
select url_tweet_id,
 count(1) 
from Q

group by url_tweet_id
order by count(1) desc

Most Liked / Replied Tweet

same as above. I don't think it'd have as much impact, the things that go viral are more controversial or meme-y than they are interesting. But it'd probably be insightful, and it was a major feature for plugins like twemex

[DefenderOfBasic]

@ri72miieop that SQL query could be run from outside the DB, like from the supabase API right? If not in a single call then maybe split over a few?

or if doing it at runtime doesn't make sense, we could pre-compute this and publish the result? (if so, that could be done as an app on top of the archive that downloads the full raw data and does that computation? and publishes it in a way that looks nice, lets people sort by most quoted thing in the archive etc)

[ri72miieop]

this and some other features related to this are in the pipeline for the browser extension! doing this with the browser extension is also a plus because we can inject it literally on twitter, so it would be better for navigation/usage purposes. (also, for fun purposes I will probably put it under x.com/MakeTwitterGreatAgain/<name_for_feature lmao)

@ri72miieop that SQL query could be run from outside the DB, like from the supabase API right? If not in a single call then maybe split over a few?

I don't like this part about supabase... I am not sure tbh, their query language is weird and I don't like to use it. We could make a function to encapsulate all this though, just user and quoted_user as parameters and anyone would be able to call it from the supabase client.

or if doing it at runtime doesn't make sense, we could pre-compute this and publish the result? (if so, that could be done as an app on top of the archive that downloads the full raw data and does that computation? and publishes it in a way that looks nice, lets people sort by most quoted thing in the archive etc)

yeah this could be a possible path, both as a use-it-for-yourself app or to (pre-)compute for everyone. we could also create a materialized view on the db for this and people could still call it through the same function mentioned before (we'd need to run it through xiq tho, because this would mean using extra disk space)