Post Analysis

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

--Deleted post count per year
SELECT count(id) as "Deleted post count", year(DeletionDate) as "Date"
from PostsWithDeleted 
group by year(DeletionDate);

--SELECT Name, Id from PostHistoryTypes;

--Reopened post count
SELECT count(PostId) as "Reopened Post Count", year(CreationDate) as "Year"
from PostHistory
Where (PostHistoryTypeId = 11) 
and year(CreationDate) > 2003
group by year(CreationDate);

--Undeleted post count
SELECT count(PostId) as "Undeleted Post Count", year(CreationDate) as "Year"
from PostHistory
Where (PostHistoryTypeId = 13) 
and year(CreationDate) > 2003
group by year(CreationDate);

--unlocked post count
SELECT count(PostId) as "Unlocked Post Count", year(CreationDate) as "Year"
from PostHistory
Where (PostHistoryTypeId = 15) 
and year(CreationDate) > 2003
group by year(CreationDate);

--SELECT Id, Name from PostTypes;

--User deleted post count
SELECT count(PostsWithDeleted.Id) as "User Deleted Count", Year(ClosedDate) as "Year"
from PostsWithDeleted INNER JOIN PostHistory 
on PostsWithDeleted.Id = PostHistory.PostId
where PostsWithDeleted.OwnerUserId = PostHistory.UserId
group by Year(ClosedDate);

--Deleted because duplication
SELECT count(PostId) as "Duplication Count", Year(CreationDate) as "Year"
from PostHistory
Where PostHistoryTypeId = 10 and (Comment = 1 or Comment = 101)
group by Year(CreationDate)

--Closed and not re-opened
SELECT count(PostId) as "Closed count", year(CreationDate) as "Year"
from PostHistory
Where PostHistoryTypeId = 10 and not exists 
(SELECT * from PostHistory 
where PostHistoryTypeId = 11 and PostId = PostId)
group by year(CreationDate)

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results