Most Frequently Unanswered Two-Tag Combinations

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

DECLARE @n int = 50

DECLARE @tagCombos table
(
	Id int IDENTITY(1, 1) UNIQUE,
	Tag1Id int NOT NULL,
	Tag2Id int NOT NULL,
	
	PRIMARY KEY CLUSTERED(Tag1Id, Tag2Id)
)

;WITH topTags AS
(
	/* Do not put this number higher than 15 when running against SO */
	SELECT TOP 100 t.Id FROM Tags t ORDER BY t.Count DESC
)
INSERT INTO @tagCombos (Tag1Id, Tag2Id)
	SELECT
		tt1.Id,
		tt2.Id
		FROM topTags tt1
		CROSS JOIN topTags tt2
		WHERE tt1.Id != tt2.Id

-- Remove duplicate combinations (i.e., (X, Y) is the same as (Y, X))
DELETE FROM tc1
	FROM @tagCombos tc1
	INNER JOIN @tagCombos tc2 ON
		(tc1.Tag1Id = tc2.Tag2Id) AND
		(tc1.Tag2Id = tc2.Tag1Id) AND
		(tc1.Id < tc2.Id)

-- Totals
DECLARE @allCombos table
(
	Tag1Id int NOT NULL,
	Tag2Id int NOT NULL,
	Count int NOT NULL
)

-- Unanswered
DECLARE @topCombos table
(
	Tag1Id int NOT NULL,
	Tag2Id int NOT NULL,
	Count int NOT NULL
)

INSERT INTO @topCombos (Tag1Id, Tag2Id, Count)
	SELECT TOP (@n)
		tc.Tag1Id,
		tc.Tag2Id,
		COUNT(1) AS Count
		FROM @tagCombos tc
		INNER JOIN PostTags pt1 ON pt1.TagId = tc.Tag1Id
		INNER JOIN PostTags pt2 ON (pt2.PostId = pt1.PostId) AND (pt2.TagId = tc.Tag2Id)
		WHERE
			NOT EXISTS
			(
				SELECT *
					FROM Posts q
					INNER JOIN Posts a ON a.ParentId = q.Id
					WHERE
						(q.Id = pt2.PostId) AND
						(q.ClosedDate IS NULL) AND
						((a.Score > 0) OR (a.Id = q.AcceptedAnswerId))
			)
		GROUP BY
			tc.Tag1Id,
			tc.Tag2Id
		ORDER BY Count DESC

INSERT INTO @allCombos (Tag1Id, Tag2Id, Count)
	SELECT
		tc.Tag1Id,
		tc.Tag2Id,
		COUNT(1) AS Count
		FROM @tagCombos tc
		INNER JOIN PostTags pt1 ON pt1.TagId = tc.Tag1Id
		INNER JOIN PostTags pt2 ON (pt2.PostId = pt1.PostId) AND (pt2.TagId = tc.Tag2Id)
		GROUP BY
			tc.Tag1Id,
			tc.Tag2Id


SELECT TOP (@n)
	Tag1Name,
	Tag2Name,
	TotalQuestions,
	Unanswered,
	UnansweredPct
	FROM
	(
		SELECT
			t1.TagName AS Tag1Name,
			t2.TagName AS Tag2Name,
			ac.Count AS TotalQuestions,
			tc.Count AS Unanswered,
			CAST(((tc.Count * 100.0) / ac.Count) AS decimal(9, 2)) AS UnansweredPct
			FROM @allCombos ac
			INNER JOIN @topCombos tc ON (tc.Tag1Id = ac.Tag1Id) AND (tc.Tag2Id = ac.Tag2Id)
			INNER JOIN Tags t1 ON t1.Id = ac.Tag1Id
			INNER JOIN Tags t2 ON t2.Id = ac.Tag2Id
	) a
	ORDER BY UnansweredPct DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached