Mastering Subqueries for Data Analysis

Introduction and Overview

Welcome back! In this lesson, we will focus on mastering subqueries for data analysis in PostgreSQL. Subqueries, also known as inner queries or nested queries, allow you to perform more complex queries by embedding one query inside another. This technique is particularly useful for comparing aggregated data, filtering results, and more. Let’s dive in!

Dataset Review

For convenience, the tables and columns in our Marvel movies dataset are: Movies Table text movie_id | movie_name | release_date | phase ----------+------------+--------------+------- Movie Details Table text movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes ----------+--------------------+------------------------+-------------+----------------- Characters Table text character_id | movie_id | character_name | actor | screen_time_minutes --------------+----------+----------------+-------+---------------------

Understanding Joining Tables

Before we explore subqueries, let's briefly review joining tables in SQL. Joining tables is a fundamental operation used to combine data from two or more tables based on a related column. Consider the following example: SQL SELECT movies.movie_name, movie_details.imdb_rating FROM movies INNER JOIN movie_details ON movies.movie_id = movie_details.movie_id; In this example: We select movie_name from the movies table and imdb_rating from the movie_details table We join the movies table with the movie_details table using the movie_id column, which is common to both tables. The output is: text movie_name | imdb_rating ---------------------------------------------+------------- Iron Man | 7.9 The Incredible Hulk | 6.7 Iron Man 2 | 7.0 Thor | 7.0 Captain America: The First Avenger | 6.9 The Avengers | 8.0 Iron Man 3 | 7.2 Thor: The Dark World | 6.9 Captain America: The Winter Soldier | 7.7 Guardians of the Galaxy | 8.0 Avengers: Age of Ultron | 7.3 Ant-Man | 7.3 Captain America: Civil War | 7.8 Doctor Strange | 7.5 Guardians of the Galaxy Vol. 2 | 7.6 Spider-Man: Homecoming | 7.4 Thor: Ragnarok | 7.9 Black Panther | 7.3 Avengers: Infinity War | 8.4 Ant-Man and The Wasp | 7.1 Captain Marvel | 6.9 Avengers: Endgame | 8.4 Spider-Man: Far From Home | 7.5 Black Widow | 6.8 Shang-Chi and the Legend of the Ten Rings | 7.6 Eternals | 6.8 Spider-Man: No Way Home | 8.4 Doctor Strange in the Multiverse of Madness | 7.8 Thor: Love and Thunder | 7.5 Black Panther: Wakanda Forever | 7.3 Ant-Man and The Wasp: Quantumania | 6.2 Guardians of the Galaxy Vol. 3 | 8.1 The Marvels | 6.1 (33 rows) The result table lists movie names and their respective IMDb rating.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Subquery Example

With this foundation of subquery syntax, let's take a look at an example. One common use of subqueries is to compare values against an average or other aggregate results. Let's form a query to find the names of movies that have an IMDb rating greater than the average IMDb rating of all the movies.

First we need a query to find the average IMDb rating.

SQL

SELECT 
    AVG(imdb_rating) 
FROM 
    movie_details;

The output of this query is:

text

        avg         
--------------------
 7.4030303030303030
(1 row)

The average IMDb rating of the movies is approximately 7.4.

We will use the resulting average to filter movies in the outer query.

SQL

SELECT 
    movie_name, 
    imdb_rating 
FROM 
    movies 
INNER JOIN 
    movie_details ON movies.movie_id = movie_details.movie_id 
WHERE 
    imdb_rating > (
        SELECT 
            AVG(imdb_rating) 
        FROM 
            movie_details
    );

In this final query:

We join the movies and movie_details tables using the movie_id column.
We use a subquery to calculate the average IMDb rating
We filter movies that have an IMDb rating greater than the average.

The output is

text

                 movie_name                  | imdb_rating 
---------------------------------------------+-------------
 Iron Man                                    |         7.9
 The Avengers                                |         8.0
 Captain America: The Winter Soldier         |         7.7
 Guardians of the Galaxy                     |         8.0
 Captain America: Civil War                  |         7.8
 Doctor Strange                              |         7.5
 Guardians of the Galaxy Vol. 2              |         7.6
 Thor: Ragnarok                              |         7.9
 Avengers: Infinity War                      |         8.4
 Avengers: Endgame                           |         8.4
 Spider-Man: Far From Home                   |         7.5
 Shang-Chi and the Legend of the Ten Rings   |         7.6
 Spider-Man: No Way Home                     |         8.4
 Doctor Strange in the Multiverse of Madness |         7.8
 Thor: Love and Thunder                      |         7.5
 Guardians of the Galaxy Vol. 3              |         8.1
(16 rows)

This output shows the movie name and IMDb rating for every movie greater than the average IMDb rating (7.4).

This powerful combination of JOIN and subqueries allows us to perform complex data analysis with ease.

Introduction and Overview

Dataset Review

Understanding Joining Tables

Join the 1M+ learners on CodeSignal

Introduction to Subqueries and Syntax

Subquery Example

Summary and Next Steps