"Multiple Tables with Subreddit" #9

Could someone please help me with this step? Here’s what I have so far:

SELECT posts.title,

subreddits.name AS ‘subreddit_name’,

MAX(posts.score) AS ‘max_score’

FROM posts

INNER JOIN subreddits

ON posts.id = subreddits.post_id

GROUP BY subreddits.id;

This doesn’t return any results. Thanks in advance.

Hello! Could you post a link to the exercise, please?

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-multiple-tables-with-reddit

I don’t have PRO; could you post a screenshot of the instructions and the table schema?

Here’s the instructions for step 9:

Next, you need to find out the highest scoring post for each subreddit.

Do this by using an INNER JOIN , with posts as the left table.

Can you provide a few of the previous instructions (that relate to step 9)? Sorry, but I can’t remember what is happening in this project (it’s been a while since I last did it).

  1. Let’s start by examining the three tables.

Write queries to select the rows from each table. Because some of the tables have many rows, select just the first 10 rows from each.

What are the column names of each table?

  1. What is the primary key for each table? Can you identify any foreign keys?

  2. Write a query to count how many different subreddits there are.

  3. Write a few more queries to figure out the following information:

  • What user has the highest score ?
  • What post has the highest score ?
  • What are the top 5 subreddits with the highest `subscriber_count.
  1. Now let’s join the data from the different tables to find out some more information.
    Use a LEFT JOIN with the users and posts tables to find out how many posts each user has made. Have the users table as the left table and order the data by the number of posts in descending order.

  2. Over time, posts may be removed and users might delete their accounts.

We only want to see existing posts where the users are still active, so use an INNER JOIN to write a query to get these posts. Have the posts table as the left table.

  1. Some new posts have been added to Reddit!

Stack the new posts2 table under the existing posts table to see them.

  1. 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 JOIN.

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 popular_posts.

Finally, utilize an INNER JOIN to join this table with the subreddits table, with subreddits as the left table. Select the subreddit name, the title and score of each post, and order the results by each popular post’s score in descending order.

  1. Next, you need to find out the highest scoring post for each subreddit.

Do this by using an INNER JOIN, with posts as the left table.

  1. Finally, you need to write a query to calculate the average score of all the posts for each subreddit.

Consider utilizing a JOIN, AVG, and GROUP BY to accomplish this, with the subreddits table as the left table.

Could you post a screen shot of the database schema, please? Sorry for all of these questions; the whole project has just escaped me!

Have a look at the schema for the subreddits table. Is there a column called post_id?

I just noticed that mistake. Thanks.

1 Like

All good. Sorry it took so long for me to do anything!