I am trying to create a Trending Posts database call for my homepage.
The best way I thought of doing this, would be by making 2 integer tables.
The problem I am having, is sorting these results.
Here is what my table looks like, I keep track of the last 10 trending posts.
name trending clicks
post1 10 5
post2 9 15
post3 8 12
post4 7 10
post5 6 8
post6 5 8
post7 4 22
post8 3 18
post9 2 8
post10 1 8
The Trending Position is updated every 4 hours and clicks are reset to 0.
From this data, I need to pull the posts in this order
name trending clicks
post6 5 22
post8 3 18
post2 9 15
post3 8 12
post4 7 10
post5 6 8
post9 2 8
post10 1 8
post1 10 5
Here is another example:
name trending clicks
post1 10 0
post5 6 9
post2 9 0
post3 8 0
post4 7 0
post6 5 0
post7 4 0
post10 1 3
post8 3 0
post9 2 0
I need to sort these results, based on the clicks ONLY if they are above the trending position.
Example: If post #8 get's more clicks than position #4, it will take it's spot and keep the order of the trending, but use the clicks to also order the data.
The original posts will keep their position until a trending post has gotten more clicks than the position to take it's place.
The problem i'm trying to resolve, is when I reset the trending data. I reset the data every 4 hours but want to keep the positions and weight on each trending post.
Did I explain this well enough and can anybody please help me?
Why this doesnt work for you ?
SELECT name, trending, clicks
FROM YourTable
ORDER BY clicks DESC
But if you want keep previous trend, then you need add a weight column.
SELECT name, trending, clicks, weight
FROM YourTable
ORDER BY ((clicks + weight) /2) DESC
Instead of reset your trend every 4h wouldnt be easy calculate the clicks on the last 4h. You have to record datatime for each click
SELECT name, count(clicks) as clicks
FROM YourTable
WHERE datetime > DATEADD(h,-4,GETDATE())
GROUP BY name
ORDER BY count(clicks) DESC