App Session Gap Analysis

## Context A mobile analytics pipeline captures user session records across multiple apps. Product teams use inter-session gap metrics to understand re-engagement patterns — shorter gaps indicate stickier apps. **Schema: `sessions`** | Column | Type | Notes | |---|---|---| | `session_id` | INTEGER | Primary key | | `user_id` | INTEGER | User identifier | | `session_start_time` | TIMESTAMP | When the session began | | `session_end_time` | TIMESTAMP | When the session ended | | `date_stamp` | DATE | Calendar date of session | | `app` | VARCHAR(50) | App name | ## Task Calculate the **average inter-session gap in seconds** for each app. A gap is measured from the `session_end_time` of one session to the `session_start_time` of the user's next session on the **same app**. **Pipeline steps:** 1. Use `LAG(session_end_time) OVER (PARTITION BY user_id, app ORDER BY session_start_time)` to retrieve each session's preceding end time. 2. Compute gap = `EXTRACT(EPOCH FROM (session_start_time - prev_session_end_time))`. 3. Filter out `NULL` gaps (first session per user-app has no predecessor). 4. Average gaps per `(user_id, app)` to get each user's contribution, then average across users per app. Return `app` and `avg_session_gap_seconds` (rounded to 2 decimal places). Order by `avg_session_gap_seconds` **ascending**. Exclude apps with no calculable gaps.