3

Please login or register to vote for this query.

(click on this box to dismiss)

Lower value shows that the upvotes are more concentrated in some hands. Here you can narrow the query for induvidual tags: https://data.stackexchange.com/stackoverflow/query/849102/entropy-density-of-answer-upvotes-on-month-basis-tag-filterable

Q&A for ebook publishers and readers

-- 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;

WITH LogFact AS (
  SELECT * FROM @LogFact
),
Raw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, Votes.CreationDate),
      DATEPART(mm, Votes.CreationDate),
      1) AS Month,
    Posts.OwnerUserId AS UserId
  FROM Posts, Votes
  WHERE
    Posts.Id = Votes.PostId
    AND
    Posts.PostTypeId = 2
    AND
    Votes.VoteTypeId = 2
    AND
    Posts.OwnerUserId IS NOT NULL
),
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;

fork query

edited may 7 18

created may 7 18 peterh