0

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?

Steve Payne
  • 612
  • 3
  • 8
  • 16

1 Answers1

2

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
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Great idea with the clicks + weight. I am trying this now!!! I am unable to use a timestamp. When I do that, some of these posts will never move positions. Sometimes there are hundreds of clicks between #1 and #2. I hope that by resetting the posts often, will give other posts a chance to go to the top. – Steve Payne Sep 30 '15 at 16:21
  • Another way is use a formula to assign a weight to each click based in how old that click is. Something like `ClickWeight = (1/number_of_second)`, then your trending is `SUM(ClickWeight)` The real work here is find a proper Weight function. I did a Real Time Traffic function once. Where every car during the last 5 min have the same weight, but after that was an exponential decay. That way newest data always have more weight, but if no data older data will add to the result as well. – Juan Carlos Oropeza Sep 30 '15 at 16:29
  • Yea, Juan. I am trying to make new data able to compete with older data. Your example of SELECT * FROM test ORDER BY ((position + clicks) /2) DESC works perfectly! I wasn't aware that I could sort like that. Thanks so much!!!! – Steve Payne Sep 30 '15 at 16:35
  • Good that work for you, but again you have to be carefull because that was a simple formula. And if previous 4h a post had lot of trend will need several reset to drop down the top. That is why I suggest a decay function. – Juan Carlos Oropeza Sep 30 '15 at 16:37