Calculating Top N items per Group (Without Window Functions)

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.value <= t2.value
GROUP BY t1.group_id,t1.value
HAVING COUNT(*)<=3
ORDER BY t1.group_id,t1.value DESC;

Leetcode Problem Link :

https://leetcode.com/problems/the-most-recent-three-orders/

Solution on my GitHub page :

https://github.com/cM2908/leetcode-sql/blob/main/1532.%20The%20Most%20Recent%20Three%20Orders.txt