Q&A about the site for expert genealogists and people interested in genealogy or family history
SELECT u.Id AS [User Link], CONCAT('https://stackoverflow.com/users/', u.Id) AS [Profile Link], u.DisplayName, u.Location, u.WebsiteUrl, u.AboutMe, u.Views, u.UpVotes, u.DownVotes, bounties.BountiesWon, answers.AnswersPosted FROM Users u JOIN ( SELECT DISTINCT UserId FROM Badges WHERE LOWER(Name) IN ('beta', 'archaeologist', 'autobiographer') AND Class IN (1, 2) AND TagBased = 0 ) user_badges ON user_badges.UserId = u.Id JOIN ( SELECT DISTINCT UserId FROM Badges WHERE LOWER(Name) IN ('python', 'asp.net', 'sql', '.net', 'c#-5.0', 'c#-4.0') AND Class IN (1, 2) AND TagBased = 1 ) tag_badges ON tag_badges.UserId = u.Id LEFT JOIN ( SELECT Posts.OwnerUserId, COUNT(*) As BountiesWon FROM Votes JOIN Posts ON Votes.PostId = Posts.Id JOIN VoteTypes ON VoteTypes.Id = Votes.VoteTypeId WHERE VoteTypes.Name = 'BountyClose' GROUP BY Posts.OwnerUserId ) bounties ON bounties.OwnerUserId = u.Id LEFT JOIN ( SELECT OwnerUserId, COUNT(*) AS AnswersPosted FROM Posts JOIN PostTypes ON PostTypes.Id = Posts.PostTypeId WHERE PostTypes.Name = 'Answer' GROUP BY Posts.OwnerUserId ) answers ON answers.OwnerUserId = u.Id WHERE LOWER(Location) LIKE '%new jersey%' OR LOWER(Location) LIKE '%new york%' OR LOWER(Location) LIKE '%new johnsonville%'