Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1369.html
1369. Get the Second Most Recent Activity
Level
Hard
Description
Table: UserActivity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| username | varchar |
| activity | varchar |
| startDate | Date |
| endDate | Date |
+---------------+---------+
This table does not contain primary key.
This table contain information about the activity performed of each user in a period of time.
A person with username performed a activity from startDate to endDate.
Write an SQL query to show the second most recent activity of each user.
If the user only has one activity, return that one.
A user can’t perform more than one activity at the same time. Return the result table in any order.
The query result format is in the following example:
UserActivity table:
+------------+--------------+-------------+-------------+
| username | activity | startDate | endDate |
+------------+--------------+-------------+-------------+
| Alice | Travel | 2020-02-12 | 2020-02-20 |
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Alice | Travel | 2020-02-24 | 2020-02-28 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
+------------+--------------+-------------+-------------+
Result table:
+------------+--------------+-------------+-------------+
| username | activity | startDate | endDate |
+------------+--------------+-------------+-------------+
| Alice | Dancing | 2020-02-21 | 2020-02-23 |
| Bob | Travel | 2020-02-11 | 2020-02-18 |
+------------+--------------+-------------+-------------+
The most recent activity of Alice is Travel from 2020-02-24 to 2020-02-28, before that she was dancing from 2020-02-21 to 2020-02-23.
Bob only has one record, we just take that one.
Solution
To get the second most recent activity, for each user, if the user has at least two activities, then filter out the most recent activity. Then select the most recent activity for each user after filtering.
# Write your MySQL query statement below
select * from UserActivity where (username, startDate) in (
select u1.username, max(u1.startDate) from UserActivity u1
where (u1.username, u1.startDate) not in (
select u2.username, max(u2.startDate) from UserActivity u2
group by u2.username
having count(u2.username) > 1
)
group by u1.username
);