Calculate Average Session Length and Compare App Performance
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
user_sessions
+---------+------------+------------+---------------------+---------------------+
| user_id | session_id | app | session_start | session_end |
+---------+------------+------------+---------------------+---------------------+
| 101 | s1 | Instagram | 2023-07-10 10:00:00 | 2023-07-10 10:15:00 |
| 101 | s2 | Facebook | 2023-07-10 10:20:00 | 2023-07-10 10:35:00 |
| 102 | s3 | Messenger | 2023-07-10 11:00:00 | 2023-07-10 11:05:00 |
| 103 | s4 | Instagram | 2023-07-10 12:00:00 | 2023-07-10 12:45:00 |
+---------+------------+------------+---------------------+---------------------+
##### Scenario
Analyzing cross-app user session logs to measure engagement and switching behavior.
##### Question
For yesterday, calculate the average session length (session_end − session_start) for each app. Propose and compute a metric to compare daily app performance, explaining why your chosen denominator makes sense. Compute each app's daily bounce rate: users who switch to another app and then return to the first app within the same day.
##### Hints
Use DATE filters, window/aggregate functions, define metrics clearly.
Overview: This question evaluates sessionization, time-based aggregation, and metric design competencies using SQL and Python, testing a data scientist's ability to process timestamped logs, compute engagement metrics, and interpret user-switching behavior within the Data Manipulation domain.
You are given a table user_sessions that logs user activity across multiple apps. For the date 2025-05-31, write a query that:
1) Calculates the average session length (session_end − session_start) in minutes for each app.
2) Computes an engagement metric called engagement_share for each app, defined as: total session minutes for that app on that day divided by the total session minutes across all apps on that day.
3) Computes each app's daily bounce_rate, defined as the fraction of the app's daily active users who exhibit at least one A→B→A pattern within that same day (where A and B are different apps used by the same user on 2025-05-31).
Return one row per app with columns: app, avg_session_minutes, engagement_share, and bounce_rate.
Tables
user_sessions(user_id INTEGER, session_id VARCHAR(20), app VARCHAR(50), session_start TIMESTAMP, session_end TIMESTAMP)
Hints
- Filter to the target date (2025-05-31) using a date-range predicate on session_start.
- Compute session duration in minutes via EXTRACT(EPOCH FROM (session_end - session_start)) / 60.0.