Determine Unhealthy Oculus Usage with SQL Analysis
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates SQL-based data manipulation and time-series analysis competencies, including sessionization, user-day aggregation, and detection of sustained behavioral patterns in event data; it is categorized under Data Manipulation (SQL/Python) and targets practical application skills.
## 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).
Tables
oculus_sessions(user_id INTEGER, session_start TIMESTAMP, session_end TIMESTAMP, device VARCHAR(20), event_date DATE)
Hints
- 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.
- 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.