Help needed on SQL Project Aggregated functions: How to Hack Hacker News question 8-11

Hi there,

I have a doubt in regards to the exercises 8-11 of the SQL Project How to Hack Hacker News https://www.codecademy.com/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-aggregates-projects-and-applications/projects/sql-hackernews

The exercise asks the question ‘What’s the best time to post a story?’

My answer would be, 7AM with an avergae score of 3.0:

SELECT strftime (’%H’, timestamp) AS ‘Hour’,
COUNT() AS ‘Total Stories’,
SUM(score) AS ‘Total Score’,
ROUND((SUM(score) / COUNT(
) ),4) AS ‘Avg score per hour’
FROM hacker_news
GROUP BY 1
ORDER BY 4 DESC;

But according to the answer the AVG score for each hour would be found like this:

SELECT strftime(’%H’, timestamp) AS ‘Hour’,
ROUND(AVG(score), 1) AS ‘Average Score’,
COUNT(*) AS ‘Number of Stories’
FROM hacker_news
WHERE timestamp IS NOT NULL
GROUP BY 1
ORDER BY 1;

and in this case the best hour is 18, with an average score of 27.0…

Does someone know why? I am not sure how the average score is calculated here… as according to my query the avg score for the hour 18 is lower…

1 Like

None is able to provide an explanation here?:pensive: