Task
On Facebook, analysts often need to measure how long each user has been active within an event log. Write a SQL query to find the time between each user's first and last recorded action.
Requirements
- Return one row per
user_id.
- Find each user's earliest and latest
action_time.
- Calculate the elapsed time between those two timestamps as
time_between_actions.
- Include users with only one action; their elapsed time should be
INTERVAL '0 seconds'.
- Order the result by
user_id ascending.
Table Definition
| column | type | description |
|---|
| action_id | INT | Unique action event ID |
| user_id | INT | Facebook user identifier |
| action_type | VARCHAR(50) | Type of action performed |
| action_time | TIMESTAMP | Time the action occurred |
| device | VARCHAR(20) | Device used for the action |
Sample Data
| action_id | user_id | action_type | action_time | device |
|---|
| 8 | 104 | like | 2024-01-03 09:00:00 | android |
| 2 | 101 | comment | 2024-01-01 09:30:00 | web |
| 10 | 106 | login | 2024-01-01 23:59:59 | web |
| 1 | 101 | login | 2024-01-01 08:00:00 | ios |
| 5 | 103 | login | 2024-01-02 12:00:00 | web |
| 3 | 101 | like | 2024-01-02 10:00:00 | ios |
| 6 | 103 | share | 2024-01-02 12:00:00 | web |
| 4 | 102 | login | 2024-01-05 14:00:00 | android |
| 9 | 105 | share | 2024-01-04 18:45:00 | ios |
| 7 | 104 | login | 2024-01-01 07:15:00 | android |
Expected Output
| user_id | first_action_time | last_action_time | time_between_actions |
|---|
| 101 | 2024-01-01 08:00:00 | 2024-01-02 10:00:00 | 1 day 02:00:00 |
| 102 | 2024-01-05 14:00:00 | 2024-01-05 14:00:00 | 00:00:00 |
| 103 | 2024-01-02 12:00:00 | 2024-01-02 12:00:00 | 00:00:00 |
| 104 | 2024-01-01 07:15:00 | 2024-01-03 09:00:00 | 2 days 01:45:00 |
| 105 | 2024-01-04 18:45:00 | 2024-01-04 18:45:00 | 00:00:00 |
| 106 | 2024-01-01 23:59:59 | 2024-01-01 23:59:59 | 00:00:00 |
