So I am working with aggregate functions and thought that in the hacker_news project (https://www.codecademy.com/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-aggregates-projects-and-applications/projects/sql-hackernews) step 11 should be to find weighted averages for each time. How would I do this? I tried many different things and just could not get it. Any help would be awesome.
Welcome to the Codecademy Forums!
If you’re looking to weight the score by the proportion of total stories per hour, the math you will want for your weighted column will be
score * (number of stories that hour / total stories).
While it seems like this should be simple to compute, you can’t reference one of your aggregate columns to create a new column in the same
SELECT statement. You need to alias a separate query with the
WITH...AS syntax and then you can create a new column when you
SELECT from that aliased table.
I’m not sure if you’ve covered that in your curriculum yet or if that comes later (it’s definitely used in the Warby Parker project), so if you can’t figure it out I’ve included some sample code below:
Click Here For Code
WITH score_table AS( SELECT strftime('%H', timestamp) AS 'hour', Round(AVG(score)) AS 'score', COUNT(*) AS 'number_of_stories' FROM hacker_news WHERE timestamp IS NOT NULL GROUP BY 1 ) SELECT hour, number_of_stories, score, score * (number_of_stories / 4000.0) as 'weighted_score' FROM score_table;
yep have not used, “WITH” yet. I did keep trying to reference the aggregate function that I used in the same SELECT statement as well (So that makes sense why it didn’t work) and then when I tried doing all the math at once it just got too messy and I couldn’t keep track. Thank you for the help!