Number of questions and unanswered questions on StackOverflow

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Parameter;
DECLARE @TagName varchar(128) = '##TagName##'
-- Begins with @TagName;
DECLARE @TagNameBeginsWith varchar(128) = @TagName+'%'
-- Contains @TagName, prefixed with dash;
DECLARE @TagNameContainsDash varchar(128) = '%-'+@TagName+'%'
 
SELECT COUNT(P.Id) FROM Posts P
WHERE P.Id IN (
	SELECT DISTINCT(PT.PostId) FROM PostTags PT
	WHERE PT.TagId IN (
		SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameBeginsWith
	) OR PT.TagId IN (
		SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameContainsDash
	)
)
 
SELECT COUNT(P.Id) FROM Posts P
WHERE P.Id IN (
	SELECT DISTINCT(PT.PostId) FROM PostTags PT
	WHERE PT.TagId IN (
		SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameBeginsWith
	) OR PT.TagId IN (
		SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameContainsDash
	)
) AND P.AcceptedAnswerId IS NOT NULL
 
SELECT CAST((
	CAST((
		SELECT SUM(P.AnswerCount) FROM Posts P
		WHERE P.Id IN (
			SELECT DISTINCT(PT.PostId) FROM PostTags PT
			WHERE PT.TagId IN (
				SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameBeginsWith
			) OR PT.TagId IN (
				SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameContainsDash
			)
		)
	) AS FLOAT)
	/
	CAST((
		SELECT COUNT(P.Id) FROM Posts P
		WHERE P.Id IN (
			SELECT DISTINCT(PT.PostId) FROM PostTags PT
			WHERE PT.TagId IN (
				SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameBeginsWith
			) OR PT.TagId IN (
				SELECT T.Id FROM Tags T WHERE T.TagName LIKE @TagNameContainsDash
			)
		)
	) AS FLOAT)
) AS DECIMAL(4,2))

Enter Parameters

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