Analyze Hacker News Trends Task 4

https://www.codecademy.com/journeys/data-scientist-ml/paths/dsmlcj-22-data-science-foundations/tracks/dsmlcj-22-learn-sql/modules/dsinf-learn-sql-aggregate-functions-252b7e12-4f8f-4c70-9dd7-ddfe54dc0cee/projects/sql-hackernews

I was working on Analyze Hacker News Trends and when I got to Task 4, the AI generated hint said “Your code is on the right track, but it seems like you have hardcoded the values of the scores instead of using the actual values from the table. Instead of manually adding the scores, you can modify your query to calculate the sum of scores from the previous task using the SUM() function.”

I realized that the task DID want us to just do the math by hard coding the values, but I decided to ask Chat GPT for suggestions on how to do the calculation without manually adding the scores. I came up with this, which I realize is more advanced than this stage in the course, but it was a fun challenge figuring it out:

WITH users_over_200 AS (
SELECT user, SUM(score) as top_users_total
FROM hacker_news
GROUP BY user
HAVING top_users_total > 200
)
SELECT (SUM(p.top_users_total) * 100.0) /
(SELECT SUM(score) FROM hacker_news)
as top_users_percentage
FROM users_over_200 p;

This problem is extremely frustrating because the AI wants us to use concepts that we haven’t even learned yet in the course (nested queries) and yet the standard hint is asking us to hard code the scores.

I really think this project needs to be re-worked to either include a lesson on nested queries, or explicitly state whether or not it’s expecting hard-coded values. Coming from 0 SQL experience, I found this project not helpful at all based on the murky requirements.

1 Like

Yes the AI helper for this project didn’t really work. I prefer the usual “Stuck? Get a Hint” section.

Unless I did a different version of this project (which I don’t think that I did), I don’t see any of the questions requiring a CTE or, nested query. Don’t make it more difficult than it needs to be! :slight_smile:

tasks 2 & 3:

Summary
SELECT title, score
FROM hacker_news
ORDER BY score DESC
LIMIT 5;

SELECT SUM(score)
FROM hacker_news;

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

Yes that is what I said in my comment.

It was only in the AI help responses. I ended up just using the hard-coded values and that worked out fine but I can definitely see the merit in using nested queries since most of the real-world situations with SQL would require creating scripts that can be reused - which would involve nested queries and whatnot. Appreciate your help!

1 Like

I figured since AI was suggesting it, I could use AI to tell me how to do what it was suggesting. And it worked. I did not know how to do it at the time since I hadn’t gotten that far. I was curious as to whether or not ChatGPT could help me with writing code since I had heard about people using it for that purpose.