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

Q&A for professional and enthusiast programmers

-- 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