Query Exercise Answer: Here’s Why The Index Gets Smaller with Updates.
In last week’s Query Exercise, I asked you why updating a table reduced its index sizes. Our table had a date column called last_update_date, and when we set that date…
Finding Sister Locations to Help Each Other: Answers & Discussion
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…
Finding Long Values Faster: Answers & Discussion
Our challenge from last week was that the developers were complaining about a slow query in the Stack Overflow database that wasn’t as fast as they’d like. PgSQL select *…
Improving Cardinality Estimation: Answers & Discussion
Your challenge for this week was to improve estimation on a query. Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. The Stack Overflow…
Finding Tagged Questions Faster: Answers & Discussion
Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag: PgSQL select * from posts where tags like '%<postgresql>%' order by…
Find the Best Time for Maintenance: Answers & Discussion
Your Query Exercise was to find the best time to take the database offline for maintenance – the time when we’ve got the least load. We kept this one pretty…
Find Posts with the Wrong CommentCount: Answers & Discussion
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…
Find Foreign Key Problems: Answers & Discussion
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…