Katkat
Back

Determine Unhealthy Oculus Usage with SQL Analysis

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.