Last Updated: 26 Aug, 2021

Get the Second Most Recent Activity

Hard
Asked in companies
AmazonHoneywell

Problem statement

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.
Similar problems

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find the Quiet Students in All Exams

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Find Cumulative Salary of an Employee

Hard
Posted: 26 Aug, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Marvel Cities

Easy
Posted: 2 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Director's Actor

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021

Spotify Sessions

Easy
Posted: 3 Sep, 2021