For example, there are N questions which have a Tag named X (there might be other tags also) and M of the questions remain unanswered. I require the number M associated with all tags in Stack Overflow.
Well, specifically, how do I get the amount of questions with close votes per week? I got this in a with atm:
select DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate)) as YearMon,
'Questions with close votes' as Series,
count(*) as Action
from PostHistory u
where u.CreationDate > @epoch AND u.PostHistoryTypeId = 10
group by u.PostId, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CONVERT(DATE,u.CreationDate)), CONVERT(DATE,u.CreationDate))
which returns 3 or 6, so I'm probably doing something wrong xd
@Olivia at best you can look at pendingflags but actual close votes aren't in the public SEDE until a post is closed. That is what you get from posthistorytypeid = 10
I'm actually thinking of a different approach, but I probably did something stupid
The amount of review tasks in the CV queue is a pretty close measure. SEDE doesn't fuzzy the numbers either, so I can format it into the same style as the rest of my massive query and sort it by post ID. I think anyway
But on the other hand, I might also have done this completely backward if ReviewTasks is one per post, in which case I actually have that already