Number of posts per year and pivoted number of first-day downvotes

1

Please login or register to vote for this query.

(click on this box to dismiss)

On the rows, years. On the columns, number of posts that had 0, 1, 2, 3, 4, or 5+ downvotes on their creation date + the specified interval in days, plus their respective average score over the same time interval. Only posts scoring at least the specified threshold over that interval are selected.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Input:
-- Post Type Id = type of post; 1 = question, ...
-- Min First Day Score = only post that scored above this threshold within creation date + VotesWithinDays
-- Votes Within Days = only consider votes created within post creation date + VotesWithinDays
-- From Year = only posts created since this year, inclusive
-- To Year = only posts created up to this year, inclusive

-- Columns:
-- 1 - Year
-- 2 - Total # of posts matching years and score threshold
-- 3 - # of posts defined at previous point, but only those that had 0 downvotes within post creation date + VotesWithinDays
-- 4 - Average score of posts identified at previous point
-- 5 - # of posts ..., but only those that had 1 downvote within ...
-- 6 - Average score of posts identified ...
-- 7 - # of posts ... that had 2 downvotes within ...
-- 8 - Average score ...
-- 9 - # of posts ... that had 3 downvotes within ...
-- 10- Average score ...
-- 11- # of posts ... that had 4 downvotes within ...
-- 12- Average score ...
-- 13- # of posts ... that had 5 or more downvotes within ...
-- 14- Avg ...

Declare @PostTypeId tinyint = ##PostTypeId##;
Declare @MinScore int = ##MinFirstDayScore##;
Declare @InDays int = ##VotesWithinDays##;
Declare @FromYear int = ##FromYear##;
Declare @ToYear int = ##ToYear##;
-- InDays = 0 => same creation date for post and votes
-- InDays = 1 => votes on post creation day and the day after
-- InDays = 2 ...
-- FromYear and ToYear are inclusive

WITH sel_cte (
    q_year
  , firstday_dvcount
  , q_count
  , avg_score
)
AS (
  SELECT
      YEAR(sel.CreationDate) AS q_year
    , CASE
        WHEN sel.firstday_dvcount > 4 THEN 5
        ELSE sel.firstday_dvcount
      END firstday_dvcount
    , COUNT(1) AS q_count
    , CONVERT(
          decimal(10,2)
        , AVG(
            CONVERT(
                decimal(10,5)
              , (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) <= DATEADD(day,@InDays,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) <= DATEADD(day,@InDays,CONVERT(date,p.CreationDate,112))
            AND v.VoteTypeId = 3
          ) AS firstday_dvcount
      FROM
          Posts p
      WHERE
          p.PostTypeId = @PostTypeId
      AND YEAR(p.CreationDate) BETWEEN @FromYear AND @ToYear
      ) sel
  WHERE
      (sel.firstday_uvcount - sel.firstday_dvcount) >= @MinScore
  GROUP BY
      YEAR(sel.CreationDate)
    , CASE
        WHEN sel.firstday_dvcount > 4 THEN 5
        ELSE sel.firstday_dvcount
      END
)
SELECT
    p1.q_year
  , ( SELECT
          SUM(q_count)
      FROM
          sel_cte s
      WHERE
          s.q_year = p1.q_year
    ) AS q_count_tot 
  , p1.[0] AS "0_dv_fd"
  , ( SELECT
          s.avg_score
      FROM
          sel_cte s
      WHERE
          s.q_year = p1.q_year
      AND s.firstday_dvcount = 0
    ) AS avg_score_0
  , p1.[1] AS "1_dv_fd"
  , ( SELECT
          s.avg_score
      FROM
          sel_cte s
      WHERE
          s.q_year = p1.q_year
      AND s.firstday_dvcount = 1
    ) AS avg_score_1
  , p1.[2] AS "2_dv_fd"
  , ( SELECT
          s.avg_score
      FROM
          sel_cte s
      WHERE
          s.q_year = p1.q_year
      AND s.firstday_dvcount = 2
    ) AS avg_score_2
  , p1.[3] AS "3_dv_fd"
  , ( SELECT
          s.avg_score
      FROM
          sel_cte s
      WHERE
          s.q_year = p1.q_year
      AND s.firstday_dvcount = 3
    ) AS avg_score_3
  , p1.[4] AS "4_dv_fd"
  , ( SELECT
          s.avg_score
      FROM
          sel_cte s
      WHERE
          s.q_year = p1.q_year
      AND s.firstday_dvcount = 4
    ) AS avg_score_4
  , p1.[5] AS "5+_dv_fd"
  , ( SELECT
          s.avg_score
      FROM
          sel_cte s
      WHERE
          s.q_year = p1.q_year
      AND s.firstday_dvcount = 5
    ) AS "avg_score_5+"
FROM
  ( SELECT
        q_year
      , firstday_dvcount
      , q_count
    FROM
        sel_cte
  ) sq_cnt
PIVOT
  (   SUM(q_count)
  FOR firstday_dvcount
  IN  ( [0], [1], [2], [3], [4], [5] )
  ) AS p1
ORDER BY
    p1.q_year
;

Enter Parameters

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