56. Total number of actors

easy

Instruction
  • Write a query to return the total number of actors from actor_tv, actor_movie with FULL OUTER JOIN.
  • Use COALESCE to return the first non-null value from a list.
  • Actors who appear in both tv and movie share the same value of actor_id in both actor_tv and actor_movie tables.

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

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

Expected results

Solution postgres

SELECT 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".



More OUTER JOINS questions

ID Title Level FTPR
213 Interchange revenue by issuer and merchant category visa easy -
194 Daily active users report for new market snap easy
20%
191 Number of replies by group twitter medium
18%
172 Comments distribution social hard
13%
168 Average number of streams in the US tiktok medium
13%
140 Daily sales of restaurant 100011 doordash hard
8%
118 Daily bookings in the US airbnb hard
5%
100 Advertiser ROI social medium
8%
55 Busy days and slow days medium
10%
54 Customer groups by movie rental spend medium
20%
53 Movie groups by rental income hard
11%
52 Movies cast by movie only actors easy
11%
51 Movie only actor easy
37%
45 Movie inventory optimization hard
9%
44 In-demand vs not-in-demand movies medium
6%
43 Customers who rented vs. those who did not medium
8%
42 Films that are in stock vs not in stock medium
12%
41 Productive actors vs less-productive actors hard
7%