Pareto principle visualization on the upvotes/day of the posts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Cognitive Sciences Meta

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

DECLARE @now DATETIME = (
  SELECT DATEADD(d, -7, MAX(CreationDate)) FROM Votes
);

CREATE TABLE #Result (
  No INT NOT NULL PRIMARY KEY,
  RunningUpsPerDay FLOAT NOT NULL
);

WITH Raw AS (
  SELECT
    Posts.Id AS PostId,
    1.0 * COUNT(Votes.Id) / DATEDIFF(d, MIN(Posts.CreationDate), @now)
      AS UpsPerDay
  FROM Posts
  LEFT JOIN Votes ON Votes.PostId = Posts.Id AND Votes.VoteTypeId = 2
  WHERE
    Posts.PostTypeId = ##PostTypeId:int?1##
    AND
    Posts.CreationDate < DATEADD(d, -1, @now)
  GROUP BY Posts.Id
),
Raw2 AS (
  SELECT
    RANK() OVER (ORDER BY UpsPerDay DESC, PostId) AS No,
    SUM(UpsPerDay) OVER (ORDER BY UpsPerDay DESC, PostId)
      AS RunningUpsPerDay
  FROM Raw
)
INSERT INTO #Result
SELECT *
FROM Raw2
WHERE No % ((SELECT COUNT(*) FROM Raw) / ##Points:int?1000##) = 0;

SELECT
  100.0 * (No - 1) / (SELECT TOP 1 No - 1 FROM #Result ORDER BY No DESC)
    AS PostPct,
  100.0 * RunningUpsPerDay
    / (SELECT TOP 1 RunningUpsPerDay FROM #Result ORDER BY No DESC)
    AS UpsPerDayPct
FROM #Result
ORDER BY No;

Enter Parameters

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