The top 200 questions with the most views
Q&A for scientific skepticism
-- 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 Posts.id AS [Post Link], users.id as [User Link], Posts.CreationDate, Posts.Score as [Question Score], max(Answer.Score) as [max Answer Score], Posts.ViewCount, Posts.FavoriteCount FROM Posts inner join users on Posts.OwnerUserId = users.id left join Posts as Answer on Answer.parentid = Posts.id and Answer.posttypeid = 2 WHERE Posts.ClosedDate is null and Posts.posttypeid = 1 and Posts.CreationDate between @From_Date and @To_Date group by Posts.Id, users.id , Posts.CreationDate, Posts.Score, Posts.ViewCount, Posts.FavoriteCount ORDER BY Posts.Score DESC