Query Exercise: Finding Sister Locations to Help Each Other

For this week’s query exercise, let’s start with a brief query to get a quick preview of what we’re dealing with:

SELECT TOP 100 Location, COUNT(*) AS Population, AVG(Reputation) AS AvgReputation FROM dbo.Users GROUP BY Location ORDER BY COUNT(*) DESC;

That query has a few problems, but hold that thought for a moment. (You’re going to have to solve those problems, but I just wanted to show you the sample data at first to give you a rough idea of what we’re dealing with.)

Our business users are thinking about doing some targeted advertising, and they have two questions.