Popular posts not previously edited by user

1

Please login or register to vote for this query.

(click on this box to dismiss)

The goal is to find important posts that may be in need of editing.

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,
  (
    select count(distinct ph.RevisionGUID)
    from PostHistory ph
    where PostId = p.Id
  ) as EditCount

from
  Posts p

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

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

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

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

  -- Questions only <= 1. Questions and answers <= 2.
  and p.PostTypeId <= ##MaxPostType?1##

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

  -- Was not previously edited by user.
  and not 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