How many times has the roomba overriden human votes?

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackoverflow.com/questions/403071

Stack Overflow

Q&A for professional and enthusiast programmers

declare @start nvarchar(10) = ##start:string?2019-11-01## 
declare @end nvarchar(10) = ##end:string?2020-11-01## 


;with roomba as
(
-- Number of questions still deleted by roomba
select p.id
     , deletiondate
from postswithdeleted p
where deletiondate between @start and @end
and posttypeid = 1
-- roomba runs within a few minutes at top of 00:00 or 03:00
and datepart(hh,deletiondate) in (0, 3)
and datepart(mi,deletiondate) between 0 and 1
),
-- Number of questions posted
questions as
(
  select p.id
  , creationdate
  from postswithdeleted p
  where deletiondate between @start and @end
  and posttypeid = 1
), 
-- Number of questions (deleted by roomba) that where undeleted at a later time at least once
undeleted as 
(
  select distinct postid
  from votes
  where creationdate between @start and @end
  and votetypeid = 11 -- undelete
),
--Number of questions deleted by roomba that where undeleted at a later time at least once, and then deleted by roomba again.
deleted_again as 
(
  select p.id
       , deletiondate
  from postswithdeleted p
  where deletiondate between @start and @end
  and posttypeid = 1
  -- roomba runs within a few minutes at top of 00:00 or 03:00
  and datepart(hh,deletiondate) in (0, 3)
  and datepart(mi,deletiondate) between 0 and 1
  and exists (select 1 
              from votes 
              where votetypeid = 11 -- undelete  
              and postid = p.id)
)


select 
  (select count(*) from questions) questions
, (select count(*) from roomba) roomba
, (select count(*) from undeleted) undeleted
, (select count(*) from deleted_again) deleted_again

Enter Parameters

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