Meta question sorting

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

;with _comments as 
(
  select
   isnull(p.ParentId, p.Id) as Id
  ,count(*) as Comments
  ,cast(avg(cast(c.CreationDate as bigint)) as datetime) as AverageCommentDate
  from dbo.Comments as c
    join dbo.Posts as p
    on p.Id = c.PostId
  group by
   isnull(p.ParentId, p.Id)
)
,_votes as
(
  select
   isnull(p.ParentId, p.Id) as Id
  ,sum(iif(v.VoteTypeId = 2, 1, 0)) as Upvotes
  ,sum(iif(v.VoteTypeId = 3, 1, 0)) as Downvotes
  ,sum(iif(v.VoteTypeId = 5, 1, 0)) as Favorites
  ,count(v.Id) as TotalVotes
  ,cast(avg(cast(v.CreationDate as bigint)) as datetime) as AverageVoteDate
  from dbo.Votes as v
    left join dbo.Posts as p
    on p.Id = v.PostId
  where v.VoteTypeId in (2,3,5)
  group by
   isnull(p.ParentId, p.Id)
)
,_roundOneRankings as
(
  select
   p.Id
  ,rank() over (order by p.ViewCount) as ViewCountRank
  ,rank() over (order by p.LastActivityDate desc) as LastActivityDateRank
  ,rank() over (order by c.Comments desc) as CommentsRank
  ,rank() over (order by c.AverageCommentDate desc) as AverageCommentDateRank
  ,rank() over (order by v.Upvotes desc) as UpvoteRank
  ,rank() over (order by v.Downvotes desc) as DownvoteRank
  ,rank() over (order by v.Favorites desc) as FavoriteRank
  ,rank() over (order by v.AverageVoteDate desc) as AverageVoteDateRank
  from dbo.Posts as p
    left join _comments as c
    on c.Id = p.Id
    left join _votes as v
    on v.Id = p.Id
   where p.PostTypeId = 1
)
select top 5
 p.Id as [Post Link]
,p.LastActivityDate
,ror.*
from dbo.Posts as p
  join _roundOneRankings as ror
  on ror.Id = p.Id
order by
 ror.ViewCountRank * ##viewcountRankFactor:float?0.05##
+ror.LastActivityDateRank * ##lastActivityDateRankFactor:float?0.25##
+ror.CommentsRank * ##commentsRankFactor:float?0.05##
+ror.AverageCommentDateRank * ##averageCommentDateRankFactor:float?0.1##
+ror.UpvoteRank  * ##upvoteRankFactor:float?0.1##
+ror.DownvoteRank * ##downvoteRankFactor:float?0.1##
+ror.FavoriteRank  * ##favoriteRankFactor:float?0.1##
+ror.AverageVoteDateRank * ##averageVoteDateRankFactor:float?0.2##;

-- viewcountRankFactor: View-Count rank factor
-- lastActivityDateRankFactor: Last Activity Date rank factor
-- commentsRankFactor: Comment Count rank factor
-- averageCommentDateRankFactor: Average Comment Date rank factor
-- upvoteRankFactor: Upvote rank factor
-- downvoteRankFactor: Downvote rank factor
-- favoriteRankFactor: Favotire rank factor
-- averageVoteDateRankFactor: Average Vote Date rank factor

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results