Yet another Votes.CreationDate timestamp estimator

1

Please login or register to vote for this query.

(click on this box to dismiss)

This works better, but unfortunately it is too slow for the StackOverflow.

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

-- last close/delete votes for all posts
WITH LastCloseDeleteRaw AS (
  SELECT PostId, VoteTypeId, MAX(Id) AS VoteId
  FROM Votes
  WHERE VoteTypeId IN (6, 10)
  GROUP BY PostId, VoteTypeId
),
-- their exact creation timestamp is the closure/deletion timestamp
LastCloseDeleteRaw2 AS (
  SELECT
    LastCloseDeleteRaw.VoteId,
    CASE
      WHEN LastCloseDeleteRaw.VoteTypeId = 6
      THEN PostsWithDeleted.ClosedDate
      ELSE PostsWithDeleted.DeletionDate
    END AS CreationDate
  FROM LastCloseDeleteRaw, PostsWithDeleted
  WHERE
    LastCloseDeleteRaw.PostId = PostsWithDeleted.Id
),
-- reopened/undeleted posts have NULL closure/deletion timestamp,
-- we remove them
LastCloseDeleteRaw3 AS (
  SELECT
    VoteId,
    CreationDate
  FROM LastCloseDeleteRaw2
  WHERE CreationDate IS NOT NULL
),
-- remove misleading results due to missing closure/deletion votes from the db
-- we also number them for time-delta calculation
LastCloseDeleteRaw4 AS (
SELECT
    RANK() OVER (ORDER BY LastCloseDeleteRaw3.VoteId) AS No,
    LastCloseDeleteRaw3.VoteId,
    LastCloseDeleteRaw3.CreationDate
FROM LastCloseDeleteRaw3, Votes
WHERE
  Votes.Id = LastCloseDeleteRaw3.VoteId
  AND
  DATEDIFF(s, Votes.CreationDate, LastCloseDeleteRaw3.CreationDate) >= 0
  AND
  DATEDIFF(s, Votes.CreationDate, LastCloseDeleteRaw3.CreationDate) < 86400
),
-- we calculate "d"
LastCloseDeleteRaw5 AS (
  SELECT
    Prev.VoteId,
    Prev.CreationDate,
    1.0 * DATEDIFF(s, Prev.CreationDate, Next.CreationDate)
      / (Next.VoteId - Prev.VoteId) AS d
  FROM
    LastCloseDeleteRaw4 Prev,
    LastCloseDeleteRaw4 Next
  WHERE
    Prev.No + 1 = Next.No
),
-- sometimes posts are closed/deleted LATER by an EARLIER close/delete vote,
-- resulting bad "d"-values, they need to be eliminated
VotesDate AS (
  SELECT *
  FROM LastCloseDeleteRaw5
  WHERE
    d > 0
    AND
    d < (SELECT AVG(d) * 5 FROM LastCloseDeleteRaw5)
),
Raw AS (
  SELECT
    Votes.Id,
    Votes.CreationDate AS DbDate,
    VotesDate.CreationDate,
    VotesDate.d,
    MAX(
      CASE WHEN VotesDate.CreationDate IS NOT NULL THEN Votes.Id
      ELSE NULL END
    ) OVER (ORDER BY Votes.Id ROWS UNBOUNDED PRECEDING) AS LastKnownId
  FROM Votes
  LEFT JOIN VotesDate ON Votes.Id = VotesDate.VoteId
)
SELECT
  Result.Id,
  Result.DbDate,
  Result.d,
  DATEADD(s, Last.d * (Last.Id - Result.Id), Last.CreationDate) AS CreationDate
FROM Raw Result, Raw Last
WHERE
  Result.LastKnownId IS NOT NULL
  AND
  Result.LastKnownId = Last.Id
ORDER BY Result.Id

Enter Parameters

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