# $1369. 获取最近第二次的活动
# https://leetcode-cn.com/problems/get-the-second-most-recent-activity/

# SQL架构
Create table If Not Exists UserActivity (username varchar(30), activity varchar(30), startDate date, endDate date);
Truncate table UserActivity;
insert into UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-12', '2020-02-20');
insert into UserActivity (username, activity, startDate, endDate) values ('Alice', 'Dancing', '2020-02-21', '2020-02-23');
insert into UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-24', '2020-02-28');
insert into UserActivity (username, activity, startDate, endDate) values ('Bob', 'Travel', '2020-02-11', '2020-02-18');

# Write your MySQL query statement below
select
    tmp.username,
    tmp.activity,
    tmp.startDate,
    tmp.endDate
from
    (
        select
            u.username,
            u.activity,
            u.startDate,
            u.endDate,
            count(*) over(partition by u.username) as cnt,
            row_number() over(
                partition by u.username
                order by
                    u.startDate desc
            ) as rn
        from
            UserActivity u
    ) tmp
where
    tmp.cnt = '1'
    or tmp.rn = '2';

# clean-up
drop table UserActivity;