Big Deletion Days

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

with PDates as (
select distinct cast (creationDate as Date) as PDate
from PostsWithDeleted
UNION
select distinct cast (deletionDate as Date)
from PostsWithDeleted
where deletionDate is not null
), DelCount as (
  select cast (DeletionDate as Date) as DDate,
         count (*) as [DCount]
  from PostsWithDeleted
  group by cast (DeletionDate as Date)
), CreateCount as (
  select cast (CreationDate as Date) as CDate,
         count (*) as [CCount]
  from PostsWithDeleted
  group by cast (CreationDate as Date)
)
select PDate as Day,
       IsNull(CCount, 0) as [Created],
       IsNull(DCount, 0) as [Deleted],
       IsNull(CCount, 0) - IsNull(DCount, 0) as [Delta]
from PDates
    left outer join CreateCount on PDate = CDate
    left outer join DelCount on PDate = DDate

Enter Parameters

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