Algorithm for trending videos,
(sql select queries)
My current query is sql select * from videos order by rand () limit 50.
When a user open the app, the initial 50 videos shown on the feed shoul be based on the above stated query.
I want to change the trending videos based on ranking and not on rand ()
I suggest few queries:
1. Lifetime top liked videos - 10 videos limit order by top liked on top
2. Monthly top liked videos - 10 videos limit order by top liked on top
3. Weekly top liked videos - 10 videos limit order by top liked on top
4. Top liked videos in last 24 hours - 10 videos limit order by top liked on top
5. Most full watched videos - 10 videos limit order by most watched video on top.
6. Verified profile videos - 5 videos limit
7. Manually reviewed video - 5 video limit
All the stated queries are to mixed and randomly order them.
The videos from 1 query should not repeat in another query.
Once a user watches a video, the same video should not repeat on the feed Next time( you need to create usser watch history table In mysql).
All the queries should be optimized as to reduce the load on server.
also i want to setup ffmpeg on app for video optimization.