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.
Quick Answer: 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.