Task
At StreamForge, product analysts want to identify users who completed action A but did not complete action B within the next 24 hours. Write a PostgreSQL query to return those users.
Requirements
- Consider each
A event independently.
- A user qualifies if they have at least one
A event with no corresponding B event from the same user in the next 24 hours.
- Return each qualifying user once, along with the timestamp of the qualifying
A event.
- Sort the result by
a_timestamp ascending, then user_id ascending.
Table Definitions
| Table | Column | Type | Description |
|---|
| users | user_id | INT | Unique user identifier |
| users | user_name | VARCHAR(100) | User display name |
| users | signup_date | DATE | Date the user signed up |
| events | event_id | INT | Unique event identifier |
| events | user_id | INT | User who performed the event |
| events | action_type | VARCHAR(10) | Event type such as A, B, or other actions |
| events | event_time | TIMESTAMP | Timestamp of the event |
Sample Data
| user_id | user_name | signup_date |
|---|
| 3 | Carla Diaz | 2024-01-03 |
| 1 | Alice Chen | 2024-01-01 |
| 8 | Hana Kim | 2024-01-08 |
| event_id | user_id | action_type | event_time |
|---|
| 101 | 1 | A | 2024-01-10 09:00:00 |
| 102 | 1 | B | 2024-01-10 20:00:00 |
| 103 | 1 | A | 2024-01-12 08:00:00 |
| 104 | 1 | B | 2024-01-13 09:00:00 |
| 105 | 2 | A | 2024-01-11 10:00:00 |
| 106 | 2 | C | 2024-01-11 11:00:00 |
| ... | ... | ... | ... |
Expected Output
| user_id | user_name | a_timestamp |
|---|
| 4 | Daniel Wu | 2024-01-09 23:30:00 |
| 2 | Brian Lee | 2024-01-11 10:00:00 |
| 1 | Alice Chen | 2024-01-12 08:00:00 |
| 6 | Fatima Noor | 2024-01-12 12:00:00 |
| 8 | Hana Kim | 2024-01-14 00:00:00 |
