0

Please login or register to vote for this query.

(click on this box to dismiss)

The top 200 questions with the most views

Q&A for those who create, solve, and study puzzles

-- Questions with most views
-- The top 20 questions with the most views
DECLARE @To_Date datetime = dateadd(mm,3, CAST ('##StartDate##' as datetime)) 
DECLARE @From_Date datetime = CAST( '##StartDate##' as datetime)

Print 'from ' + format(@From_Date, 'D') + ' to ' + format(@To_Date, 'D')
/*
SELECT TOP 20 Posts.Id
AS [Post Link],
users.id as [User Link],
Posts.CreationDate,
Score,
ViewCount, 
FavoriteCount
FROM Posts 
inner join
users on
Posts.OwnerUserId = users.id
WHERE ClosedDate is null
      and Posts.CreationDate between @From_Date and @To_Date
ORDER BY ViewCount DESC
*/

SELECT TOP 20 Question.Id
AS [Question Link],
users.id as [User Link],
Question.CreationDate,
Question.Score as [Question Score],
Question.ViewCount, 
Question.FavoriteCount, 
max(Answer.Score) as [max Answer Score]
FROM Posts as Question
inner join
users on
Question.OwnerUserId = users.id
left join
Posts as Answer
on Answer.parentid = Question.id and Answer.posttypeid = 2
WHERE Question.ClosedDate is null
      and Question.posttypeid = 1
      and Question.CreationDate between @From_Date and @To_Date
group by Question.Id,
users.id ,
Question.CreationDate,
Question.Score,
Question.ViewCount, 
Question.FavoriteCount   
ORDER BY Question.Score DESC

view current version

edited apr 5 13 davrob60

created nov 19 11 Hippo