brentOzar_quizz_20240229.sql

SELECT TOP (10) u.[Location]
	, COUNT(DISTINCT p.OwnerUserId) AS NbUsers
	, COUNT(p.Id) AS NbPosts
	, SUM(Score) AS TotalScore
FROM dbo.Users u
JOIN dbo.Posts p ON p.OwnerUserId = u.Id AND p.PostTypeId = 2 -- Answers
WHERE 1=1
	AND u.[Location] IS NOT NULL AND u.[Location] <> ''
GROUP BY u.[Location]
HAVING COUNT(DISTINCT p.OwnerUserId)>100 
ORDER BY TotalScore DESC
;

SELECT TOP (10) u.[Location]
	, COUNT(DISTINCT p.OwnerUserId) AS NbUsers
	, COUNT(p.Id) AS NbPosts
FROM dbo.Users u
JOIN dbo.Posts p ON p.OwnerUserId = u.Id AND p.PostTypeId = 1 -- only Questions
WHERE 1=1
		/* Location exclusions */
	AND u.[Location] IS NOT NULL AND u.[Location] <> ''
		/* Exclude any user who posted at least one answer */
	AND NOT EXISTS
	(
		SELECT 1
		FROM dbo.Posts p2 
		WHERE p2.PostTypeId = 2 -- Answers
			AND p.OwnerUserId = p2.OwnerUserId 
			AND p.Id <> p2.Id 
	)
GROUP BY u.[Location]
HAVING COUNT(DISTINCT p.OwnerUserId)>100 
ORDER BY NbUsers DESC
;