Determine Unhealthy Oculus Usage with SQL Analysis

oculus_sessions

+---------+---------------------+---------------------+---------+------------+
| user_id | session_start       | session_end         | device  | event_date |
+---------+---------------------+---------------------+---------+------------+
| 1       | 2024-05-01 10:00:00 | 2024-05-01 12:30:00 | Quest2  | 2024-05-01 |
| 1       | 2024-05-02 09:00:00 | 2024-05-02 13:45:00 | Quest2  | 2024-05-02 |
| 2       | 2024-05-01 18:00:00 | 2024-05-01 18:45:00 | Quest3  | 2024-05-01 |
| 3       | 2024-05-01 20:10:00 | 2024-05-01 22:10:00 | Quest2  | 2024-05-01 |
| 3       | 2024-05-02 20:05:00 | 2024-05-02 22:30:00 | Quest2  | 2024-05-02 |
+---------+---------------------+---------------------+---------+------------+

Scenario

Oculus VR usage health analysis – product health team wants to know how widespread excessive headset usage is.

Question

Propose a clear SQL-definable rule for an "unhealthy" Oculus user (e.g., >120 continuous minutes per day for ≥3 consecutive days in the last month).

Write SQL to return (a) number of unhealthy users, (b) total active users, and (c) percentage of unhealthy users over the last 30 days.

Hints

Derive session length, aggregate by user & day, use HAVING for consecutive-day condition; compute denominator from distinct active users.

Percentage of Unhealthy Oculus Users Over a 30-Day Window

Percentage of Unhealthy Oculus Users Over a Fixed 30-Day Window

You are given the oculus_sessions table, where each row records one continuous VR session for a user. The relevant columns are:

• user_id — the user.
• session_start, session_end — the timestamps bounding a single continuous session. The session length in minutes is (session_end - session_start).
• event_date — the calendar day the session belongs to.

Consider only the fixed window 2025-05-03 to 2025-06-01 inclusive.

Define an unhealthy user as one who, within this window, has at least one session longer than 120 minutes on each of 3 or more consecutive calendar days. (A day qualifies if the user's single longest session that day exceeds 120 minutes; the qualifying days must form a run of 3+ consecutive dates.)

Write a SQL query that returns exactly one row with these three columns, in this order:

1. unhealthy_users — the count of distinct unhealthy users.
2. active_users — the count of distinct users with any session in the window (the total active user base).
3. pct_unhealthy — unhealthy_users as a percentage of active_users, rounded to 2 decimal places (return 0 when there are no active users).

Table oculus_sessions

column	type	constraints
user_id	INTEGER	NOT NULL
session_start	TIMESTAMP	NOT NULL
session_end	TIMESTAMP	NOT NULL
device	VARCHAR(20)	NOT NULL
event_date	DATE	NOT NULL

Sample data:

device	event_date	session_end	session_start	user_id
Quest2	2025-05-03	2025-05-03 12:30:00	2025-05-03 10:00:00	1
Quest2	2025-05-04	2025-05-04 13:45:00	2025-05-04 09:00:00	1
Quest2	2025-05-05	2025-05-05 11:30:00	2025-05-05 09:00:00	1
Quest3	2025-05-03	2025-05-03 18:45:00	2025-05-03 18:00:00	2
Quest2	2025-05-03	2025-05-03 22:10:00	2025-05-03 20:10:00	3
Quest2	2025-05-04	2025-05-04 22:30:00	2025-05-04 20:05:00	3

Expected output

One row: unhealthy_users, active_users, and pct_unhealthy over the 2025-05-03..2025-06-01 window. With the sample, user 1 has >120-min sessions on 3 consecutive days (May 3, 4, 5) so is unhealthy; 3 distinct active users → 33.33%.

active_users	pct_unhealthy	unhealthy_users
3	33.33	1

Hints

1. Session length in minutes is EXTRACT(EPOCH FROM (session_end - session_start)) / 60.0; collapse to one row per user-day keeping the day's MAX.
2. Use the gaps-and-islands trick: for a run of consecutive dates, (event_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date)) stays constant — but cast the row number to int so date minus integer is valid in Postgres.
3. Count distinct users in the window for the denominator, and guard the division with NULLIF or a CASE so an empty window returns 0.