Analyse Hacker News Trends Task 3

Hi all, I am working on the Analyse Hacker News Trends and I got to task 3 where we need to work out the users who have accumulated a lot of points across their stories, and find out who has gotten combined scores of more than 200 and what their combined scores are. Before looking at the hint I tried to do this myself and wrote out the following code:

SELECT user, score
FROM hacker_news
GROUP BY user
HAVING score > 200
ORDER BY score DESC;

The code in the hint was the following:

SELECT user, SUM(score)
FROM hacker_news
GROUP BY user
HAVING SUM(score) > 200
ORDER BY 2 DESC;

Both gave the same result but the main difference in the the hint code is the use of the aggregate function SUM (score).

My question is I don’t really understand what role the SUM function is playing when querying the data, as my understanding of the question was that we wanted to know which users had the highest scores, and my understanding of the SUM function was that it adds all values in a specified column and returns the result, but it does not seem to do that here as without the SUM function, the original query I wrote that did not have SUM gave the same information.

Also, I used the HAVING operator in my original code, but because I did not use an aggregate function, would it have been better to use the WHERE operator instead?

Thanks in advance!

You want to use SUM to add up each user’s scores. You’re grouping by user, then using HAVING to filter. Remember that HAVING filters groups, WHERE filters rows.

Ah okay! I was confused because I didn’t consider that users may have multiple scores across different stories, and so the SUM function is adding those together. Thanks!

1 Like