Use the number of stars for trending projects
Trending projects are currently calculated based on the number of notes. This is quite silly because the number of notes has very little to do with a project being trending. For example, a project can have thousands and thousands of notes all made by 2 people; while nobody has ever heard of the project.
As an alternative we should use the data stored in users_star_projects
. The idea is pretty simple: every N hours we calculate the top 100 projects with the most stars in the past N hours, instead of using notes. While not entirely accurate (e.g. a project can get many page views without stars), it's much better than using notes.
The query for this should be pretty straightforward (from the top of my head):
INSERT INTO trending_projects (project_id)
SELECT projects.id
INNER JOIN projects ON projects.id = users_star_projects.project_id
FROM users_star_projects
WHERE users_star_projects.created_at >= now() - '24 hours'::interval
AND projects.visibility_level = 20
GROUP BY projects.id
ORDER BY COUNT(*) DESC
LIMIT 100;
Edited by Yorick Peterse