Task
StreamLine wants a simple daily engagement report from its app event log. Write a SQL query to calculate user engagement metrics for a specific date range using only the user_events table.
Requirements
- Return one row per
event_date for dates between 2024-03-01 and 2024-03-05 inclusive.
- For each date, calculate:
total_events: total number of event rowsactive_users: count of distinct non-null user_id values
- Sort the result by
event_date ascending.
Table Definition
| column | type | description |
|---|
| event_id | INT | Primary key for each event |
| user_id | INT | User who generated the event; may be NULL for anonymous activity |
| event_type | VARCHAR(50) | Type of event such as login, click, or purchase |
| event_date | DATE | Date the event occurred |
| device_type | VARCHAR(20) | Device used for the event |
Sample Data
| event_id | user_id | event_type | event_date | device_type |
|---|
| 3 | 102 | click | 2024-03-01 | web |
| 1 | 101 | login | 2024-03-01 | mobile |
| 8 | 104 | login | 2024-03-03 | mobile |
| 5 | 101 | purchase | 2024-03-02 | mobile |
| 10 | NULL | view | 2024-03-04 | web |
| 12 | 106 | login | 2024-03-05 | tablet |
Expected Output
| event_date | total_events | active_users |
|---|
| 2024-03-01 | 3 | 2 |
| 2024-03-02 | 3 | 2 |
| 2024-03-03 | 2 | 2 |
| 2024-03-04 | 2 | 1 |
| 2024-03-05 | 2 | 2 |
