Better Than Its Own Genre

A 7.5 horror film and a 7.5 crime film are not the same achievement. Crime averages 8.48 in this table; horror averages 7.35. The programming desk wants every film that beats the average of its own genre — each row judged against its own crowd, not against the table.

Show exactly these 3 columns, in this order: title, genre, rating. No aliases, no rounding. Order by genre ascending, then rating descending, then title ascending.

The new idea, and the only one on this card: the inner query reads a value from the outer row. Give the outer table an alias, give the inner one a different alias, and join them in the inner WHERE — WHERE m2.genre = m.genre. That single line is what makes it *correlated*: the subquery no longer runs once, it runs once per row, and each row gets its own average.

Compare it with Movies Rated Above the Average, which used one average for everything. That query returns 61 rows, this one returns 52, and they are not nested lists: 5 films are above their own genre's average but below the table's, and 14 are above the table's but not above their own genre's. Different questions, different answers.

One genre returns nothing at all. Mystery has a single film, so its average *is* that film's rating — and nothing is strictly greater than itself.