Joins Coding Practice - 3

In this practice set, let’s apply Joins to fetch meaningful data from a sample database of social networking application.

Database: The database contains user, post, comment and reaction tables.

• A user can post many posts. A post is posted by only one user.
• A user can react to multiple posts. A post can have many reactions.
• A user can comment on multiple posts. A post can have many comments.

Note

You can assume that a user may or may not post on the social media platform, i.e., a user can have zero or many posts. Also, a post can have zero or many comments or reactions. So, do take care of this detail while writing various queries on the database, especially, when deciding on INNER JOIN / LEFT JOIN.

Refer the tables in the code playground for a better understanding of the database.

1.

Fetch all the posts along with user details.

Note:

Sort the posts in the descending order of

posted_at

Expected Output Format:

name	age	post_id	content	posted_at
...	...	...	...	...

2.

Fetch the 5 recent posts posted by “James Williams”.

Note:

• Condiser that the name of the user is “James Williams”
• Get the recent posts based on
  posted_at
  column in post table

Expected Output Format:

name	age	post_id	content	posted_at
...	...	...	...	...

3.

Fetch the total number of posts posted by “James Williams” till date as

posts_count

Note:

• Condiser that the name of the user is “James Williams”

Expected Output Format:

posts_count
...

4.

For every user, fetch the total number of posts posted so far as

posts_count

Note:

• If a user does not publish any posts, keep the posts_count as 0.
• Sort the output in the descending order of posts_count, and then in the ascending order of user_id.

Expected Output Format:

user_id	user_name	posts_count
...	...	...

5.

Get all the users details who posted at least 2 posts.

Note:

• Sort the output in the descending order of posts_count, and then in the ascending order of user_id.

Expected Output Format:

user_id	user_name	age	gender	posts_count
...	...	...	...	...

6.

Fetch the active users in January 2021.

Note:

• Consider the users as active, if they posted at least 2 posts in January 2021.
• Sort the output in the descending order of posts_count, and then in the ascending order of user_id.

Expected Output Format:

user_id	user_name	posts_count
...	...	...

7.

For each post, get the total number of positive reactions as

positive_reactions_count

Note:

• Consider the reaction_type "LIKE", "LIT", "WOW" and "LOVE" as positive reactions.
• If the post does not have any positive reactions, keep thepositive_reactions_count as 0.
• Sort the output in the descending order of positive_reactions_count , and then in the ascending order of
  post_id
  .

Expected Output Format:

post_id	content	posted_by	positive_reactions_count
...	...	...	...

8.

For each post, get the total number of positive reactions and negative reactions as

positive_reactions_count

and

negative_reactions_count

respectively.

Note:

• Consider the reaction_type "LIKE", "LIT", "WOW" and "LOVE" as positive reactions, and "DISLIKE" as a negative reaction.
• If the post does not have positive_reactions, keep the positive_reactions_count as 0.
• If the post does not have any negative_reactions, keep the negative_reactions_count as 0.
• Sort the output in the ascending order of post_id.

Expected Output Format:

post_id	content	positive_reactions_count	negative_reactions_count
...	...	...	...

9.

A general manager from the social networking site would like to review the content in all the posts that have more negative reactions over positive reactions.

Note:

• Consider the reaction_type "LIKE", "LIT", "WOW" and "LOVE" as positive reactions, and "DISLIKE" as a negative reaction.
• Output must contain rows in the ascending order of post_id.

Expected Output Format:

post_id	content	positive_reactions_count	negative_reactions_count
....	....	....	....

10.

Get all the comment details for the post with post_id = 5.

Note:

• Output must contain comments in the ascending order of commented_at.

Expected Output Format:

comment_id	comment_content	commented_by	commented_at
...	...	...	...

11.

Fetch all the posts posted by the user with user_id = 98, along with the comments for the posts.

Note:

• Output must contain posts in the ascending order of post_id, and then in the descending order of commented_at.
• If there is no comment for a post, keep the comment_id and comment_content as NULL.

Expected Output Format:

post_id	posted_by	content	posted_at	comment_id	comment_content	commented_at
...	...	...	...	...

12.

Fetch all the posts and their comments count, for the posts posted by the user with user_id = 98.

Note:

• Output must contain posts in the ascending order of post_id.
• If there is no post for a user, keep the content, posted_at values as NULL.
• If there is no comment for a post, keep the comments_count values as 0.

Expected Output Format:

post_id	posted_by	content	posted_at	comments_count
...	...	...	...	...

13.

For each user, get all the posts posted, and the total comments count for each post, along with user_details.

Note:

• Sort the output in the ascending order of user_id, and then in the ascending order of post_id.
• If there is no post for a user, keep the content, posted_at values as NULL
• If there is no comment for a post, keep the comments_count value as 0.

Expected Output Format:

user_id	name	post_id	content	posted_at	comments_count
...	...	...	...	...

14.

For each user, get all the posts posted, and the total reactions count for each post, along with user_details.

Note:

• Sort the output in the ascending order of user_id, and then in the ascending order of post_id.
• If there is no post for a user, keep the content, posted_at as NULL.
• If there is no reaction for a post, keep the reactions_count as 0.

Expected Output Format:

user_id	name	post_id	content	posted_at	reactions_count
...	...	...	...	...

 0000