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.
user_id.action_time.time_between_actions.INTERVAL '0 seconds'.user_id ascending.| 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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| action_idPK | INT | Unique action event ID |
| user_id | INT | Facebook user identifier |
| action_type | VARCHAR(50) | Type of action performed |
| action_time | TIMESTAMP | Timestamp of the action |
| device | VARCHAR(20) | Device used for the action |
| 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 |
| 1 | 28 | tag_photo | 2024-01-04 02:44:28 | mobile_web |
| 2 | 86 | login | 2024-01-01 06:46:07 | instagram_crosspost |
| 3 | 85 | receive_message | 2023-12-30 15:37:05 | lite |
| 4 | 44 | follow | 2024-01-05 03:03:12 | wearable |
| 5 | 29 | logout | 2024-01-06 21:03:41 | tablet |
| 6 | 22 | decline_request | 2024-01-08 12:31:28 | smart_tv |
| 7 | 60 | friend_request | 2024-01-02 13:58:06 | web_messenger |
| 8 | 96 | create_story | 2024-01-02 16:53:43 | beta_android |
| 9 | 51 | receive_message | 2024-01-02 03:11:41 | instagram_crosspost |
| 10 | 12 | join_group | 2024-01-03 02:41:07 | beta_android |
| 11 | 32 | accept_request | 2024-01-04 18:16:19 | business_suite |
| 12 | 80 | receive_message | 2023-12-31 23:03:41 | quest |
| 13 | 100 | tag_photo | 2024-01-07 06:51:15 | mobile_web |
| 14 | 30 | react | 2023-12-29 18:21:29 | kiosk |
| 15 | 81 | follow | 2024-01-04 22:53:49 | threads_crosspost |
| 16 | 53 | tag_photo | 2024-01-02 07:10:34 | wearable |
| 17 | 89 | click_notification | 2023-12-29 14:32:43 | mac |
| 18 | 19 | null | 2024-01-07 21:00:15 | null |
| user_id | first_action_time | last_action_time | time_between_actions |
|---|---|---|---|
| 19 | 2024-01-07 21:00:15 | 2024-01-07 21:00:15 | 0:00:00 |
| 30 | 2023-12-29 18:21:29 | 2023-12-29 18:21:29 | 0:00:00 |
| 32 | 2024-01-04 18:16:19 | 2024-01-04 18:16:19 | 0:00:00 |
| 53 | 2024-01-02 07:10:34 | 2024-01-02 07:10:34 | 0:00:00 |
| 80 | 2023-12-31 23:03:41 | 2023-12-31 23:03:41 | 0:00:00 |
| 81 | 2024-01-04 22:53:49 | 2024-01-04 22:53:49 | 0:00:00 |
| 89 | 2023-12-29 14:32:43 | 2023-12-29 14:32:43 | 0:00:00 |
| 100 | 2024-01-07 06:51:15 | 2024-01-07 06:51:15 | 0:00:00 |
| 101 | 2024-01-01 08:00:00 | 2024-01-02 10:00:00 | 1 day, 2:00:00 |
| 102 | 2024-01-05 14:00:00 | 2024-01-05 14:00:00 | 0:00:00 |
| 103 | 2024-01-02 12:00:00 | 2024-01-02 12:00:00 | 0:00:00 |
| 104 | 2024-01-01 07:15:00 | 2024-01-03 09:00:00 | 2 days, 1:45:00 |
| 105 | 2024-01-04 18:45:00 | 2024-01-04 18:45:00 | 0:00:00 |
| 106 | 2024-01-01 23:59:59 | 2024-01-01 23:59:59 | 0:00:00 |
