Oscar Nominees Table

Table: oscar_nominees

Schema:

year	int
category	varchar
nominee	varchar
movie	varchar
winner	bool
id	int

Preview:

year	category	nominee	movie	winner	id
2010	actor in a leading role	James Franco	127 Hours	FALSE	648
2003	actor in a supporting role	Benicio Del Toro	21 Grams	FALSE	139
2003	actress in a leading role	Naomi Watts	21 Grams	FALSE	1119
2001	actress in a supporting role	Jennifer Connelly	A Beautiful Mind	TRUE	710
2001	actor in a leading role	Russell Crowe	A Beautiful Mind	FALSE	1315

MEDIUM

Find the nominee who has won the most Oscars

Find the nominee who has won the most Oscars. Output the nominee's name alongside the result.

StrataScratch - Find the nominee who has won the most Oscarsplatform.stratascratch.com

SELECT nominee, count(id) AS oscar_won
FROM oscar_nominees
WHERE winner = TRUE
GROUP BY  1
HAVING count(id) = (SELECT COUNT(ID) 
                FROM oscar_nominees
                WHERE winner = TRUE 
                GROUP BY nominee 
                ORDER BY COUNT(ID) DESC 
                LIMIT 1) -- to include all winners 
ORDER BY count(id) DESC;

HARD

Find the genre of the person with the most number of oscar winnings

Find the genre of the person with the most number of oscar winnings. If there are more than one person with the same number of oscar wins, return the first one in alphabetic order based on their name. Use the names as keys when joining the tables.

StrataScratch - Find the genre of the person with the most number of oscar winningsplatform.stratascratch.com

SELECT t1.*, n.genre
SELECT o.nominee, count(o.id) AS oscar_won
FROM oscar_nominees o
WHERE winner = TRUE
GROUP BY 1
ORDER BY count(id) DESC, nominee
LIMIT 1) t1
LEFT JOIN nominee_information n
ON t1.id = n.id
;