39. Most productive actor with inner join

easy

Instruction
  • Write a query to return the name of the actor who appears in the most films.
  • You have to use INNER JOIN in your query.

Table 1: actor


  col_name   | col_type
-------------+--------------------------
 actor_id    | integer
 first_name  | text
 last_name   | text

Table 2: film_actor

Films and their casts


  col_name   | col_type
-------------+--------------------------
 actor_id    | smallint
 film_id     | smallint

Sample results


 actor_id | first_name | last_name
----------+------------+-----------
     1234 | FIRST_NAME | LAST_NAME

Solution postgres

SELECT
    FA.actor_id,
    MAX(A.first_name) first_name,
    MAX(A.last_name) last_name
FROM film_actor FA
INNER JOIN actor A
ON A.actor_id = FA.actor_id
GROUP BY FA.actor_id
ORDER BY COUNT(*) DESC
LIMIT 1;
    

Explanation

This query retrieves data from two tables: "film_actor" and "actor". It selects the actor_id, first_name, and last_name columns from these tables.

The query then joins the two tables using the actor_id column as the common link between them.

The GROUP BY clause groups the results by actor_id.

The ORDER BY clause orders the results in descending order based on the count of records for each actor.

The LIMIT clause limits the result set to the first row.

Overall, the query is selecting the actor with the most film credits and returning their actor_id, first_name, and last_name.

Last Submission postgres

Expected results



More INNER JOIN questions

ID Title Level FTPR
213 Interchange revenue by issuer and merchant category visa easy
70%
179 Members who ever moved from Microsoft to Google linkedin medium
31%
162 Number of orders per brand walmart easy
41%
150 Countries with above average customers google easy
15%
99 Page recommendation facebook medium
13%
91 same day friend request acceptance rate spotify easy
13%
50 Top 5 cities for movie rentals easy
22%
49 Top 3 money making movie categories medium
18%
48 Movie and TV actors easy
38%
40 Top 2 most rented movie in June 2020 medium
14%
38 Most popular movie category (name and id) medium
19%
37 Most popular movie category easy
15%
36 Actors from film 'AFRICAN EGG' easy
32%