/*
Netflix SQL Interview Question

You have been given the following tables schema:

`nominee_information` Table
| Column          | Data Type   | Description                                       |
|-----------------|-------------|---------------------------------------------------|
| `name`          | VARCHAR(20) | The name of the nominee                           |
| `amg_person_id` | VARCHAR(10) | A unique identifier for the nominee               |
| `top_genre`     | VARCHAR(10) | The primary genre associated with the nominee     |
| `birthday`      | DATETIME    | The birth date of the nominee                     |
| `id`            | INT         | A unique identifier for each record in the table  |

`oscar_nominees` Table
| Column    | Data Type   | Description                                                        |
|-----------|-------------|--------------------------------------------------------------------|
| `year`    | INT         | The year of the Oscar nomination                                   |
| `category`| VARCHAR(30) | The category of the nomination                                     |
| `nominee` | VARCHAR(20) | The name of the nominee.                                           |
| `movie`   | VARCHAR(30) | The name of the movie associated with the nomination.              |
| `winner`  | INT         | Indicates whether the nominee won (1 for winner, 0 for not winner) |
| `id`      | INT         | A unique identifier for each record in the table                   |

Task:
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.
*/

CREATE TABLE nominee_information(
    name varchar(20), 
    amg_person_id varchar(10),
    top_genre varchar(10),
    birthday datetime,
    id int
);

INSERT INTO nominee_information VALUES
('Jennifer Lawrence','P562566','Drama','1990-08-15',755),
('Jonah Hill','P418718','Comedy','1983-12-20',747),
('Anne Hathaway', 'P292630','Drama', '1982-11-12',744),
('Jennifer Hudson','P454405','Drama', '1981-09-12',742),
('Rinko Kikuchi', 'P475244','Drama', '1981-01-06', 739);

CREATE TABLE oscar_nominees(
    year int,
    category varchar(30),
    nominee varchar(20),
    movie varchar(30),
    winner int,
    id int
);

INSERT INTO oscar_nominees VALUES
(2008,'actress in a leading role','Anne Hathaway','Rachel Getting Married',0,77),
(2012,'actress in a supporting role','Anne HathawayLes','Mis_rables',1,78),
(2006,'actress in a supporting role','Jennifer Hudson','Dreamgirls',1,711),
(2010,'actress in a leading role','Jennifer Lawrence','Winters Bone',1,717),
(2012,'actress in a leading role','Jennifer Lawrence','Silver Linings Playbook',1,718),
(2011,'actor in a supporting role','Jonah Hill','Moneyball',0,799),
(2006,'actress in a supporting role','Rinko Kikuchi','Babel',0,1253);

WITH winner_count AS
(
 SELECT
    nominee,
    SUM(winner) AS total_wins
 FROM oscar_nominees
 GROUP BY nominee
)

SELECT ni.top_genre
FROM winner_count wc
JOIN nominee_information ni
ON ni.name = wc.nominee
ORDER BY total_wins DESC, name
LIMIT 1;