Query Exercise Answers

This week’s query exercise asked you to find two kinds of locations in the Stack Overflow database: Locations populated with users who seem to be really helpful, meaning, they write really good answers Locations where people seem to need the most help, meaning, they ask a lot of questions, but they do not seem to…

Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone’s Stack Overflow database might be slightly different, we had to start by finding the “end date” for our query. I’m working…

In last week’s Query Exercise, our developers had a query that wasn’t going as fast as they’d like: Transact-SQL CREATE INDEX DisplayName ON dbo.Users(DisplayName); GO SELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; 12345 CREATE INDEX DisplayName ON dbo.Users(DisplayName);GOSELECT *  FROM dbo.Users  WHERE LEN(DisplayName) > 35; The query had an index, but SQL Server was refusing to…

Your challenge for last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location: Transact-SQL CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation AS SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate FROM dbo.Users u WHERE u.Location = (SELECT TOP 1 Location FROM…

In the last Query Exercise discussion, we hit a performance wall when we were trying to quickly find questions with a specific tag. We threw up our hands and said it’s time to make database changes, but we had a few restrictions: We have a lot of existing code that uses the Posts.Tags column That…

Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag: Transact-SQL SELECT TOP 100 * FROM dbo.Posts WHERE Tags LIKE '%<sql-server>%' ORDER BY Score DESC; 1234 SELECT TOP 100 *FROM dbo.PostsWHERE Tags LIKE '%<sql-server>%'ORDER BY Score DESC; That’s currently using this index in its execution plan:…

Your Query Exercise was to find the best time to do database maintenance by querying the Users table, looking for a one-hour window with the lowest number of created users. We kept this one pretty simple by looking at the data in just one table, and we didn’t hassle with time zones. We just wanted…

Your Query Exercise was to find denormalization accuracy problems: checking the accuracy of a reporting column, Posts.CommentCount. There were two parts: finding the top 100 most problematic Posts with the biggest variances, and thinking about a long term solution to keep the CommentCount accuracy as high as practical. Question 1: Finding the Problematic Posts Your…

Your Query Exercise was to find foreign key problems: data that might stop us from successfully implementing foreign keys in the Stack Overflow database. Question 1: Write queries to find the problematic data. Montro1981’s thorough answer broke the challenge into two parts: first, check to see if there is ANY bad data, and if so,…