# $1811. 寻找面试候选人
# https://leetcode.cn/problems/find-interview-candidates/

# SQL架构
Create table If Not Exists Contests (contest_id int, gold_medal int, silver_medal int, bronze_medal int);
Create table If Not Exists Users (user_id int, mail varchar(50), name varchar(30));
Truncate table Contests;
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('190', '1', '5', '2');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('191', '2', '3', '5');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('192', '5', '2', '3');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('193', '1', '3', '5');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('194', '4', '5', '2');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('195', '4', '2', '1');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('196', '1', '5', '2');
Truncate table Users;
insert into Users (user_id, mail, name) values ('1', 'sarah@leetcode.com', 'Sarah');
insert into Users (user_id, mail, name) values ('2', 'bob@leetcode.com', 'Bob');
insert into Users (user_id, mail, name) values ('3', 'alice@leetcode.com', 'Alice');
insert into Users (user_id, mail, name) values ('4', 'hercy@leetcode.com', 'Hercy');
insert into Users (user_id, mail, name) values ('5', 'quarz@leetcode.com', 'Quarz');

# Write your MySQL query statement below
select
    name,
    mail
from
    (
        select
            user_id
        from
            (
                select
                    user_id,
                    contest_id - row_number() over(
                        partition by user_id
                        order by
                            contest_id
                    ) as diff
                from
                    (
                        select
                            contest_id,
                            gold_medal as user_id
                        from
                            Contests
                        union
                        all
                        select
                            contest_id,
                            silver_medal as user_id
                        from
                            Contests
                        union
                        all
                        select
                            contest_id,
                            bronze_medal as user_id
                        from
                            Contests
                    ) temp1
            ) temp2
        group by
            user_id,
            diff
        having
            count(diff) >= 3
        union
        select
            gold_medal user_id
        from
            Contests
        group by
            gold_medal
        having
            count(gold_medal) >= 3
    ) temp3
    join Users using(user_id);

# clean-up
drop table Contests;
drop table Users;