Your question is Compute Session Durations and Gaps. Start with the requirements and the one table 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.
Adobe Analytics stores user session logs with a start and end timestamp for each session. Write a SQL query to calculate the duration of each session and identify the gap from the previous session for the same user.
session_duration_minutes as the difference between session_end and session_start.gap_from_previous_minutes as the time between the current session start and the previous session end for the same user.gap_flag with value TRUE when the gap is greater than 30 minutes, otherwise FALSE.user_id, then session_start.| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Primary key for the session record |
| user_id | INT | Adobe user identifier |
| session_start | TIMESTAMP | Session start time |
| session_end | TIMESTAMP | Session end time |
| device_type | VARCHAR(50) | Device used for the session |