1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero

WITH Raw AS (
  SELECT
    A.Id,
    A.Score,
    DATEDIFF(s, Q.CreationDate, A.CreationDate) AS Seconds
  FROM Posts Q, Posts A
  WHERE
    A.ParentId = Q.Id
    AND
    A.CreationDate > Q.CreationDate
    AND
    Q.OwnerUserId != A.OwnerUserId
    AND
    Q.Id NOT IN (
      SELECT PostId
      FROM PostHistory
      WHERE PostHistoryTypeId = 18
    )
),
Raw2 AS (
  SELECT
    Id,
    Score,
    Seconds
  FROM Raw
  WHERE Seconds > 0
),
Raw3 AS (
  SELECT
    Id,
    Score,
    FLOOR(LOG(Seconds)/LOG(##LogBase:float?2##)) AS LogSeconds
  FROM Raw2
),
Raw4 AS (
  SELECT
    LogSeconds,
    PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY Score)
      OVER (PARTITION BY LogSeconds) AS MedianScore
  FROM Raw3
)
SELECT DISTINCT LogSeconds, MedianScore
FROM Raw4
ORDER BY LogSeconds

fork query

edited may 8 19

created may 8 19 peterh (2)