Practical example for Ranking with Window Functions

In this article, Mysql 5.7 Window Functions Workaround, I put some examples of how we can solve problems if we need window functions in MySQL 5.7(which lacks such functionality).

In this post, we can achieve the same result, but with less code, and it looks more readable.

What we want to do: given we have a list of tweets, we want to find SUM of the latest comments(only 3 latest items) for each user.

SELECT
  user_id,
  SUM(likes)
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY published_at DESC ) AS rank
      FROM twitter_comments
     ORDER BY user_id ASC, published_at DESC
  ) AS data
 WHERE rank <= 3
 GROUP BY user_id;

For each user's tweet, we generate a unique row number, which is later used to limit rows with aggregation functions(like SUM or AVG)

The intermediate result(with rank for each tweet per user) looks like this:

 id | user_id | likes |    published_at     | rank
---------+---------+-------+---------------------+------
  6 |       1 |   200 | 2021-04-05 14:01:00 |    1
  5 |       1 |   100 | 2021-04-05 13:01:00 |    2
  4 |       1 |   200 | 2021-04-05 12:01:00 |    3
  3 |       1 |   300 | 2021-04-05 11:01:00 |    4
  2 |       1 |   200 | 2021-04-05 10:01:00 |    5
  1 |       1 |   100 | 2021-04-05 09:01:00 |    6
 12 |       2 |   100 | 2021-05-05 14:01:00 |    1
 11 |       2 |   200 | 2021-05-05 13:01:00 |    2
 10 |       2 |   300 | 2021-05-05 12:01:00 |    3
  9 |       2 |   100 | 2021-05-05 11:01:00 |    4
  8 |       2 |   300 | 2021-05-05 10:01:00 |    5
  7 |       2 |   200 | 2021-05-05 09:01:00 |    6
(12 rows)

SQL Fiddle example

---

Helpful links:

• PostgreSQL ROW_NUMBER Function
• PostgreSQL 9.6.21 Documentation