Popular posts previously edited by user

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select
  p.Id as [Post Link],
  p.PostTypeId,
  p.Score,
  isnull(p.ViewCount, q.ViewCount) as ViewCount,
  p.LastEditorUserId as [User Link],
  p.LastEditDate

from
  Posts p

  left outer join Posts q on p.ParentId = q.Id

where
  -- Minimum score of post.
  p.Score >= ##MinScore##

  -- Minimum ViewCount of question.
  and isnull(p.ViewCount, q.ViewCount) >= ##MinViewCount##

  -- Filter by tag.
  -- and isnull(p.Tags, q.Tags) like '%' + ##Tag:string## + '%'

  -- Questions only.
  -- and p.PostTypeId = 1

  -- Last editor was user.
  -- and p.LastEditorUserId == ##UserId##

  -- Was previously edited by user.
  and exists (
    select 1
    from PostHistory
    where
      PostId = p.Id
      and UserId = ##UserId##
  )

  -- Was not locked at some point.
  -- Warning: Also excludes posts that are no longer locked!
  and not exists (
    select 1
    from PostHistory
    where
      PostId = p.Id
      and PostHistoryTypeId = 14  -- Locked
  )

  -- Is not currently locked.
  -- Warning: may be slow.
  -- and not exists (
  --   select 1
  --   from (
  --     select top 1 PostHistoryTypeId
  --     from PostHistory
  --     where
  --       PostId = p.Id
  --       and PostHistoryTypeId in (14, 15)  -- Locked, unlocked
  --     order by CreationDate desc
  --   ) latestLockToggle
  --   where latestLockToggle.PostHistoryTypeId = 14
  -- )

order by
  p.Score desc

;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results