C:\GAMES\DOOM\SETUP.COM

1

Please login or register to vote for this query.

(click on this box to dismiss)

Percentage of the posts posted by users having floor(log(n)) posts already, by month.

Cognitive Sciences Meta

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

DECLARE @now DATETIME = (SELECT MAX(CreationDate) FROM Posts);

SET @now = DATEFROMPARTS(YEAR(@now), MONTH(@now), 1);

CREATE TABLE #tmp (
  Month DATETIME NOT NULL,
  LogPostN INT NOT NULL,
  Count INT NOT NULL,
  PRIMARY KEY(Month, LogPostN)
);

WITH Raw AS (
  SELECT
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month,
    RANK() OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS UserN
  FROM Posts
  WHERE
    OwnerUserId > 0
    AND
    CreationDate < @now
),
Raw2 AS (
  SELECT
    Month,
    FLOOR(LOG(UserN) / LOG(##LogBase:int?2##)) AS LogPostN
  FROM Raw
)
INSERT INTO #tmp
SELECT Month, LogPostN, COUNT(*) AS Count
FROM Raw2
GROUP BY Month, LogPostN;

WITH Months AS (
  SELECT Month FROM #tmp GROUP BY Month
),
LogPostNs AS (
  SELECT LogPostN FROM #tmp GROUP BY LogPostN
),
Raw4 AS (
  SELECT
    Months.Month,
    LogPostNs.LogPostN,
    COALESCE(#tmp.Count, 0) AS Count
  FROM Months
  LEFT JOIN LogPostNs ON 1=1
  LEFT JOIN #tmp
    ON Months.Month = #tmp.Month AND LogPostNs.LogPostN = #tmp.LogPostN
),
Smooth AS (
  SELECT
    A.Month,
    A.LogPostN,
    1.0
      * SUM(B.Count
            * (##SmoothN## - ABS(DATEDIFF(MONTH, A.Month, B.Month)))
           )
      / SUM(##SmoothN## - ABS(DATEDIFF(MONTH, A.Month, B.Month)))
      
      AS Count
  FROM Raw4 A, Raw4 B
  WHERE
    A.LogPostN = B.LogPostN
    AND
    ABS(DATEDIFF(MONTH, A.Month, B.Month)) < ##SmoothN:int?3##
  GROUP BY A.Month, A.LogPostN
),
Totals AS (
  SELECT Month, SUM(Count) AS Count FROM Smooth GROUP BY Month
)
SELECT
  Smooth.Month,
  CAST(POWER(##LogBase##, Smooth.LogPostN) AS VARCHAR(MAX))
    + ' - '
    + CAST(POWER(##LogBase##, Smooth.LogPostN + 1) - 1
           AS VARCHAR(MAX))
    AS PostCount,
  100.0 *
    SUM(Smooth.Count)
      OVER (PARTITION BY Smooth.Month ORDER BY Smooth.LogPostN)
    / Totals.Count
    AS Pct
FROM Smooth, Totals
WHERE Smooth.Month = Totals.Month
ORDER BY Smooth.Month, Smooth.LogPostN DESC

Enter Parameters

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