Entropy density of answer upvotes on month basis, tag-filterable

1

Please login or register to vote for this query.

(click on this box to dismiss)

Lower value means more concentrated answer upvotes. Whole-site version is here: https://data.stackexchange.com/stackoverflow/query/849039/entropy-density-of-answer-upvotes-on-month-basis

Stack Overflow

Q&A for professional and enthusiast programmers

-- Stirling-formula:
-- SELECT LOG(SQRT(2*3.14*@value)) + (LOG(@value) - 1) * @value

DECLARE @LogFact TABLE (
  N INT,
  LogFact FLOAT
);

DECLARE @n INT = 1
DECLARE @logSum FLOAT = 0

WHILE @n < 100000
BEGIN
  INSERT INTO @LogFact VALUES(@n, @logSum)
  SET @n = @n + 1
  SET @logSum = @logSum + LOG(@n)
END;

CREATE TABLE #AnswersForTag (
  PostId INT NOT NULL,
  UserId INT NOT NULL
);

INSERT INTO #AnswersForTag
SELECT A.Id AS PostId, A.OwnerUserId
FROM Posts Q, Posts A, PostTags, Tags
WHERE
  A.ParentId = Q.Id
  AND
  Q.Id = PostTags.PostId
  AND
  PostTags.TagId = Tags.Id
  AND
  Tags.TagName = ##TagName:string##
  AND
  A.OwnerUserId IS NOT NULL;

WITH LogFact AS (
  SELECT * FROM @LogFact
),
Raw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, Votes.CreationDate),
      DATEPART(mm, Votes.CreationDate),
      1) AS Month,
    #AnswersForTag.UserId
  FROM #AnswersForTag, Votes
  WHERE
    #AnswersForTag.PostId = Votes.PostId
    AND
    Votes.VoteTypeId = 2
),
Raw2 AS (
  SELECT
    Month,
    UserId,
    COUNT(*) AS Count
  FROM Raw
  GROUP BY Month, UserId
),
VotesPerUserMonth AS (
  SELECT
    Month,
    UserId,
    Count,
    CASE
      WHEN Count < 100000 THEN LogFact.LogFact
      ELSE LOG(SQRT(2*3.14*Count)) + (LOG(Count) - 1) * Count
    END AS LogFactCount
  FROM Raw2
  LEFT JOIN LogFact ON Raw2.Count = LogFact.N
),
Raw3 AS (
  SELECT
    Month,
    SUM(Count) AS Count,
    COUNT(*) AS UserCount
  FROM Raw2
  GROUP BY Month
),
VotesPerMonth AS (
  SELECT
    Month,
    Count,
    UserCount,
    CASE
      WHEN Count < 100000 THEN LogFact.LogFact
      ELSE LOG(SQRT(2*3.14*Count)) + (LOG(Count) - 1) * Count
    END AS LogFactCount
  FROM Raw3
  LEFT JOIN LogFact ON Raw3.Count = LogFact.N
)
SELECT
  VotesPerMonth.Month,
  (MIN(VotesPerMonth.LogFactCount) - SUM(VotesPerUserMonth.LogFactCount))
    / MIN(UserCount)
  AS EntropyDensity
FROM VotesPerMonth, VotesPerUserMonth
WHERE VotesPerMonth.Month = VotesPerUserMonth.Month
GROUP BY VotesPerMonth.Month
ORDER BY VotesPerMonth.Month;

Enter Parameters

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