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.

Q&A for Drupal developers and administrators

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

fork query

edited jan 20

created jan 20 peterh - PLEASE DELETE THIS ACCOUNT