Retrieve First Active and Last Inactive Dates per User
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given a table activity that tracks user activities, write a SQL query to retrieve the first active date and last inactive date for each user.
## Table Schema
CREATE TABLE activity (
id INT PRIMARY KEY,
user_id INT,
date DATE,
status VARCHAR
(
20) -- 'active' or 'inactive'
);
## Sample Data
+----+---------+------------+----------+
| id | user_id | date | status |
+----+---------+------------+----------+
| 1 | 1 | 2023-01-01 | active |
| 2 | 1 | 2023-01-05 | inactive |
| 3 | 1 | 2023-01-10 | active |
| 4 | 2 | 2023-01-02 | active |
| 5 | 2 | 2023-01-08 | inactive |
| 6 | 3 | 2023-01-03 | inactive |
+----+---------+------------+----------+
## Requirements
Write a SQL query that returns:
- user_id: The user identifier
- first_active_date: The earliest date when the user was active
- last_inactive_date: The latest date when the user was inactive
1 | 2023-01-01 | 2023-01-10
2 | 2023-03-20 | 2023-01-05
## Notes
- If a user has no active dates, first_active_date should be NULL
- If a user has no inactive dates, last_inactive_date should be NULL
- Use conditional aggregation (CASE) or window functions to isolate the two dates.
Overview: The question evaluates a data scientist's competence in SQL data aggregation, date/time handling, null-aware summarization, and per-user record summarization within the Data Manipulation (SQL/Python) domain.
Given the activity table, return one row per user with the earliest date they were 'active' and the latest date they were 'inactive'. If a user never had a given status, return NULL for that date. Output columns: user_id, first_active_date, last_inactive_date.
Tables
activity(id INT, user_id INT, date DATE, status VARCHAR(20))
Hints
- Use conditional aggregation: MIN over active rows and MAX over inactive rows.
- Group by user_id to return one row per user.
Community answers
Answer by Jay123
had the exact same question earlier
Answer by [Deleted User]
great question
Answer by Andy Nian
Thanks for sharing
Answer by Xiaoming
Wow!
Answer by mersalmanoj005
SELECT
user_id,
MIN(CASE WHEN status = 'active' THEN date END) AS first_active_date,
MAX(CASE WHEN status = 'inactive' THEN date END) AS last_inactive_date
FROM your_table
GROUP BY user_id;