Inside Out - Leetcode SQL

210+

Nope, it is not a magic number, these many database/SQL questions are there on the leetcode to let you hunt them. Wait, wait, relax, take a deep breath and read this article before jumping into the graveyard.

Took me over 3 months to conquer it.

Comments

Post a Comment

Popular posts from this blog

Calculating Top N items per Group (Without Window Functions)

Image
These kinds of questions are more common than you think: Out of a list of Twitter users, find their N recent tweets. Out of all the cities, find the top N cities in each country by population. Out of all orders on the e-commerce website, find the top N recent orders for each customer. Solving these questions becomes pretty easy if we use a window function like row_number(), but solving this without any window function makes the task challenging. Here we will see both approaches. Question: Display the top 3 recent values from every group Table : groups                                            Required Output: Method 1 (Using Window Function): WITH ranked AS ( SELECT group_id,value, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY value DESC ) AS rn FROM groups ) SELECT group_id,value FROM ranked WHERE rn <= 3 ; ...
Read more

Leverage the mind smartly, How?

Add new learnings to your mind effectively. Mistakes : While studying/learning any new topic, we all tend to make the same mistake time and again. Whenever we come across an unknown topic, we go through the internet, read any article, or watch any random youtube videos. Yes, this way, we understand the topic and just stop there and after some time, when we need the same thing, we repeat the same process of surfing the internet. As you are reading the same topic from different sources every time you need it, it takes a long time for your mind to grasp the thing. So, What's the solution? What we can do is, after getting sufficient knowledge on the topic, make a note on it which you can revise later point in time and don't need to go through the web. And if the topic is too large and complex to prepare notes on, bookmark the webpage/video you learn it on and visit the same exact thing next time. what happens is when you read something from the same thing again and again, it builds...
Read more

Weird Syntax - Combine Recursive CTE with Normal CTE (PostgreSQL)

While writing recursive SQL queries    in PostgreSQL   , there is a very weird syntax of Recursive Common Table Expression(CTE) . Single CTE clause : WITH cte AS ( -- query ) -- main-query; Multiple CTE clause : WITH cte1 AS ( -- query ), cte2 AS ( -- query ) -- main-query; Single Recursive CTE clause : WITH RECURSIVE recur_cte AS ( -- base-query UNION [ ALL ] -- recur-query ) -- main-query; Things look pretty normal until here, but now comes the syntax twist. Combined Recursive CTE clause : WITH RECURSIVE cte AS ( -- query ), recur_cte AS ( -- base-query UNION [ ALL ] -- recur-query ) -- main-query; Conclusion :  Even though we have our second CTE as recursive nature, PostgresSQL wants us to put RECURSIVE keyword right after the WITH keyword in the first CTE itself and it will identify the cte_recur itself, where you have written the base-query & recur-query.
Read more