What I have learned by solving (almost) all the SQL problems in Leetcode
This is my first post in medium and I will share some of the things I have learned throughout my journey of solving the SQL problems in Leetcode.
I have added some resources in the later part of this post, they might be beneficial for you.
SQL problems are more real world oriented
You often hear the argument that there are lots of data structures and algorithms(DSA) you use to solve coding problems which have no use in your real life job. SQL problems are not like that. They are more real world oriented and you will find similar problems in your day to day work quite often., that’s why it’s very useful to practice these problems.
Although, you might never need to invert a binary tree in your daily job, solving DSA problems can improve your rational thinking and problem solving ability
Practising SQL problems will not only help you to ace interviews but also will help you in your daily tasks, no matter you are a Data Engineer/Analyst/Scientist writing queries for OLAP systems or a Backend Engineer writing queries for OLTP systems.
SQL has less surprises
When you are solving DSA problems, you can see drastic difference in terms of code, time and space complexity while solving same problem with different data structures and/or algorithms but this is not always the case for SQL. Definitely, you can use optimization techniques to boost your query performance but there are not as much surprises in SQL as you can have for DSA problems.
This is mainly because SQL is a language and it has limited features. There are numerous DSAs and you can implement them in different ways in different programming languages. In case of SQL, as it itself is a language, you have specific set of options and eventually less surprises.
Master your JOINs
We all know JOINs and how they work but the more problems you will solve, the more you will understand that there are lots of things you still don’t know about JOINs. JOINs can lead you to a very bad performing query if you mess it up, it can also make the query significantly performant if you know what you are doing.
The more you will learn, the more you will understand you know nothing
Try to visualize JOINs in your head while practising. Try different type of joins with same tables and see the difference in output. Mastering JOINs will not only let you solve difficult problems easily, it will also boost your ability to solve data related problems efficiently and effectively.
CTEs are your friend
I have seen a common misconception among many engineers that Common Table Expression(CTE) is a “high level” SQL thing and you don’t use it for easy problems and hence, people tend to write sub-queries instead of writing CTEs.
CTE is a wonderful way of structuring your long and complex query into pieces which not only looks beautiful but also makes your query modular, easier to read, understand and debug.
If your query has multiple sub-queries with hundreds of line of codes and it does not work as expected, you will realize how painful debugging it can be. Try to re-write the query with CTEs, that will help you debug the query easily.
Using CTEs during interviews is also helpful as you will have limited time to find and fix issues, if any.
WINDOWing makes things easier
WINDOW functions are one of the few magics SQL has.
At the beginning, it might be a bit difficult to understand its’ use cases and implementation but the more you will practise and implement WINDOW functions, the more you will be able to solve complex problems efficiently.
Window functions such as RANK, DENSE_RANK, FIRST_VALUE etc. are commonly used in many cases to solve complex problems, get used to with them. Don’t get frustrated if it takes some time to grasp it at the beginning, hang on there! You will see the improvement over time.
There are PATTERNs
When you start solving problems, no matter it’s a SQL or DSA problem, in the beginning every problem might seem different and you will be overwhelmed by the amount of different ways to solve different problems.
However, after practising for couple of days and solving dozens of problems, you will be able to figure out that there are actually not “a lot of ways” to solve different problems, rather you have “couple of common patterns” which are being using repeatedly to solve different problems.
“Angels converge where patterns are repeated.”
― Michael Bassey Johnson, The Oneironaut’s Diary
Once you will find those patterns, your problem solving journey will be much more easier and fun. I will try to write another post about the common patterns I have noticed while solving problems.
Practising and solving problems can be tiring at the beginning. This is normal and you are not the only one who feels that. I will give some tips to make your life easier.
Take it easy
SQL is a fairly easy language to start learning. If you are beginning, start with basics such as WHERE, aggregate functions, GROUP BY, ORDER BY, JOINS. Take time to master them. Once you feel that you understand and can solve problems with them pretty fast, start with more complex problems to practice advanced topics such as WINDOW functions.
Everyone was a beginner once.
Everyone started with basics and eventually used & understood complex functionalities. It takes time, take it easy.
Don’t waste time
Set a time to solve each problem. Let’s say, you shall take 5–8 minutes for easy problems, 8–15 minutes for medium problems and 15–25 minutes for hard problems.
If you can not solve the problem within the specific timeframe, look at the solution. Looking into solution is not a bad thing, directly copy-pasting it without understanding the solution is bad. That doesn’t help.
“Experience is a master teacher, even when it’s not our own.”
― Gina Greenlee, Postcards and Pearls: Life Lessons from Solo Moments on the Road
Take your time to read the solution someone had written, try to understand what was their thought process and how they approached towards the solution. Then try to think if you can come up with any better way of solving it. If yes, code your idea and see if that works, otherwise write the code by yourself instead of simply copy-pasting it.
Sometimes looking into others’ solution can help you thinking better. Don’t waste too much time solving one problem if you are stuck. Give yourself a little push.
Think before you code, don’t code before you think.
Once you read the problem statement, don’t jump right into coding. Think what are the steps you need to reach the final outcome.
I will give an example with the problem 1164. Product Price at a Given Date.
This problem gives you a products table with product_id, new_price & change_date and asks you to find the prices of all products on 2019–08–16 assuming that the price of all products before any change is 10.
Now, if I think about what I need to do to find the solution, I come up with the following steps:
- Find the product_id’s which has first change_date after 2019–08–16. These id’s have no change_date before 2019–08–16 and will have the default value 10 for price.
- Find the maximum change_date for product_id’s which has change_date before 2019–08–16. It will give me the product_id’s which has change date before and after 2019–08–16 with corresponding change_date and new_price.
- Find the product_id’s which has the change_date from step 2 and get the new_price. This new_price should be the price we are looking for that particular product_id.
- Combine the product_id’s from step 1 and step 3 with default price and new_price respectively.
Once I figure out these steps, I have a clear overview of how I shall proceed. If I am in an interview, I will discuss the above steps with the interviewer to clarify and will start coding once we both feel it’s ok to code. I will write three CTEs for step 1, 2 and 3 and combine the result of step 1 and step 3 to get the final result.
I have seen people during interviews started writing code right after reading the problem statement without saying a single word. I do understand there is a time constraint and the pressure is on the interviewee but unfortunately, thinking before going to implement a solution and thinking out loud is also a crucial part of coding interview. This helps the interviewer understand how you think, gives room for discussions/hints, makes it easier for the candidate to write the solution and the interviewer to evaluate.
I have given YES to many candidates who were able to explain how they will solve the problem clearly but was not able to complete the code due to time constraint.
Although it might seem like you will not be able to think all these during a time bound scenario like interview, trust me, you will if you practise enough. If you can practice this way, you will get used to with it! This will help you think clearly before jumping into writing code, consider edge cases, save time while writing the code and also help you pass interviews fairly comfortably.
Your father also has a father
Even if you are able to solve a problem, it’s a good idea to go through the discussion section in leetcode. You might find out that your solution was good but there is someone who came up with a better solution.
There should always be someone who knows more than you
We all experienced it several times throughout the journey, don’t get inferiority complex for that. You can not know everything and nobody knows everything. Accept the fact and take this opportunity to learn things. There were so many occasions when I was proud of my solution and after reading couple of other solutions, I found mine was near to the most optimized one. I have learned lot of things just by going through the discussions and comments.
These discussion and comment sections are gold mines, take full advantage of it!
Perfection takes time
Don’t bother about writing perfect solution at the beginning. Your first task is to solve the problem. Once you do that, you can always optimize it for better performance later.
Make it work, make it efficient, make it beautiful.
Perfection takes time to achieve. Don’t feel down if you can not come up with the best solution at the beginning. With time, you will eventually be there.
Don’t be frustrated
Once you start, you might feel like you will never be able to make it. Again, you are not the only one, we all were there! Hang tight, just don’t loose hope and discontinue.
Don’t stop trying. It’s the easiest thing to do and doesn’t bring anything.
To be good at something, you need to put time into that. Give that time, don’t be frustrated. This is not a rocket science, lots of people are doing it and not all of them are born genius. You might need more time to grasp something compared to others but eventually you will be there if you keep trying.
Trust me, you will reach there! It’s just a matter of time and you have to stick with it.
Practice, Practice and Practice
Yes, you have heard this 100 times before, hear this for the 101th time. If you want to be good at something, keep doing that. Even the people who are born genius, they keep practising what they are good at.
It takes 10000 hours to master something
Unfortunately, there is no shortcut of success. Practicing will not only help you become better over the time, it will make you confident for upcoming challenges. Keep doing what you are doing.
Resources and final thoughts
I have curated all my leetcode solutions in a github repository, you can follow and star that repository in github.
Happy learning!!
