Average daily upvotes by the upvotes you've already got

1

Please login or register to vote for this query.

(click on this box to dismiss)

Reputation differs only about 10% from 10 * (upvotes got) in about 90% of the users.

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

CREATE TABLE #Raw (
  HasUps INT NOT NULL PRIMARY KEY,
  AvgDailyUps FLOAT NOT NULL
);

WITH Raw AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    Votes.CreationDate AS Date,
    COUNT(*) AS Count
  FROM Posts, Votes
  WHERE
    Votes.PostId = Posts.Id
    AND
    Votes.VoteTypeId = 2
    AND
    Posts.OwnerUserId > 0
  GROUP BY Posts.OwnerUserId, Votes.CreationDate
),
Raw2 AS (
  SELECT
    UserId,
    Date,
    Count,
    SUM(Count) OVER (PARTITION BY UserId ORDER BY Date) AS CumCount
  FROM Raw
),
Raw3 AS (
  SELECT
    UserId,
    Date,
    LAG(CumCount) OVER (PARTITION BY UserId ORDER BY Date) AS HasUps,
    DATEDIFF(
      D,
      LAG(Date) OVER (PARTITION BY UserId ORDER BY Date),
      DATE) AS DaysSinceLastUp,
    Count AS UpsGot
  FROM Raw2
),
Raw4 AS (
  SELECT
    HasUps,
    AVG(1.0 * UpsGot / DaysSinceLastUp) AS AvgUps,
    DaysSinceLastUp * COUNT(*) AS Weight
  FROM Raw3
  WHERE HasUps IS NOT NULL
  GROUP BY HasUps, DaysSinceLastUp
)
INSERT INTO #Raw
SELECT
  HasUps,
  1.0 * SUM(AvgUps * Weight) / SUM(Weight) AS AvgDailyUps
FROM Raw4
GROUP BY HasUps;

WITH Raw5 AS (
  SELECT
    FLOOR(LOG(HasUps) / Log(##LogBase:float?1.3##)) AS LogHasUps,
    AvgDailyUps
  FROM #Raw
)
SELECT
  FLOOR(EXP(LOG(##LogBase##) * LogHasUps) + 0.9999) AS HasUps,
  AVG(1.0 * AvgDailyUps) AS AvgDailyUps
FROM Raw5
GROUP BY LogHasUps
ORDER BY HasUps

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results