HNQ list with moderator-removed questions only

2

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

declare @EndDate datetime = (select case
  -- If we request 'today', actually use the date
  -- of the most recent transfer to this database,
  -- which may be as much as a week in the past.
  when lower(##EndDate:string?today##) = 'today' then max(CreationDate)
  -- Otherwise, specify the most recent date to include.
  else ##EndDate##
  end
  from Posts
);
  
declare @StartDate datetime = dateadd(day, -1*##DaysBack?30##, @EndDate);

select
  enter.postid as [Post Link]
  , enter.creationdate as [Enter HNQ]
  , leave.creationdate as [Removed from HNQ]
  , leave.userid as [User Link]
  , datediff(hh, enter.creationdate, leave.creationdate) as [hours on HNQ]
  , posts.ClosedDate, posts.AnswerCount, posts.Score, posts.ViewCount
from posthistory as enter
  left outer join (
    select * from posthistory
    where
      creationdate between @StartDate and @EndDate
      and posthistorytypeid = 53
    ) as leave
  on (enter.postid = leave.postid)
  inner join posts
  on (enter.postid = posts.id)
where
  enter.creationdate between @StartDate and @EndDate
  and enter.posthistorytypeid = 52 AND
  leave.UserId IS NOT NULL
  order by enter.creationdate desc

Enter Parameters

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