Weighted AVG

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.

Hi @fly900,

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;

@ el_cocodrilo
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!