Forum

Posted by Andrei, March 6, 2022, 8:44 a.m.

#99 with and without limit 5 different top 5 results

There's some kind of bug in #99 because the top 5 from the solution query is ordered differently then you can see it by leaving out the 'LIMIT 5'.
Check the examples:
---------------------------------------
WITH friends_liked_pages AS
(
SELECT user_id, page_id
FROM liked_page
WHERE user_id IN (
SELECT friend_id FROM exercise.friends WHERE user_id = 1
)
),
page_liked_count AS (
SELECT page_id, COUNT(DISTINCT user_id) AS num_liked_friends
FROM friends_liked_pages
WHERE page_id NOT IN (
SELECT page_id FROM liked_page WHERE user_id = 1
)
GROUP BY page_id
)
SELECT
page_id
FROM page_liked_count
ORDER BY num_liked_friends DESC
LIMIT 5;
---------------------------------------
------> gives
2026
2020
2014
2016
2019
BUT, if you do
-------------------------------------------
WITH friends_liked_pages AS
(
SELECT user_id, page_id
FROM liked_page
WHERE user_id IN (
SELECT friend_id FROM exercise.friends WHERE user_id = 1
)
),
page_liked_count AS (
SELECT page_id, COUNT(DISTINCT user_id) AS num_liked_friends
FROM friends_liked_pages
WHERE page_id NOT IN (
SELECT page_id FROM liked_page WHERE user_id = 1
)
GROUP BY page_id
)
SELECT
page_id
FROM page_liked_count
ORDER BY num_liked_friends DESC
-----------------------------------------------------------------
(no LIMIT 5 HERE)
---------> then the answer is:
2026
2029 <-- this was 2020 previously
2014
2016
2019
...

Answers

Thanks Andrei,
I've gone ahead and updated the problem, there seemed to be some pages liked the the equal amount of friends, that why the database engine kinda randomly selected them.
-Leon
SQLPad user avatar

Leon (949)

March 7, 2022, 9:57 a.m.