Log(2) of the mean upvotes to answers by the age in days

1

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

DECLARE @SiteStart DATETIME;
SELECT @SiteStart = CreationDate
FROM Posts
WHERE Id = (SELECT MIN(Id) FROM Posts);

DECLARE @SiteAge INT;
SELECT @SiteAge = DATEDIFF(day,@SiteStart,CreationDate)
FROM Posts
WHERE Id = (SELECT MAX(Id) FROM Posts);

PRINT 'Site started: ' + FORMAT(@SiteStart, 'yyyy-MM-dd', 'en-US');
PRINT 'Site age: ' + CAST(@SiteAge AS VARCHAR(MAX)) + ' days';

WITH PostDayRaw AS (
  SELECT
    DATEDIFF(day,@SiteStart,CreationDate) AS Cday,
    COUNT(*) AS Count
  FROM Posts
  WHERE
    PostTypeId = ##PostTypeId:int?2##
    AND
    CreationDate > @SiteStart
  GROUP BY DATEDIFF(day,@SiteStart,CreationDate)
),
PostDay AS (
  SELECT
    Cday,
    Count,
    SUM(Count) OVER (ORDER BY Cday ROWS UNBOUNDED PRECEDING) AS IntCount
  FROM PostDayRaw
),
VoteByDelay AS (
  SELECT
    DATEDIFF(day,Posts.CreationDate,Votes.CreationDate) AS VoteDelay,
    COUNT(*) AS Count
  FROM Votes, Posts
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.PostTypeId = ##PostTypeId##
    AND
    Votes.VoteTypeId = ##VoteTypeId:int?2##
  GROUP BY DATEDIFF(day,Posts.CreationDate,Votes.CreationDate)
  HAVING DATEDIFF(day,Posts.CreationDate,Votes.CreationDate) >= 0
)
SELECT
  VoteByDelay.VoteDelay,
  LOG(1.0 * VoteByDelay.Count / PostDay.IntCount) / LOG(2) AS MeanVote,
  LOG(1.0 * PostDay.Count) / LOG(2.0) AS PostsByAge
FROM PostDay, VoteByDelay
WHERE
  VoteByDelay.VoteDelay = @SiteAge-PostDay.Cday
  AND
  VoteByDelay.VoteDelay
    BETWEEN ##MinVoteDelay:int?0## AND ##MaxVoteDelay:int?10000##
ORDER BY VoteByDelay.VoteDelay;

Enter Parameters

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