Penultimate User Activity Snapshot

MEDIUM20 pts

A product analytics team records bounded user sessions in a table and needs a one-row recency snapshot per account.

Table: user_activity

username: user identifier
activity: activity label (for example: "search", "checkout", "stream")
start_date: session start date (YYYY-MM-DD)
end_date: session end date (YYYY-MM-DD)

Data characteristics:

For a given user, activity intervals do not overlap.
Exact duplicate rows may appear due to ingestion replay.

Task: For each username, return exactly one activity interval:

If the user has at least two unique intervals, return the second latest interval.
If the user has only one unique interval, return that interval.

Recency rule:

More recent means larger start_date.

Output requirements:

Return exactly these columns in this order:
1. username
2. activity
3. start_date
4. end_date
Return one row per username.
Sort by username ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

user_activity:
| username | activity | start_date | end_date   |
|----------|----------|------------|------------|
| Alice    | Travel   | 2020-02-12 | 2020-02-20 |
| Alice    | Dance    | 2020-02-21 | 2020-02-23 |
| Alice    | Travel   | 2020-02-24 | 2020-02-28 |
| Bob      | Travel   | 2020-02-11 | 2020-02-18 |

Output

[
  {"username":"Alice","activity":"Dance","start_date":"2020-02-21","end_date":"2020-02-23"},
  {"username":"Bob","activity":"Travel","start_date":"2020-02-11","end_date":"2020-02-18"}
]

Explanation

Alice has three unique intervals, so we return the second latest by start_date. Bob has one unique interval, so we return it.

Example

Input

user_activity:
| username | activity | start_date | end_date   |
|----------|----------|------------|------------|
| Nora     | Browse   | 2023-01-01 | 2023-01-02 |
| Nora     | Browse   | 2023-01-01 | 2023-01-02 |
| Nora     | Purchase | 2023-01-05 | 2023-01-06 |

Output

[
  {"username":"Nora","activity":"Browse","start_date":"2023-01-01","end_date":"2023-01-02"}
]

Explanation

Duplicate rows are treated as the same unique interval. Nora has two unique intervals, so we return the older one among the top two.

Example

Input

user_activity:
| username | activity | start_date | end_date   |
|----------|----------|------------|------------|
| Kai      | Login    | 2024-03-09 | 2024-03-09 |
| Kai      | Search   | 2024-03-10 | 2024-03-10 |
| Kai      | Search   | 2024-03-10 | 2024-03-10 |
| Mira     | View     | 2024-05-01 | 2024-05-02 |

Output

[
  {"username":"Kai","activity":"Login","start_date":"2024-03-09","end_date":"2024-03-09"},
  {"username":"Mira","activity":"View","start_date":"2024-05-01","end_date":"2024-05-02"}
]

Explanation

Kai's latest unique interval is Search, so the second latest is Login. Mira has only one interval.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in user_activity <= 200000
username and activity are non-empty strings
start_date and end_date are valid dates in YYYY-MM-DD format
start_date <= end_date for every row
For each user, unique intervals do not overlap
Exact duplicate rows may exist
Return one row per username appearing in the input
If a user has 2+ unique intervals, return the second latest by start_date
If a user has 1 unique interval, return that interval
Return exactly: username, activity, start_date, end_date
Sort output by username ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required output columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

user_activity =

[{"username":"Alice","activity":"Travel","start_date":"2020-02-12","end_date":"2020-02-20"},{"username":"Alice","activity":"Dance","start_date":"2020-02-21","end_date":"2020-02-23"},{"username":"Alice","activity":"Travel","start_date":"2020-02-24","end_date":"2020-02-28"},{"username":"Bob","activity":"Travel","start_date":"2020-02-11","end_date":"2020-02-18"}]

Penultimate User Activity Snapshot

Hints

Penultimate User Activity Snapshot

Hints