Analyze VR App Usage and Engagement Metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
vr_usage
+---------+------------+---------+------------+----------+
| user_id | date | app_id | session_id | duration |
+---------+------------+---------+------------+----------+
| 10 | 2023-08-01 | 1001 | 555 | 1800 |
| 10 | 2023-08-02 | 1002 | 556 | 2400 |
| 11 | 2023-08-02 | 1001 | 557 | 1200 |
| 12 | 2023-08-03 | 1003 | 558 | 3600 |
| 10 | 2023-08-04 | 1001 | 559 | 900 |
+---------+------------+---------+------------+----------+
apps
+---------+-----------+--------------+
| app_id | app_name | app_category |
+---------+-----------+--------------+
| 1001 | SpaceWar | game |
| 1002 | VRChat | social |
| 1003 | HomeView | home |
+---------+-----------+--------------+
##### Scenario
Oculus VR usage logs and app catalog; product team needs insights into most-used apps and category engagement.
##### Question
Which app had the greatest total usage duration in the last 30 days? 2. What percentage of total VR time in that period belongs to each app_category? 3. Test the hypothesis: users of “social” apps are more engaged than users of “game” apps. Define an engagement metric, outline the SQL, and describe the statistical test.
##### Hints
Join usage with apps, filter date >= CURRENT_DATE-30, aggregate durations; for hypothesis you might use active days per user.
Overview: This question evaluates data manipulation and analytical competencies, including SQL/Python joins and aggregations, time-window filtering, definition of engagement metrics, and basic statistical hypothesis testing.
Top app by duration (2025-05-03 to 2025-06-01)
Find the single app with the highest total usage duration from 2025-05-03 to 2025-06-01 (inclusive). Return app_id, app_name, and total_duration_seconds.
Tables
vr_usage(user_id INTEGER, date DATE, app_id INTEGER, session_id INTEGER, duration INTEGER)
apps(app_id INTEGER, app_name VARCHAR(100), app_category VARCHAR(50))
Hints
- Filter vr_usage to dates between 2025-05-03 and 2025-06-01
- Join to apps to get app_name
Category share of VR time (2025-05-03 to 2025-06-01)
Compute each app_category's total usage duration and its percentage share of total VR time from 2025-05-03 to 2025-06-01 (inclusive).
Tables
vr_usage(user_id INTEGER, date DATE, app_id INTEGER, session_id INTEGER, duration INTEGER)
apps(app_id INTEGER, app_name VARCHAR(100), app_category VARCHAR(50))
Hints
- Filter vr_usage to dates between 2025-05-03 and 2025-06-01 before aggregating
- Aggregate duration by app_category
Engagement by category summary (2025-05-03 to 2025-06-01)
Using the inclusive period from 2025-05-03 through 2025-06-01, define engagement as each user's average daily active duration within an app category, where each active day first sums all session duration for that user and category. For the `social` and `game` categories, return `app_category`, `n_users`, the mean of the per-user engagement, and the sample standard deviation of the per-user engagement. Round the mean and standard deviation to 2 decimal places.
Tables
apps(app_id INTEGER, app_name VARCHAR(100), app_category VARCHAR(50))
vr_usage(user_id INTEGER, date DATE, app_id INTEGER, session_id INTEGER, duration INTEGER)
Hints
- Filter to dates between 2025-05-03 and 2025-06-01 and join vr_usage with apps to label categories
- Aggregate to per-user, per-day totals within each app_category, then average per user to define engagement