Questions per year having minimum net score

1

Please login or register to vote for this query.

(click on this box to dismiss)

With link, downvotes-answers-comments counters, owner, ranking by views

Stack Overflow

Q&A for professional and enthusiast programmers

Declare @MinScore smallint = ##MinScore##;
Declare @CreationYear smallint = ##CreationYear##;
Declare @InDays smallint = ##VotesInDays##;
Declare @PostTypeId smallint = ##PostTypeId##;

--InDays: Net score as sum of votes cast in "InDays" after post's creation date
--InDays = 0: net score on creation date

SELECT
    ROW_NUMBER() OVER(ORDER BY p.Score DESC) AS rownum
  , p.Id
  , p.Id AS [Post Link]
  , p.PostTypeId
  , p.CreationDate
  , p.Score
  , p.ViewCount
  , p.OwnerUserId
  , ( SELECT
          u.DisplayName
      FROM
          Users u
      WHERE
          u.Id = p.OwnerUserId
    ) AS p_owner_name
  , p.Title
  , p.Tags
  , p.AnswerCount
  , p.CommentCount
  , ( SELECT
        COUNT(1)
      FROM
          Votes v
      WHERE
          v.PostId = p.Id
      AND v.VoteTypeId = 3
      AND CONVERT(date,v.CreationDate,112) = CONVERT(date,p.CreationDate,112)
    ) AS p_downvotes
  , ( SELECT
          COUNT(1) + 1
      FROM
          Posts pv
      WHERE
          pv.PostTypeId = @PostTypeId
      AND pv.ViewCount > p.ViewCount
    ) AS p_views_rank
FROM
    Posts p
WHERE
    p.PostTypeId = @PostTypeId
AND YEAR(p.CreationDate) = @CreationYear
AND @MinScore <=
  ( ( SELECT
          COUNT(1)
      FROM
          Votes v
      WHERE
          v.PostId = p.Id
      AND v.VoteTypeId = 2
      AND CONVERT(date,v.CreationDate,112) <= DATEADD(day,@InDays,CONVERT(date,p.CreationDate,112))
    ) -
    ( SELECT
          COUNT(1)
      FROM
          Votes v
      WHERE
          v.PostId = p.Id
      AND v.VoteTypeId = 3
      AND CONVERT(date,v.CreationDate,112) <= DATEADD(day,@InDays,CONVERT(date,p.CreationDate,112))
    )
  )
ORDER BY
    p.CreationDate DESC

Enter Parameters

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