# $1454. 活跃用户
# https://leetcode-cn.com/problems/active-users/

# SQL架构
Create table If Not Exists Accounts (id int, name varchar(10));
Create table If Not Exists Logins (id int, login_date date);
Truncate table Accounts;
insert into Accounts (id, name) values ('1', 'Winston');
insert into Accounts (id, name) values ('7', 'Jonathan');
Truncate table Logins;
insert into Logins (id, login_date) values ('7', '2020-05-30');
insert into Logins (id, login_date) values ('1', '2020-05-30');
insert into Logins (id, login_date) values ('7', '2020-05-31');
insert into Logins (id, login_date) values ('7', '2020-06-01');
insert into Logins (id, login_date) values ('7', '2020-06-02');
insert into Logins (id, login_date) values ('7', '2020-06-02');
insert into Logins (id, login_date) values ('7', '2020-06-03');
insert into Logins (id, login_date) values ('1', '2020-06-07');
insert into Logins (id, login_date) values ('7', '2020-06-10');

# Write your MySQL query statement below
select
    t4.id,
    a.name
from
    (
        select
            distinct id
        from
            (
                select
                    id,
                    subdate(login_date, rn) as diff
                from
                    (
                        select
                            id,
                            login_date,
                            row_number() over(
                                partition by id
                                order by
                                    login_date
                            ) as rn
                        from
                            (
                                select
                                    id,
                                    login_date
                                from
                                    Logins
                                group by
                                    id,
                                    login_date
                            ) t1
                    ) t2
                group by
                    id,
                    diff
                having
                    count(*) >= 5
            ) t3
    ) t4
    join Accounts a on t4.id = a.id
order by
    id;

# clean-up
drop table Accounts;
drop table Logins;