Table 1: actor_movie
Actors who appeared in a movie.
col_name | col_type ------------+------------------- actor_id | integer first_name | character varying last_name | character varying
Question 56
Total number of actors
Instruction
- Write a query to return the total number of actors from actor_tv, actor_movie with
FULL OUTER JOIN. - Use
COALESCEto return the first non-null value from a list. - Actors who appear in both tv and movie share the same value of
actor_idin both actor_tv and actor_movie tables.
Table 2: actor_tv
Actors who appeared in a TV show.
col_name | col_type ------------+------------------- actor_id | integer first_name | character varying last_name | character varying
Sample results
count ------- 123
Solution
postgresSELECT COUNT(DISTINCT actor_id) FROM (
SELECT
COALESCE(T.actor_id, M.actor_id) AS actor_id
FROM actor_tv T
FULL OUTER JOIN
actor_movie M
ON M.actor_id = T.actor_id
) X;Explanation
This query is counting the number of distinct actor IDs that appear in either the "actor_tv" table or the "actor_movie" table. It does this by first joining the two tables on the "actor_id" column, and using the COALESCE function to select the "actor_id" value from whichever table it appears in (if it appears in both, it will just select one of them). The resulting set of actor IDs is then counted using the COUNT and DISTINCT functions. The query is wrapped in a subquery with the alias "X".
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.