Oscar Nominees Table
Table: oscar_nominees
Schema:
year
int
category
varchar
nominee
varchar
movie
varchar
winner
bool
id
int
Preview:
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.
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.
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
;
Last updated
Was this helpful?