Hi, Please could someone offer some advice for the following query.
- I’m not sure what the foreign key should be, id or score?
Now you need to find out which subreddits have the most popular posts. We’ll say that a post is popular if it has a
score of at least 5000. We’ll do this using a
WITH and a
First, you’ll need to create the temporary table that we’ll nest in the
WITH clause by writing a query to select all the posts that have a score of at least 5000.
Next, place the previous query within a
WITH clause, and alias this table as
Finally, utilize an
INNER JOIN to join this table with the
subreddits table, with
subreddits as the left table. Select the subreddit
name , the
score of each post, and order the results by each popular post’s score in descending order.
WITH popular_posts AS (
WHERE score >= 5000
SELECT subreddits.name, popular_posts.title, popular_posts.score
INNER JOIN popular_posts
ON subreddits.id = popular_posts.id
ORDER BY popular_posts.score DESC;