WITH statement SQL exercise hacker_news

HI, I was working on the hacker_news project.

LINK: https://www.codecademy.com/courses/learn-sql/projects/sql-hackernews

After finishing the project, I wanted to try a WITH statement to check which users score higher then average. I made up the query below, but it doesn’t work. What am I doing wrong?

WITH temporary_table AS (SELECT ROUND(AVG(score),2) AS ‘average’ FROM hacker_news)

SELECT name,score FROM hacker_news

WHERE hacker_news.score > temporary_table.average;

Hi there, and welcome to the forums!

The problem you are having is a slight misunderstanding of how WITH works. The WITH clause creates a temporary table in the database that disappears after the query has been completed. As such, when interacting with it you need to interact as if it’s just another table in the database. When you are executing your second part of the query, it’s looking for temporary_table and being unable to find it as it’s not actually included in that section of the query. Therefore to include it as something that part of the query can find, you need to include it in your FROM clause, like so:

WITH temporary_table AS (
   SELECT ROUND(AVG(score), 2) AS 'average'
   FROM hacker_news
SELECT title, score
FROM hacker_news, temporary_table
WHERE hacker_news.score > temporary_table.average;

Please note I have also corrected where you put “name” to “title”, just a slight typo! If you use this code, you should find it works sufficiently, and I hope it is clear why!


Thank you Adam! It is clear now.