Posts

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 ; Method 2 (Without Window Function): SELECT t1.group_id,t1.value FROM groups t1 INNER JOIN groups t2 ON t1.group_id = t2.group_id AND t1
1 comment
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.
Post a Comment
Read more

Single vs Double Quotation marks (In PostgreSQL)

While writing sql queries there can be a lot of confusion when it comes to using single vs double quotation marks, especially in a database like PostgreSQL where both of them can not be used interchangeably (unlike MySQL) Even though it is very basic topic, it is very important and can having clarity on it can save a lot of time while writing and debugging the sql queries in PostgreSQL. Here's the link that i found that can reduce the dilemma upto an extent. https://streamofcoding.com/single-quote-vs-double-quotes-in-postgres/ Please feel free to add your thoughts or resources related to the topic in the comments.
Post a Comment
Read more