Stay in the loop with everything that's happening in web3 - a simple HackerNews/RektNews-style page with the most frequently shared links
![](https://private-user-images.githubusercontent.com/94986441/238150394-20c94720-fcc5-4e69-8f87-c64abe074dea.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjU3NjEwMjQsIm5iZiI6MTcyNTc2MDcyNCwicGF0aCI6Ii85NDk4NjQ0MS8yMzgxNTAzOTQtMjBjOTQ3MjAtZmNjNS00ZTY5LThmODctYzY0YWJlMDc0ZGVhLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA5MDglMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwOTA4VDAxNTg0NFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWM0ZjUyNjAwYTk0ODg3NGNlNDJkYWM5NGYwMjRlZTI5NTIyOWU5N2M1MTM1ZTcwZmJkMjk5NjEwN2Y5ZTE5ODAmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.b3_MRsycgHTLo77b-DEb-rSR1oSzrEmCA7CJkGamFOY)
- Allow community members to vote on the list of Twitter accounts
- Find last tweet id in DB
- Fetch new tweets with urls since last tweet id via Twitter API
- Find urls and save titles of the websites
- Build leaderboard for site based on urls shared in the previous days
- count multiple url shares only once per twitter account
- give each url a score
- add retweets / nr. of links to tweet to score
Run
pip install pipenv
pipenv install
brew cask install geckodriver
(on first run a confirmation in Security Preferences might be required)
SQL Query to create Scored News Items
in DB
CREATE OR REPLACE view ScoredNewsItem as
WITH Web3NewsItem as (SELECT ni.id,
ARRAY_AGG(DISTINCT tag.title) as tags
FROM "NewsItem" ni
join "NewsItemToTag" ni2tag on ni.id = ni2tag.news_item_id
join "Tag" tag on ni2tag.tag_id = tag.id
group by ni.id
having 'Web3' = ANY (ARRAY_AGG(DISTINCT tag.title)))
select CEIL(AVG(COALESCE(author.score, 0)) * count(DISTINCT author.twitter_id))::integer as score,
count(DISTINCT author.twitter_id) as count_unique_authors,
count(author.twitter_id) as author_ids,
max(tweet.created_at) as last_tweet_date,
inner_ni.tags,
ni.*
from Web3NewsItem inner_ni
join "NewsItem" ni on ni.id = inner_ni.id
join "NewsItemToTweet" ni2tweet on ni.id = ni2tweet.news_item_id
join "Tweet" tweet on tweet.id = ni2tweet.tweet_id
join "Author" author on tweet.author_id = author.twitter_id
where tweet.created_at::date >= CURRENT_DATE - interval '7 day'
group by ni.id, inner_ni.tags