add 1369

fishercoder1534 · fishercoder1534 · commit 121459aede51 · 2020-03-07T15:33:25.000-08:00
diff --git a/README.md b/README.md
@@ -917,6 +917,7 @@ _If you like this project, please leave me a star._ &#9733;
 
 |  #  |      Title     |   Solutions   | Video         | Difficulty    | Tag          
 |-----|----------------|---------------|---------------|---------------|-------------
+|1369|[Get the Second Most Recent Activity](https://leetcode.com/problems/get-the-second-most-recent-activity/)|[Solution](../master/database/_1369.sql) || Hard |
 |1364|[Number of Trusted Contacts of a Customer](https://leetcode.com/problems/number-of-trusted-contacts-of-a-customer/)|[Solution](../master/database/_1364.sql) || Medium |
 |1355|[Activity Participants](https://leetcode.com/problems/activity-participants/)|[Solution](../master/database/_1355.sql) || Medium |
 |1350|[Students With Invalid Departments](https://leetcode.com/problems/students-with-invalid-departments/)|[Solution](../master/database/_1350.sql) || Easy |
diff --git a/database/_1369.sql b/database/_1369.sql
@@ -0,0 +1,60 @@
+--1369. Get the Second Most Recent Activity
+--
+--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.
+
+--# Write your MySQL query statement below
+--credit: https://leetcode.com/problems/get-the-second-most-recent-activity/discuss/530992/MySQL-solution-184ms-no-subquery
+
+select * from UserActivity
+group by username
+having count(*) = 1
+
+union all
+
+select u1.*
+from UserActivity as u1
+left join UserActivity as u2
+on u1.username = u2.username and u1.endDate < u2.endDate
+group by u1.username, u1.endDate
+having count(u2.endDate) = 1
