SQL hacker news project question

In the free SQL course, there is the last project called hacker news. Task 5 says:

Oh no! While we are looking at the power users, some users are rickrolling — tricking readers into clicking on a link to a funny video and claiming that it links to information about coding.

The url of the video is:

https://www.youtube.com/watch?v=dQw4w9WgXcQ

How many times has each offending user posted this link?

My solution is:

SELECT user, COUNT("https://www.youtube.com/watch?v=dQw4w9WgXcQ") FROM hacker_news GROUP BY user ORDER BY 2 DESC;

Why is my solution incorrect?

The aggregate, COUNT cannot count a URL string.

You’re supposed to count how many times the “offending user” has posted that link.
What is another way one could write this query? What clause would filter rows where you could search for something based on a condition LIKE that particular url?

1 Like

My solution is this:

SELECT user, COUNT(*)

FROM hacker_news

WHERE url = 'https://www.youtube.com/watch?v=dQw4w9WgXcQ'

GROUP BY user

ORDER by 2 DESC;

Is there a reason why COUNT cannot count strings? I found it on this post on stackoverflow:
[sql server - (sql) how can I use count() method when data type is text? - Stack Overflow](https://it says here that COUNT cannot be used to count the string data type)

You have to COUNT(*) the rows that meet a particular condition.
You have to use LIKE w/your WHERE clause.

2 Likes

Be careful when hunting SQL information, there are various implementations that unfortunately often have different behaviour. That link is to an SQL server question and mentions the TEXT data type which seems to have been deprecated anyway for VARCHAR (which apparently can be counted). You can count text columns in sqlite which is what the lessons normally use unless otherwise specified.