Penultimate User Activity Snapshot

Medium

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: username activity start_date end_date Return one row per username. Sort by username ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

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 2

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 3

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.

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

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 |

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 |

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 |