Task
StreamWave tracks user activity across web and mobile sessions. Write a PostgreSQL query to return each active user's first and last event based on event timestamp.
Requirements
- Include only users whose
status is 'active'.
- Return one row per active user, even if the user has no events.
- For each user, show the first event type and timestamp, and the last event type and timestamp.
- If multiple events share the same timestamp for a user, break ties using the smaller
event_id for the first event and the larger event_id for the last event.
- Order the final result by
user_id.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for the user |
| full_name | VARCHAR(100) | User name |
| status | VARCHAR(20) | Account status |
| signup_date | DATE | Date the user signed up |
platforms
| column | type | description |
|---|
| platform_id | INT | Primary key for platform |
| platform_name | VARCHAR(50) | Platform name |
events
| column | type | description |
|---|
| event_id | INT | Primary key for event |
| user_id | INT | User who generated the event |
| platform_id | INT | Platform where the event occurred |
| event_type | VARCHAR(50) | Event name |
| event_time | TIMESTAMP | Event timestamp |
Sample Data
Representative rows are included below in the dataset. Some users have no events, some events have NULL platforms, and some users have multiple events at the same timestamp.
Expected Output
| user_id | full_name | first_event_type | first_event_time | last_event_type | last_event_time |
|---|
| 1 | Ava Chen | login | 2024-02-01 09:00:00 | purchase | 2024-02-03 18:30:00 |
| 2 | Ben Ortiz | login | 2024-02-01 08:00:00 | logout | 2024-02-01 08:00:00 |
| 4 | Diego Park | NULL | NULL | NULL | NULL |
| 5 | Ella Singh | login | 2024-02-10 07:45:00 | support_ticket | 2024-02-11 16:00:00 |
| 7 | Grace Kim | login | 2024-02-15 12:00:00 | login | 2024-02-15 12:00:00 |
