T-SQL

For this week’s Query Exercise, I asked you to write a better query than ChatGPT wrote. Your goal was to find the best days and times to post questions on Stack Overflow. I found it interesting that a lot of the initial answers focused on the times when there were the most questions, or which questions…

In last week’s post, I gave you a trigger that populated a history table with all changes to the Users.AboutMe column. It was your job to write a T-SQL query that turned these Users_Changes audit table rows: Into the full before & after data for each change, like the earlier query from the blog post:…

Your challenge for this week was to find out who keeps mangling the contents of the AboutMe column in the Stack Overflow database. Conceptually, there are a lot of ways we can track when data changes: Change Tracking, Change Data Capture, temporal tables, auditing, and I’m sure I’m missing more. But for me, there are…

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…