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.

Why a regexp? `LIKE` or `=` are probably sufficient, no? Moreover by default MySQL regexps are case sensitive... see the doc to request case insensitivity (unless the country and language are indeed in big letters). You know that `regexp ^WORD$` is (almost) equivalent to `="WORD"`, probably not what you want. (almost because `=` is case insensitive) — Déjà vu, Oct 11 '20 at 07:38
I tried with LIKE and = but no result and hence I tried with regexp. — Aishwarya Wuntkal, Oct 11 '20 at 07:42
Not sure if you have a good quality data. Inner joins will not work if you dont have data in all the tables. can you pls try changing where clause like this `where upper(m.country) like '%INDIA%' and upper(m.languages) regexp '%HINDI%'`. Or you can replace all inner join with left join and see which join is causing 0 results. — Koushik Roy, Oct 11 '20 at 07:42
*I see blank rows when the query is run.* Eh? Blank rows are not the same as no result!?!?! — Strawberry, Oct 11 '20 at 07:45
`JOIN role_mapping as rm ON n.id = rm.movie_id`. You may want to rethink that. Also, use `group by n.id` (there might be actors with the same name just to check if you do this correctly). Also, your select list is [invalid with your `group by`](https://stackoverflow.com/q/34115174) (e.g. "r.total_votes" doesn't make sense: which of the at least 5 votes per actor do you want to display here). — Solarflare, Oct 11 '20 at 08:04

score 1 · Accepted Answer · edited Nov 24 '20 at 13:57

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

Sayan Sarkar · Answer 2 · 2022-02-01T06:32:15.527

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;

score 0 · Answer 3 · edited Apr 30 '22 at 14:23

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;

I hope this will give u correct answer – Syed Naveed Apr 30 '22 at 08:41 — Syed Naveed, Apr 30 '22 at 08:41

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

3 Answers3