Posted by Olivia, July 13, 2022, 6:14 p.m.
Q19. Most productive actor
I submitted the answer below and it produces a different output from the correct solution.
Could someone please enlighten me about what exactly is wrong with my answer?
WITH film_actors AS (
SELECT
first_name,
last_name,
film_id
FROM
actor
INNER JOIN
film_actor
ON
actor.actor_id = film_actor.actor_id
),
actors_film_count AS (
SELECT
first_name,
last_name,
COUNT(DISTINCT film_id) AS film_cnt
FROM
film_actors
GROUP BY
first_name,
last_name
)
SELECT
first_name,
last_name
FROM
actors_film_count
ORDER BY
film_cnt DESC
LIMIT 1
;