Week #3: 100 Days of SQL Optimisation
Fine-Tuning SQL: Writing Efficient Queries for Better Performance
Think of SQL optimisation as tuning a musical instrument. A poorly tuned guitar can still play a song, but it won’t sound right. By refining joins and filters, and adding indexes, you create an efficient and harmonious melody, hitting all the right notes without missing a beat.🎵🎶
This week, we mixed it up by tackling a blend of popular interview questions, and real-world datasets. Each example highlights different techniques for achieving better performance in diverse contexts.
Here’s a list of what you’ll find in this week’s optimisation journey:
Day 15: Solved the Second Highest Salary SQL problem from LeetCode1 using randomly generated 10k entries.
Day 16: Tackled the Rank Scores SQL question from LeetCode2 using a 10k-entry generated dataset.
Day 17: Solved the Average Review Ratings Amazon SQL interview question from DataLemur3, with 100k randomly generated entries.
Day 18: Optimising a query using the IMDb's Non-Commercial Datasets4.
Day 19: Solved the Data Science Skills LinkedIn SQL interview question from DataLemur5, with 100k randomly generated entries.
Day 20: Solved the Duplicate Job Listings LinkedIn SQL interview question from DataLemur6, using 100k randomly generated entries.
Day 21: Solved the Number of Unique Subjects Taught by Each Teacher SQL question from LeetCode7, with 100k randomly generated entries.
In this post, we dive into week three of our 100-day SQL optimisation journey.
Day 15: Self Joins vs. CTEs
Goal: Retrieve the second highest distinct salary from the Employee table.