0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

SELECT num.TagName AS Tag, ROW_NUMBER() OVER (ORDER BY rate.Rate DESC) AS CycleRank, ROW_NUMBER() OVER (ORDER BY num.Num DESC) AS TotalRank, rate.Rate AS QuestionsInCycle, num.Num AS QuestionsTotal
FROM (
	SELECT COUNT(PostId) AS Rate, TagName
	FROM Tags, PostTags, Posts
	WHERE Tags.Id = PostTags.TagId
		AND Posts.Id = PostId
		AND Posts.CreationDate < '2022-07-01'
		AND Posts.CreationDate >= '2022-07-01'
	GROUP BY TagName
) rate
	INNER JOIN (
		SELECT COUNT(PostId) AS Num, TagName
		FROM Tags, PostTags, Posts
		WHERE Tags.Id = PostTags.TagId
			AND Posts.Id = PostId
		GROUP BY TagName
		HAVING COUNT(PostId) > 10000
	) num
	ON rate.TagName = num.TagName
ORDER BY rate.rate DESC;

fork query

edited aug 18 22

created aug 18 22 anonymous