Hacker News Project Question 5

https://www.codecademy.com/courses/learn-sql/projects/sql-hackernews

Question 5 asks me to identify what users are rickrolling other users on the forum. I simply pulled user and url columns where the URL matched the URL in question as below

select user, url from hacker_news where url = 'https://www.youtube.com/watch?v=dQw4w9WgXcQ'

This gets me the answer I need - the two users that are rickrolling.

However, the “correct” solution is much more complicated (below).

SELECT user,
   COUNT(*)
FROM hacker_news
WHERE url LIKE '%watch?v=dQw4w9WgXcQ%'
GROUP BY 1
ORDER BY 2 DESC;

Is this correct because my formula may only take into account exact URL’s and/or that the result could contain much more data so my solution wouldn’t necessarily be the most concise way to view the data?

Thanks!

But the question asks ‘How many times has the user posted the offending link?’ Which is why you use COUNT(*)

Using url LIKE '%watch?v=dQw4w9WgXcQ%' matches exactly that pattern sequence.
It’s just abbreviated rather than using the entire address.

1 Like

I see! I was able to identify the number of times the user posted the link with my formula but the solution clearly identifies the number and would be more useful for a larger data set. Thanks!

It’s just shortening the link for ease of use - got it.

1 Like