Your question is SQL for User Session Time. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
The Nielsen Digital Analytics team needs a session-level report showing engagement for each observed user. Write a PostgreSQL query that calculates each user's click rate across all of their events and derives the start and end timestamps for every session.
user_click_rate as click events divided by all recorded events for that user, rounded to four decimal places. Users or sessions with no events must have a rate of 0.session_start and session_end using the minimum and maximum event timestamps. Sessions with no events should retain NULL timestamps.user_id and session_id.| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique Nielsen Digital Analytics user identifier |
| user_name | VARCHAR(80) | User display name |
| Column | Type | Description |
|---|---|---|
| session_idPK | INTEGER | Unique session identifier |
| user_id | INTEGER | User who initiated the session |
| Column | Type | Description |
|---|---|---|
| event_idPK | INTEGER | Unique event identifier |
| session_id | INTEGER | Session containing the event |
| event_type | VARCHAR(20) | Recorded event type |
| event_ts | TIMESTAMP | Timestamp when the event occurred |