Estimated creation timestamps of the votes on your posts

1

Please login or register to vote for this query.

(click on this box to dismiss)

Its precision is around a minute on the SO. You need to use your SE Account Id, not your site account id!

Computational Science

Q&A for scientists using computers to solve scientific problems

-- PostHistoryTypes: 10, 11, 12, 13
-- VoteTypes: 6, 7, 10, 11

--SELECT * FROM PostHistoryTypes;
--SELECT * FROM VoteTypes;

WITH PhRaw AS (
  SELECT
    Id,
    PostId,
    PostHistoryTypeId,
    CreationDate,
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      DATEPART(d, CreationDate)
    ) AS Day
  FROM PostHistory
  WHERE
    PostHistoryTypeId IN (10, 11, 12, 13)
),
PhRawMaxId AS (
  SELECT MAX(Id) AS LastId
  FROM PhRaw
  GROUP BY PostId, PostHistoryTypeId, Day
),
Ph AS (
  SELECT *
  FROM PhRaw
  WHERE Id IN (SELECT LastId FROM PhRawMaxId)
),
VoteRaw AS (
  SELECT
    Id,
    PostId,
    VoteTypeId,
    CreationDate
  FROM Votes
  WHERE VoteTypeId IN (6, 7, 10, 11)
),
VoteMaxId AS (
  SELECT MAX(Id) AS LastId
  FROM VoteRaw
  GROUP BY PostId, VoteTypeId, CreationDate
),
Vote AS (
  SELECT *
  FROM VoteRaw
  WHERE Id IN (SELECT LastId FROM VoteMaxId)
),
Conn AS (
  SELECT 10 AS PostHistoryTypeId, 6 AS VoteTypeId
  UNION
  SELECT 11, 7
  UNION
  SELECT 12, 10
  UNION
  SELECT 13, 11
),
VoteDate AS (
  SELECT
    Vote.Id AS Id,
    Ph.CreationDate,
    RANK() OVER (ORDER BY Vote.Id) AS TmpId
  FROM Ph, Conn, Vote
  WHERE
    Ph.PostId = Vote.PostId
    AND
    Ph.PostHistoryTypeId = Conn.PostHistoryTypeId
    AND
    Conn.VoteTypeId = Vote.VoteTypeId
),
VoteFilteredRaw AS (
  SELECT
    Prev.Id,
    Prev.CreationDate,
    1.0 * DATEDIFF(s, Prev.CreationDate, Next.CreationDate)
      / (Next.Id - Prev.Id) AS d
  FROM VoteDate Prev, VoteDate Next
  WHERE
    Prev.TmpId + 1 = Next.TmpId
),
VotesOnYou AS (
  SELECT Votes.Id
  FROM Votes, Posts
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.OwnerUserId =
      (SELECT Id FROM Users WHERE AccountId = ##SEAccountId:int##)
),
VoteFiltered AS (
  SELECT Id, CreationDate, d
  FROM VoteFilteredRaw
  WHERE d >= 0.1 AND d < 20
  
  UNION
  
  SELECT Id, NULL, NULL
  FROM VotesOnYou
),
Raw AS (
  SELECT *, RANK() OVER (ORDER BY Id) AS TmpId
  FROM VoteFiltered
),
Raw2 AS (
  SELECT
    Next.Id,
    DATEADD(s, Prev.d * (Next.Id - Prev.Id), Prev.CreationDate) AS CreationDate
  FROM Raw Prev, Raw Next
  WHERE
    Prev.TmpId + 1 = Next.TmpId
    AND
    Prev.d IS NOT NULL
    AND
    Next.d IS NULL
)
SELECT
  Raw2.Id AS VoteId,
  Votes.PostId AS PostId,
  Votes.PostId AS [Post Link],
  VoteTypes.Name AS VoteType,
  Votes.CreationDate AS VoteDate,
  Raw2.CreationDate AS EstimatedDate
FROM Raw2
LEFT JOIN Votes ON Raw2.Id = Votes.Id
LEFT JOIN VoteTypes ON Votes.VoteTypeId = VoteTypes.Id
ORDER BY Raw2.Id

Enter Parameters

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