Forum

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
;

 

 

Answers

Hi Olivia,

Very good question, the issue came from your assumption. You are using first_name + last_name as a unique identifier for an actor, which is wrong.

Actor's unique identifier is their actor_id, not first_name + last_name.

It will be clear if you run the following query:

SELECT * FROM actor
WHERE first_name = 'SUSAN'
AND last_name = 'DAVIS';

 

Hope it helps,

Leon

Hi Leon,

Thank you so much for your quick response!
I see. This is very clear to me now.

With much thanks,

Olivia

Olivia, July 13, 2022, 10:30 p.m.
SQLPad user avatar

Leon (949)

July 13, 2022, 9:30 p.m.