Kata SQL

Using Window Functions To Get Top N per Group

  1. category name alphabetically
  2. number of post views largest to lowest
  3. post id lowest to largest
  • Some categories may have less than two or no posts at all.
  • Two or more posts within the category can be tied by (have the same) the number of views. Use post id as a tie breaker — a post with a lower id gets a higher rank.

Schema

categories

Column     | Type                        | Modifiers
------------+-----------------------------+----------
id | integer | not null
category | character varying(255) | not null

posts

Column     | Type                        | Modifiers
------------+-----------------------------+----------
id | integer | not null
category_id | integer | not null
title | character varying(255) | not null
views | integer | not null

Desired Output

category_id | category | title                             | views | post_id
------------+----------+-----------------------------------+-------+--------
5 | art | Most viewed post about Art | 9234 | 234
5 | art | Second most viewed post about Art | 9234 | 712
2 | business | NULL | NULL | NULL
7 | sport | Most viewed post about Sport | 10 | 126
...
  • category_id - category id
  • category - category name
  • title - post title
  • views - the number of post views
  • post_id - post id
WITH CTE AS (
SELECT c.id as category_id,
c.category,
p.id as post_id,
p.views as views,
p.title,
ROW_NUMBER() OVER (PARTITION BY p.category_id
ORDER BY p.views DESC, p.id) ROW_NUM
FROM categories c
LEFT JOIN posts p
ON c.id = p.category_id
GROUP BY c.id, c.category, p.title, p.id
)SELECT category_id, category, title, views, post_id
FROM CTE WHERE ROW_NUM <= 2
ORDER BY category, views DESC, post_id;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store