Into the Tumbleweeds

1

Please login or register to vote for this query.

(click on this box to dismiss)

Pick out the older posts that people seem to be interested in, filtered by tags.

Ask Ubuntu

Q&A for Ubuntu users and developers

--2019-07-30 Gregor y
declare @StartDate  date = getdate()-##DaysAgo:int?14##;
declare @LowerBound date = dateadd(year, ##OceanClamp:int?-2##, @StartDate);

with TheOcean as (
select
    Id as [Post Link]
   ,CreationDate
   ,Score
   ,ViewCount
   ,OwnerUserId as [User Link]
   ,Tags
   ,AnswerCount
   ,CommentCount
   ,FavoriteCount
   ,ClosedDate
   ,rank() over(partition by floor(cast(CreationDate as float)) order by Score desc, FavoriteCount desc, ViewCount desc) r
from Posts
where 
   CreationDate    between @LowerBound and @StartDate
   and PostTypeId   = 1 --PostTypes.Id=1,PostTypes.Name='Question'
   and Score       >= 0
   and AnswerCount  = 0
   and CommentCount = 0
--   and ClosedDate is null
--   and DeletionDate is null
--   and CommunityOwnedDate is null
   and Tags like ('%'+ ##Tag1:string?<## + '%')
   and Tags like ('%'+ ##Tag2:string?<## + '%')
   and Tags like ('%'+ ##Tag3:string?<## + '%')
   and Tags like ('%'+ ##Tag4:string?<## + '%')
   and Tags like ('%'+ ##Tag5:string?<## + '%')
)select top ##MaxRecs:int?500## * from TheOcean where r < ##RankClamp:int?5##
order by Score desc, floor(cast(CreationDate as float)) desc, ViewCount desc
;

Enter Parameters

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