Rank actors with movies released in India based on their average ratings. Which actor is at the top of the list?

Question

-- Note: The actor should have acted in at least five Indian movies. -- (Hint: You should use the weighted average based on votes. If the ratings clash, then the total number of votes should act as the tie breaker

SELECT n.name as actor_name 
     , r.total_votes
     , COUNT(r.movie_id) as movie_count
     , r.avg_rating as actor_avg_rating
     , RANK() OVER( PARTITION BY
        rm.category = 'actor'
        ORDER BY 
        r.avg_rating DESC
        ) actor_rank
  FROM names as n
  JOIN role_mapping as rm
    ON n.id = rm.movie_id
  JOIN movie as m
    ON m.id = rm.movie_id
  JOIN ratings as r
    ON r.movie_id = m.id
 where m.country regexp '^INDIA$' 
   and m.languages regexp '^HINDI$'
 group 
    by actor_name
 having count(rm.movie_id) >= 5; 

The output gives no error but no result too.

Answer 1

This would work:

SELECT a.name as actor_name, c.total_votes, COUNT(c.movie_id) as movie_count,c.avg_rating as actor_avg_rating,
RANK() OVER( PARTITION BY
            d.category = 'actor'
            ORDER BY 
            c.avg_rating DESC
            ) actor_rank
FROM names a, movie b, ratings c, role_mapping d    
where b.country = 'INDIA'
       and b.id = c.movie_id
       and b.id= d.movie_id
       and a.id = d.name_id
    
group by actor_name
having count(d.movie_id) >= 5
order by actor_avg_rating desc
; 

You had tried joining nameid with movie id which is the mistake

Answer 2

SELECT NAME AS actor_name, 
Cast(Sum(total_votes)/Count(movie_id) AS DECIMAL(8,0)) AS total_votes, 
Count(movie_id) AS movie_count, 
avg_rating AS actor_avg_rating,
Dense_rank() OVER(ORDER BY avg_rating DESC) AS actor_rank
FROM names n INNER JOIN role_mapping r ON n.id=r.name_id 
     INNER JOIN ratings using (movie_id) INNER JOIN movie m ON m.id=r.movie_id
WHERE country="india" AND category="actor"
GROUP BY actor_name
HAVING Count(movie_id)>=5;

Answer 3

WITH top_actor
     AS (SELECT b.NAME
                AS
                actor_name,
                Sum(c.total_votes)
                AS
                   total_votes,
                Count(DISTINCT a.movie_id)
                AS
                   movie_count,
                Round(Sum(c.avg_rating * c.total_votes) / Sum(c.total_votes), 2)
                AS
                actor_avg_rating
         FROM   role_mapping a
                INNER JOIN names b
                        ON a.name_id = b.id
                INNER JOIN ratings c
                        ON a.movie_id = c.movie_id
                INNER JOIN movie d
                        ON a.movie_id = d.id
         WHERE  a.category = 'actor'
                AND d.country LIKE '%India%'
         GROUP  BY a.name_id,
                   b.NAME
         HAVING Count(DISTINCT a.movie_id) >= 5)
SELECT *,
       Rank()
         OVER (
           ORDER BY actor_avg_rating DESC) AS actor_rank
FROM   top_actor;