Calculate Average Session Length and Compare App Performance

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.

Daily App Engagement Metrics and Bounce Rate

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.

Table user_sessions

column	type	constraints
user_id	INTEGER	NOT NULL
session_id	VARCHAR(20)	PRIMARY KEY, NOT NULL
app	VARCHAR(50)	NOT NULL
session_start	TIMESTAMP	NOT NULL
session_end	TIMESTAMP	NOT NULL

Sample data:

app	session_end	session_id	session_start	user_id
Instagram	2025-05-31 10:15:00	s1	2025-05-31 10:00:00	101
Facebook	2025-05-31 10:35:00	s2	2025-05-31 10:20:00	101
Messenger	2025-05-31 11:05:00	s3	2025-05-31 11:00:00	102
Instagram	2025-05-31 12:45:00	s4	2025-05-31 12:00:00	103

Expected output

One row per app for the date 2025-05-31. Columns: avg_session_minutes (average of session_end − session_start, in minutes); engagement_share (total session minutes for the app divided by total session minutes across all apps on that day — the denominator is total cross-app attention, which normalizes for day-to-day volume and enables fair cross-app comparison); bounce_rate (fraction of the app's daily active users with at least one A→B→A pattern that day).

app	avg_session_minutes	bounce_rate	engagement_share
Facebook	15	0	0.1875
Instagram	30	0	0.75
Messenger	5	0	0.0625

Hints

1. Filter to the target date (2025-05-31) using a date-range predicate on session_start.
2. Compute session duration in minutes via EXTRACT(EPOCH FROM (session_end - session_start)) / 60.0.
3. Use window functions (LAG/LEAD) partitioned by user and day to detect A→B→A bounce patterns.
4. Define engagement_share as app_total_minutes / total_minutes_all_apps for that day to normalize cross-app comparisons.