0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH allMyEdits AS (
  SELECT
    ph.PostId,
    MAX( ph.Id )           AS MyLatestEditId,
    MAX( ph.CreationDate ) AS MyLatestEditDateTime
  FROM
    PostHistory AS ph
  WHERE
    ph.UserId = 159145
    AND
    ph.PostHistoryTypeId = 5 /* Edit Body */
  GROUP BY
    ph.PostId
),
otherPeoplesAnswerPosts AS (
  SELECT
    p.Id AS PostId,
    p.CreationDate,
    p.LastEditDate,
    p.LastEditorUserId
  FROM
    Posts AS p
  WHERE
    p.OwnerUserId <> 159145
    AND
    p.PostTypeId = 2 /* 1 = Question, 2 = Answer */
)
/*
otherPeoplesAnswerPostsThatIveEdited AS (
  SELECT
    ap.PostId               AS "Post Link",
    ap.CreationDate         AS "Post Date/Time",
    ap.LastEditorUserId     AS "Last Edit UserId",
    me.MyLatestEditDateTime AS "My Edit Date/Time",
    ap.LastEditDate         AS "Last Edit Date/Time",
    DATEDIFF( day, me.MyLatestEditDateTime, p.LastEditDate ) AS "Days between My Edit and Last Edit"
  FROM
    allMyEdits AS ap
    INNER JOIN otherPeoplesAnswerPosts AS me ON ap.PostId = me.PostId
)
SELECT
  "Post Link",
  "My Edit Date/Time",
  CASE "Last Edit UserId" WHEN 159145 THEN 'Me' ELSE '' END AS "Last Edit By",
  "Last Edit UserId",
  "Last Edit Date/Time",
  "Days between My Edit and Last Edit"
FROM
  otherPeoplesAnswerPostsThatIveEdited
ORDER BY
  ;
*/
SELECT
  ap.PostId,
  ap.PostId               AS "Post Link",
  ap.CreationDate         AS "Post Date/Time",
--ap.LastEditorUserId     AS "Last Edit UserId",
  CASE ap.LastEditorUserId WHEN 159145 THEN 'Me' ELSE '' END AS "Last Editor",
  me.MyLatestEditDateTime AS "My Edit Date/Time",
  ap.LastEditDate         AS "Last Edit Date/Time",
  DATEDIFF( day, me.MyLatestEditDateTime, ap.LastEditDate ) AS "Days between My Edit and Last Edit"
FROM
  allMyEdits AS me
  INNER JOIN otherPeoplesAnswerPosts AS ap ON ap.PostId = me.PostId
ORDER BY
  ap.PostId DESC;

fork query

edited mar 6 23

created mar 6 23 Dai