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
...
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
...