Modules

In SQL, subqueries are def important, as they let you do stuff you won't be able to do using a single query. Before diving into some use cases, let me give you an idea on why they are so important?

• They will help you break down complex queries into more manageable parts. Yes as you become a Data Analyst, you won't be writing queries of 10 lines but more of 100+ lines. Trust me. Some projects require that! And without subqueries, I would have probably lost my mind
• With that said, it helps enhance your readability. If you work in a job, you not only need to understand your query but other people need to be able to do as well.
• With subqueries, you can perform a lot of calculation from different tables that has nothing to do with each other and then join them at the end to get your final result

Let's do a real use case, it will make more sense:

You're being asked on your new job at Instagram to: find out the average number of comments on Instagram posts that have received more likes than the average for posts with a specific hashtag, such as #travel.

And you will be using the instagram_post table that look like this (a sample of it):

Step 1: We calculate the average number of likes for posts with the #travel hashtag

SELECT AVG(likes) AS avg_likes
  FROM instagram_posts
 WHERE hashtags LIKE '%#travel%'

Step 2: Next, we identify posts that are more popular than average

We just checked whether the number of likes on our instagram post is actually greater than the average. Then we made sure to only include posts that have the #travel in it.

SELECT *
  FROM instagram_posts
 WHERE likes > (SELECT AVG(likes) 
                  FROM instagram_posts
                 WHERE hashtags LIKE '%#travel%')
   AND hashtags LIKE '%#travel%'

Step 3: Get the average comments

Now, we simply need to average the number of comments for those posts that are doing better than average.

SELECT AVG(comments) AS avg_comments
  FROM instagram_posts
 WHERE likes > (SELECT AVG(likes) 
                  FROM instagram_posts
                 WHERE hashtags LIKE '%#travel%')
   AND hashtags LIKE '%#travel%'

This analysis can help your marketing team to find out which posts are actually driving more engagement. In this case, we are only looking at posts that have more likes than average. This will help shape strategy in your company and make actionable decisions!

So yes, trust me, subqueries are powerful!

Time to practice!