Number of questions per year and number of first-day downvotes

1

Please login or register to vote for this query.

(click on this box to dismiss)

We wanted to know how many questions had at least one downvote on their first day, given a minimum net score on their first day.

Stack Overflow

Q&A for professional and enthusiast programmers

Declare @MinScore int = ##MinFirstDayScore##

SELECT
    YEAR(sel.CreationDate) AS q_year
  , sel.firstday_dvcount
  , COUNT(*) AS q_count
  , AVG(sel.firstday_uvcount - sel.firstday_dvcount) AS avg_score
FROM
  ( SELECT
        p.Id
      , p.CreationDate
      , ( SELECT
              COUNT(1)
          FROM
              Votes v
          WHERE
              v.PostId = p.Id
          AND CONVERT(date,v.CreationDate,112) = CONVERT(date,p.CreationDate,112)
          AND v.VoteTypeId = 2
        ) AS firstday_uvcount
      , ( SELECT
              COUNT(1)
          FROM
              Votes v
          WHERE
              v.PostId = p.Id
          AND CONVERT(date,v.CreationDate,112) = CONVERT(date,p.CreationDate,112)
          AND v.VoteTypeId = 3
        ) AS firstday_dvcount
    FROM
        Posts p
    WHERE
        p.PostTypeId = 1
    ) sel
WHERE
    ISNULL(@MinScore,0) = 0
OR  sel.firstday_uvcount - sel.firstday_dvcount >= @MinScore
GROUP BY
    YEAR(sel.CreationDate)
  , sel.firstday_dvcount
ORDER BY
    sel.firstday_dvcount
  , q_year

Enter Parameters

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