Table 1: actor
col_name | col_type -------------+-------------------------- actor_id | integer first_name | text last_name | text
Question 39
Most productive actor with inner join
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 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
postgresSELECT
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.
Copied
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
5:00
Your results
Run your query to preview results here.