Task
StreamHub wants to identify its most active users based on recorded app events. Write a SQL query to return the top N active users, where activity is measured by the number of valid events tied to active user accounts.
Requirements
- Count events per user using the
events table.
- Only include users whose
account_status = 'active'.
- Exclude events where
event_type is NULL.
- Return the top 3 users ranked by total event count, ordered by event count descending and then
user_id ascending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for each user |
| user_name | VARCHAR(100) | User display name |
| account_status | VARCHAR(20) | Account state such as active, inactive, or suspended |
| signup_date | DATE | Date the user registered |
events
| column | type | description |
|---|
| event_id | INT | Primary key for each event |
| user_id | INT | User who generated the event |
| event_type | VARCHAR(50) | Type of event such as login or play |
| event_time | TIMESTAMP | Time the event occurred |
Sample Data
users
| user_id | user_name | account_status | signup_date |
|---|
| 104 | Diana Park | active | 2024-01-20 |
| 101 | Alice Chen | active | 2024-01-05 |
| 106 | Farah Khan | active | 2024-02-10 |
| 102 | Bob Singh | inactive | 2024-01-08 |
| 103 | Carlos Ruiz | active | 2024-01-15 |
events
| event_id | user_id | event_type | event_time |
|---|
| 11 | 104 | play | 2024-03-03 10:00:00 |
| 2 | 101 | play | 2024-03-01 08:05:00 |
| 8 | 103 | like | 2024-03-02 09:00:00 |
| 14 | 106 | login | 2024-03-04 07:00:00 |
| 5 | 102 | login | 2024-03-01 09:00:00 |
Expected Output
| user_id | user_name | total_events |
|---|
| 101 | Alice Chen | 4 |
| 103 | Carlos Ruiz | 4 |
| 104 | Diana Park | 3 |
