Relative distribution of voting and posting activities

1

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

WITH VoteRaw AS (
  SELECT
    CreationDate,
    FLOOR(LOG(COUNT(*))/LOG(##LogBase:float?1.05##)) AS LogCount
  FROM Votes
  GROUP BY CreationDate
),
VoteStat AS (
  SELECT
    POWER(##LogBase##, LogCount) AS VoteCount,
    COUNT(*) AS DayCount
  FROM VoteRaw
  GROUP BY LogCount
),
VoteStatNorm AS (
  SELECT
    VoteCount / (SELECT MAX(VoteCount) FROM VoteStat) AS RelVoteCount,
    DayCount
  FROM VoteStat
),
PostRaw AS (
  SELECT
    DATEFROMPARTS(
      YEAR(CreationDate),
      MONTH(CreationDate),
      DAY(CreationDate)) AS CreationDate,
    FLOOR(LOG(COUNT(*))/LOG(##LogBase##)) AS LogCount
  FROM PostsWithDeleted
  GROUP BY
    DATEFROMPARTS(
      YEAR(CreationDate),
      MONTH(CreationDate),
      DAY(CreationDate))
),
PostStat AS (
  SELECT
  POWER(##LogBase##, LogCount) AS PostCount,
  COUNT(*) AS DayCount
  FROM PostRaw
  GROUP BY LogCount
),
PostStatNorm AS (
  SELECT
    PostCount / (SELECT MAX(PostCount) FROM PostStat) AS RelPostCount,
    DayCount
  FROM PostStat
)
SELECT
  RelVoteCount AS RelCount,
  'Vote' AS Type,
  DayCount
FROM VoteStatNorm

UNION

SELECT
  RelPostCount AS RelCount,
  'Post' AS Type,
  DayCount
FROM PostStatNorm

ORDER BY RelCount, Type

Enter Parameters

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